PHP / SQL Code not inserting data into database but no errors reported either

Question

I am having problems which the following code:

if(mysql_num_rows(mysql_query("SELECT user_id FROM myTable WHERE user_id = '$user_id'"))){

    mysql_query("UPDATE myTable SET
    id='',
    user_id='$user_id',
    title='$title',
    WHERE user_id='$user_id'
    ") 

}  else {

    mysql_query("INSERT INTO myTable (id, user_id, title)
                 VALUES ('', $user_id, $title) ");
}

The update part seems to work but after the else it doesn't insert the record into the table.

Can anyone spot what could be the problem please?

If you did some very, very basic error checking, you could find out yourself... [Reference: What is a perfect code sample using the mysql extension?](http://stackoverflow.com/q/6198104) — Pekka, Oct 31 '11 at 14:39
does mysql_query('query') or die(mysql_error()) say something? — , Oct 31 '11 at 14:42

Your Common Sense · Accepted Answer · 2011-10-31T15:12:06.840

I am having problems which the following code

no wonder, as this code is just horrible. four nested operators!
even if you'd get some error messages, you'll be unable to tell what operator it belongs to

no errors reported either

you didn't ask for any

make your code more consistent and add error reporting to it:

$sql = "SELECT user_id FROM myTable WHERE user_id = '$user_id'";
$res = mysql_query($sql) or trigger_error(mysql_error()." ".$sql);
if (mysql_num_rows($res)) { 
  $sql = "UPDATE myTable SET id='',user_id='$user_id',title='$title', WHERE user_id='$user_id'";
  mysql_query($sql) or trigger_error(mysql_error()." ".$sql); 
}  else { 
  $sql = "INSERT INTO myTable (id, user_id, title) VALUES ('', $user_id, $title)";
  mysql_query($sql) or trigger_error(mysql_error()." ".$sql); 
}

and you will always be informed of the any error occurred

This should be the accepted answer. This way, you can help yourself! — Pekka, Oct 31 '11 at 14:58

score 1 · Answer 2 · answered Oct 31 '11 at 14:40

1

Make sure you quote your values in the VALUES () portion:

mysql_query("INSERT INTO myTable (id, user_id, title)
             VALUES ('', '$user_id', '$title') ");

answered Oct 31 '11 at 14:40

jprofitt

10,874
4
36
46

score 1 · Answer 3 · answered Oct 31 '11 at 14:40

1

If your ID field an auto increment integer? If so then trying to set it to a value of '' will fail. Just leave the ID out of your insert query altogether and it should work

answered Oct 31 '11 at 14:40

GordonM

31,179
15
87
129

score 0 · Answer 4 · answered Oct 31 '11 at 14:40

0

Please look into INSERT... ON DUPLICATE KEY UPDATE, http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

answered Oct 31 '11 at 14:40

Derek

4,864
5
28
38

Careful with this one if you need to be able to port to another database in the future, few other (if any) databases support it. – GordonM Oct 31 '11 at 14:42

PHP / SQL Code not inserting data into database but no errors reported either

4 Answers4