What's wrong with this SQL UPDATE query?

Question

I wouldn't ask if i wasn't sure that i have 100% no idea why this isn't working, so, in PHP i'm simply trying to update a value in the MySQL database:

The field im updating - Name: read - Type: tinyint - Length: 1

Here is the PHP code:

do_SQL("UPDATE messages SET read=1 WHERE id='".$id."'");

The do_SQL function is working for everything else, so it's something wrong with this statement. I have tried putting 1 in '1' and it still didn't work.m The error is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'read=1 WHERE id='1'' at line 1

Thanks for reading!

Answer 1

There are no escape characters.

do_SQL('UPDATE messages SET read=1 WHERE id=\''.$id.'\'');

Answer 2

If id is numeric, try removing the quotes aka:

do_SQL("UPDATE messages SET `read` = 1 WHERE id = ".$id);

Answer 3

Is 'Id' an integer?

Answer 4

read Is a keyword in MySQL, so you can't use it without quoting it (with backticks):

do_SQL("UPDATE messages SET `read`=1 WHERE id='".$id."'");

Answer 5

read is probably a reserved word in MySQL.

Yep it is MySQL Reserved Words

Next time check that list before creating a column with a name that's likely to be used already by the database system.

Answer 6

Don't quote $id. Let PHP do substitution in the string.

 do_SQL("UPDATE messages SET read=1 WHERE id=$id.");