Member Avatar for 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

  • 6 Contributors
  • 14 Replies
  • 2K Views
  • 2 Years Discussion Span
  • Latest Post Latest Post by cereal

Dani AI

Generated

Good news: the TEXT type is not the problem. The syntax error is coming from the column name. references is a reserved keyword in MySQL (used in foreign key syntax). Reserved words can only be used as identifiers if you quote them, or better, avoid them entirely. So either rename the column (for example, company_refs) or always use backticks around it. (dev.mysql.com)

Quick, safe fix using a prepared statement (so you do not fight quotes in the data and you avoid SQL injection):

// PDO
$sql  = "UPDATE company SET `references` = :txt WHERE company_id = :id";
$stmt = $pdo->prepare($sql);
$stmt->execute([':txt' => 'blah blah blah', ':id' => 18]);

A few gotchas that came up in this thread:

  • and are right about quoting identifiers. Use backticks for identifiers and single quotes for string literals. Do not rely on double quotes for strings because servers running with ANSI_QUOTES treat double quotes as identifier quotes. (dev.mysql.com)
  • , your failing example is missing quotes around the string value and uses unquoted array keys. Use prepared statements (or at least quote and escape) and reference array keys as strings:
$note = $row['note'];           // array key in quotes
$id   = (int)$row['ID'];        // cast for safety

$sql  = "UPDATE tableNotes SET note = ? WHERE ID = ?";
$stmt = $pdo->prepare($sql);
$stmt->execute([$note, $id]);

Why you could INSERT but not UPDATE with references: sometimes the parser accepts an identifier until it hits a context where that word has special meaning. UPDATE tends to surface it quickly. Long term, renaming the column avoids future surprises and makes the SQL clearer.

Recommended Answers

All 14 Replies

Member Avatar for 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 then remove single quotes from it. This way: mysql_query("UPDATE company SET references = $_POST[references] WHERE company_id = 18");

Member Avatar for 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.

Member Avatar for 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 put backticks, not single quotes, around company_id.

Bye :)

Member Avatar for 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.

Member Avatar for 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.

-

Member Avatar for 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

Member Avatar for 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.

Member Avatar for MitkOK

Or may be you can ;)

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

PS: Pay attention to the quotes ...


- Mitko Kostov

Member Avatar for nanharb

thanks Mitko, I will try that fist.

Member Avatar for 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!!

Member Avatar for cereal

You're welcome :)

Member Avatar for 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.

Member Avatar for munyampundu

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

thanks !

Member Avatar for cereal

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.