error on MySQL UPDATE on a text field

[nanharb]

I am trying to create an update query for a website, and I keep getting an syntax error message. I narrowed it down to the table column that is the only field that is set to be of type text.

UPDATE company SET references = "blah blah blah" WHERE company_id = 18

I don't have any errors in the code. I googled for this and think I have found that it is a bug in MySQL. I am not sure which version I am using but it is 3.23.32 or newer. Is there a work around for this, or do I have to structure an update query differently for a text type field?
HELP!
Thanks
Nan

[cereal]

Maybe it depends on double quotes in references row. Try it like this:

mysql_query("UPDATE company SET references = 'blah blah blah' WHERE company_id = 18");

If you are using a $_POST['references'] then remove single quotes from it. This way: mysql_query("UPDATE company SET references = $_POST[references] WHERE company_id = 18");

[nanharb]

I just tried this query as you suggested, not using any variables:

$query = "UPDATE company SET references = 'blahblahblah' where company_id = 18"

and I get the same error message.

[cereal]

Ok, then try it like this:

$query = "UPDATE company SET references = 'blahblahblah' where `company_id` = 18"

Sometimes the underscore gives some problems.. like "syntax error". In this example I've putbackticks, not single quotes, around company_id.

Bye :)

[nanharb]

I tried that, still get the same error message. So then I tried using this:

$query = "UPDATE company SET references = 'blahblahblah' where username = 'company'";

so I avoid having an underscore completely. Any other suggestions? I am completely at a loss here. Thanks for your help.

[cereal]

:D I hope I've found the reason of the problem! The word references is a reserved word in MySQL, this means that you can't use it as a table or column name.

- http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html

[nanharb]

WOW! That is it? Thank you so much for solving this for me! I have a list of reserved words in PHP, but not MySQL.
I am off to pick through hundreds of lines of code to change the name of that field, oh joy....

Nan

[nanharb]

The weird part is that MySQL let me create the table and insert tons of trial rows using that reserved word, and it never complained until trying to UPDATE a row.

[MitkOK]

Or may be you can ;)

$query = "UPDATE company SET `references` = 'blahblahblah' where username = 'company'";

PS: Pay attention to the quotes ...

-Mitko Kostov

[nanharb]

thanks Mitko, I will try that fist.

[keanan]

JUST WANTED TO SAY THANK-YOU TO cereal AND MitkOK
YOU SAVED ME A LOT OF GRIEF I HAVE BEEN SITTING HERE ALL DAY
GOOGLING ETC TRYING TO FIND THE PROBLEM AS I WAS ABLE TO INSERT BUT NOT
UPDATE AAAAARRR!!! I USED 'desc' AS A COLUMN NAME DOH!!

[cereal]

You're welcome :)

[petrovitch]

I have the same problem, but it's not associated with mysql reserved words.

note is a mysql text filed. I am trying to modify the field with preg_replace in PHP then updating the field to the db table. I have used the update command many times with other data types. Testing the command I did not modify the field. I just tried to rewrite it.

$note = $row["note"];

$query = "update `tableNotes` set `note` = $note where `ID` = $row[ID]";

10322 Failed. 1064: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 'where `ID` = 10322' at line 2

I've tried numerous variations -- all fail.

[munyampundu]

How can I display database content in textfields that I have used to enter them ? using php
I need a help .

thanks !

[cereal]

You can store data to a session, so you can display it when you reload the form.