Simple query throwing an SQL syntax error

Question

I've got a simple SQL query that keeps throwing an SQL syntax error. It looks like this:

$sql = "SELECT COUNT(*) AS TOTAL FROM PRODUCT WHERE ID_PRODUCT = ".$id;
$result = mysql_query($sql) or die(mysql_error()); 

Which throws :

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_PRODUCT = 1' at line 1

I don't understand what's going on. Of course if I try the query directly in phpMyAdmin it works with no problem.

It returns exactly the same error with a SELECT *

Edit: ID_PRODUCT is a foreign key...

Update: I also get the same error when replacing the $id variable by a static value in the $sql string WHERE ID_PRODUCT = 2 and when protecting the names by quotes.

Answer 1

If the ID_PRODUCT column is a varchar, you will need to put single quotes around the value in your where clause. Might be something like that, can't tell without info about your schema.

---

Update: Not sure then. Obvious next step is to print out the generated SQL and try running it manually, hopefully the issue will manifest and you will be able to rule out PHP as the source of the issue.

Answer 2

Try quoting your table, column and identifier names. Take case-sensitivity into account. Do not use FULL-CAPITAL names for your tables, columns or identifiers.

Edit: For readability, mostly, but might also prevent clashes with reserved keywords.

I was thinking along the lines of:

SELECT COUNT(*) AS "total" FROM "Product" WHERE "id_Product" = 1

Note that MySQL uses the non-standard backtick character (`) to quote these by default. (Though they don't show up here properly so I might as well use double quotes.)

Answer 3

Try completely retyping your $sql line by hand, you might accidentally have an invisible extended character in there (such as a non-breaking space instead of a regular space, etc).