0

I have this very simple query:

SELECT * FROM "orders" WHERE "printer_id" = 'AC001' AND "sent" = '0'

It throws the following error:

#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 '"orders" WHERE "printer_id" = 'AC001' AND "sent" = '0' at line 1

Removing the "" from the column names makes it work! Im using Medoo (http://medoo.in/) as my database framework if that helps.

Jiggles
  • 73
  • 2
  • 16

2 Answers2

1

Fields need to be escaped using backticks, not double quotes:

SELECT * FROM `orders` WHERE `printer_id` = 'AC001' AND `sent` = '0'
Cobra_Fast
  • 15,671
  • 8
  • 57
  • 102
1

The double quotes around identifiers are used only if the SQL_MODE setting includes ANSI_QUOTES.

The default identifier quote character is the backtick character.

MySQL is returning a syntax error because it's seeing "orders" as a string literal, where it's expecting an identifier (a table name).

Either of these should work to get around the problem:

Leave the identifiers unquoted:

SELECT * FROM  orders  WHERE  printer_id  = 'AC001' AND  sent  = '0'

Use backticks to enclose the identifiers

SELECT * FROM `orders` WHERE `printer_id` = 'AC001' AND `sent` = '0'

Or (I dom't recommend this, but it is another approach...

Change the setting of the SQL_MODE to include ANSI_QUOTES option, and use the double quotes as the identifier escape character, like in your original statement.

spencer7593
  • 106,611
  • 15
  • 112
  • 140