mysql_query INSERT INTO fails, but no error is returned

PHP
1

Hi all

My function below attempts to insert values into db table. The table is never updated and mysql returns no errors. The table itself remains empty.

I have found many proposed solutions for similar situations (including in this forum), but none of them have helped. For example, using back ticks instead of single quotes around the values, INSERT might be sending empty values, values might not be reaching the function - along with a few other suggested fixes which didn’t work.

Unfortunately, I don’t have direct access to the db. So, I’m taking the clients word that the table is empty, she seems to know what she’s doing, I don’t have any reason to doubt what she says.

Can anyone throw some new light on this for me - thanks


function store_details($db_server, $db_user, $db_pass, $db_name, $ftp_server, $subject, $skipfile, $skipdir, $rename_file, $alert, $from, $message, $createLog, $cronlogpath){

$date = date ("dMy");
$time = date("H:i");

    $site_table = 'ssa_'.str_replace('-','$',str_replace('.','_',$ftp_server)).'_site';
    $con = mysql_connect($db_server,$db_user,$db_pass)or die('Unable to connect to MySQL server: '.$db_server.'<br>Please check that the following details are correct:<br>
        db server name<br>
        db user name<br>
        db password<br>
        <a href="index1.php?check_db_details=Y">Click to reload form</a>');
    mysql_select_db($db_name, $con)or die('Connected but, unable to select db! '.mysql_error());
    $query = "TRUNCATE TABLE $site_table";
    mysql_query($query)or die('Failed to empty site table<br>'.mysql_error());

    $query ="INSERT INTO $site_table (
        email_subj,
        email_alert,
        skip_files,
        skip_dir,
        from_addr,
        email_header,
        cron_path,
        rename_file,
        SSA_log,
        date,
        time
       )
        VALUES (
        '$subject',
        '$alert',
        '$skipfile',
        '$skipdir',
        '$from',
        '$message',
        '$cronlogpath',
        '$rename_file',
        '$createLog',
        '$date',
        '$time')";


    mysql_query($query) or die('Failed to update site table. Mysql returned the following:<br><br>'.mysql_error());

    mysql_close($con)or die(mysql_error());

}
2

Do you have error checking enabled to see if you just have some simple php error not related to the db actions?

3

Hi ginerjm

I had error checking enabled. Still nothing in the db and no error revealed.

I’m about to try a different approach by calling a separate function to do a bit of sanitisation and then do the query. The new function is below.

$inserts //is an array where the keys are the column names and the values are the values to be inserted

$table //is the name of the table


function mysql_insert($table, $inserts) {
    $values = array_map('mysql_real_escape_string', array_values($inserts));
    $keys = array_keys($inserts);
    return mysql_query('INSERT INTO `'.$table.'` (`'.implode('`,`', $keys).'`) VALUES (\\''.implode('\\',\\'', $values).'\\')');
}

It works on all the servers I have access to - just hoping it works on the clients server.

4

Echo the qry statement and br sure it looks good

5

Since you need to write all the mysql_ calls to use either mysqli_ or PDO it may be worthwhile doing that rewrite first and see if that makes any difference to whether the code works or not. Doing the rewrite will also mean that you will be looking at the code a different way and so may make it easier to spot the error.

6

Thanks guys

felgall - my entire script will need updating to msqli/PDO, as soon as I get to grips with how to change it that is. I’m not sure what’s involved at this time.

I just want to get this current problem out of the way first.

Regards

7

Haven’t seen you post your actual completed query statement. Have you at least looked at it to see if it is syntactically correct?

8

You might give this a go. Return $sql string for testing query string.

function mysql_insert($table, $inserts) {
    $values = array_map('mysql_real_escape_string', array_values($inserts));
    $keys = array_keys($inserts);
    $sql = "INSERT INTO `$table` (`".implode("`,`", $keys)."`) VALUES ('".implode('\\',\\'', $values)."')";
	//return $sql;
	return mysql_query($sql);
}
9

Thanks guys

ginerjm, my query is in full in my first message. However …

I’ve found the cause of the problem. The failed mysql_query() turned out to be a red herring. The problem is that the function to store the details is never called.

For some reason on this server, the IF statement that checks for form submission is stopping the function call. I’ve tried the following checks:


if($_POST['submit']){
  store_details( ... );
}

and


if(isset($_POST['submit'])){
  store_details( ... );
}

The Submit button name is ‘submit’, but the check always fails, which is weird because my script has a 2nd form on a different page with exactly the same check, which works perfectly. And both forms work perfectly on all other servers.

I’m stuck for ideas - this simply shouldn’t be happening

Can anyone shed any light on this for me?

10

Dump out the POST array to see if submit is a value within it. There used to be an issue where if you clicked submit it sent it as normal but if you hit the Enter key - it didnt.
Dont know if this is still an issue but what it lead me to do (and still do) is use a hidden field with a form_id identifier.

eg


<input type="hidden" name="form_id" value="insert_user_form">

then check for that value in the POST array.

11

Yes, I’ve already tried the hidden field trick, but still no joy. Also, when I try var_dump or print_r on the $_POST I get a server error

12

Sorted out the server error (my fault), but now I get an empty array for var_dump($_POST);

My form action: <form name=“myform” method=“POST” action=“index.php”> is very straightforward.
index.php is the same page. I’ve tried $PHP_SELF with the same results GRRRRRRRR!

13

My request for the query meant the full, translated query. but never…

As for your submission - are you sure that your bad form has a closing form tag?

14

Yes, the form is closed correctly.

I’ve just been speaking with the site owner and he now tells me that he is moving his site from that server due to other problems (which he didn’t tell me about), So, as this problem only occurs on that server, I’m not going to investigate any more. I can now concentrate on mysql to mysqli conversion in my script.

Thanks to all - sorry to back out like this.

15

method=“POST” should be lower-case method=“post” but I doubt that’s the problem.

16

Actually most tutorials/examples of method= use UPPER case for POST. Just as it HAS to be in the PHP code processing the array.

And I really dont’ see how an html form would have submission problems from one server to the next. Perhaps you’d like to post the complete form logic that is not working.

17

http://www.w3.org/TR/html4/interact/forms.html#adef-method Strictly speaking that is. If you were running the page through a good validater, it would probably throw a warning on the upper-case.

If he’s getting an empty array for var_dump($_POST); then it is a form issue.

18

or a server issue in that the server is for some reason not processing the posted form.
If it works on one server but not the other then it’s more likely to be the server config.

As for capitals or not, its a case of personal preference. My preference is lowercase :slight_smile: