Web Hosting Talk : Web Hosting Main Forums : Programming Discussion : Simple MySQL/PHP error

[wesslayneb]

Alright, I've been coding in PHP and MySQL for about a year now and I feel like I've done this very query a million times, but all day today I've been running into errors.

The table name is title_transfers, the column I'm editing is read, and the column to represents the person receiving a transfer. $userfinal is the session name. If the "read" column is "no", a notification appears on the home page, alerting the user of a transfer to him/her. When they view the page the following code is at the top of, the read column of any transfer to that person is changed to "yes" so that the notification no longer appears on the home page. The code is as follows:

PHP Code:

mysql_query("UPDATE title_transfers SET read=yes WHERE to=$userfinal") or die(mysql_error()); 


The error I've been receiving:

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=yes WHERE to=RapideGT' at line 1

RapideGT is the test username/session name. I've tried everything; I can't understand why it's not working. I've noticed this type of thing happening a lot after I upgrade to VPS, if that helps. Any input is appreciated!

[kjsrs]

You need to quote your inputs when they are not numerical. Try this instead:

PHP Code:

mysql_query("UPDATE title_transfers SET read='yes' WHERE to='$userfinal'") or die(mysql_error()); 


It's also good practice to quote your table and field names with backticks too:

PHP Code:

mysql_query("UPDATE `title_transfers` SET `read`='yes' WHERE `to`='$userfinal'") or die(mysql_error()); 


[quantumphysics]

use pdo or something

[wesslayneb]

I did use quotes at first, but after viewing several tutorial websites to see if I did anything wrong, most did not have them so I tried without, for kicks and giggles. No luck there, obviously.

And I have no idea what PDO is.

[wesslayneb]

But by some magic it actually worked. The missing backticks were the smoking gun there, lol. Thanks a lot.

[alanversowa]

Nice can you discuss some sorts of magic here ?

[Sajid-HostX10]

Did it work ? It should but if not try this:

PHP Code:

mysql_query("UPDATE `title_transfers` SET `read`='yes' WHERE `to` = '".$userfinal."'") or die(mysql_error()); 


[speckl]

http://dev.mysql.com/doc/refman/5.1/...ved-words.html

Read and to are reserved words. It would only work with backticks or tablename.columnname format.

I recommend changing the column names because you will eventually run into more issues.

[wesslayneb]

Yes, the magic I mentioned before worked, the second way kjsrs mentioned. And thanks, speckl, now I can understand why it didn't work.