-1

I always get this error when executing the query:

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 user_id_sender = 1 AND user_id_receiver = 33 AND OR user_id_sender' at line 1

This is my query: 

mysql_query("UPDATE messages SET read =1 WHERE user_id_sender = $user_id AND user_id_receiver = $user_id_partner AND read = 0 OR user_id_sender = $user_id_partner AND user_id_receiver = $user_id  AND read = 0  ") or die (mysql_error());

the table(messages) has these columns:

message_id(INT)(AI)
user_id_sender(INT)
user_id_receiver(INT)
message(TEXT)
read(INT)

I really don't know why it does this, i have looked the web but I can't seem to find an answer for my exact problem. Maybe somebody could help me out.

Jocelyn
  • 11,209
  • 10
  • 43
  • 60
Simon Vermeir
  • 41
  • 1
  • 1
  • 6

1 Answers1

9

READ is a reserved word.

Always enclose database, table and column names in backticks to prevent such conflicts:

UPDATE `messages` SET `read`=1 WHERE `user_id_sender`=$user_id AND...
Niet the Dark Absol
  • 320,036
  • 81
  • 464
  • 592
  • 1
    Probably a good idea to avoid using a reserved word for a column name. – Ray Paseur Dec 30 '12 at 19:43
  • @RayPaseur Why, when it makes sense as a name? – Niet the Dark Absol Dec 30 '12 at 19:43
  • @Kolink: Because it caused an extra debugging cycle, as evidenced here. Choosing something like my_read might also make sense as a name, but would avoid this hiccup. – Ray Paseur Dec 30 '12 at 19:47
  • @RayPaseur It would also make the code more enigmatic. Backticks are there for a reason. Are you saying that just because PHP recognises `$str = barestring;` we should never use quotes and instead define each word one by one? – Niet the Dark Absol Dec 30 '12 at 19:49
  • @Kolink: You're kidding, right? Or do you just want to argue? In any case, this will be my last on it. Organizations have coding standards. I've consulted for lots of organizations and I've never seen one standard that said it was OK to use SQL reserved words for column names. And while PHP will let you use that expression, it will also raise this: Notice: Use of undefined constant barestring - assumed 'barestring' in /path/to/kolink.php on line 2. – Ray Paseur Dec 30 '12 at 20:04
  • @RayPaseur I just don't get why you would contort yourself into knowing every single reserved word and avoiding them like the plague, when you could just "vaccinate" yourself with backticks. Heck, even `SHOW CREATE TABLE` uses backticks around the column names, even if it not a reserved word. – Niet the Dark Absol Dec 30 '12 at 20:07