3

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.

Improve this question
7
  • If you run that code you will get syntax error, unexpected T_VARIABLE because you are missing a semi-colon. Please provide the code you are actually using. When people debug something that isn't your code, they'll often fail to fix your code because it isn't as similar as you think it is. Commented Feb 22, 2010 at 12:51
  • what does your table structure look like? Commented Feb 22, 2010 at 12:52
  • what is the type of the ID_PRODUCT field? Commented Feb 22, 2010 at 12:53
  • oop, added the semi colon. Forgot it when i typed the question Commented Feb 22, 2010 at 12:53
  • 1
    What is $id? What steps are you taking to sanitize it? Why are you using parametrized queries? Commented Feb 22, 2010 at 12:56

3 Answers 3

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

ID_PRODUCT is an INT. More precisly it's an int(11)
What kind of database? Do you need to specify a schema or a table owner?
thanks for you help. I copy pasted the query in phpMyAdmin and it works normally...
There is no table owner. I'm sorry but what do you mean by kind of database ?
Ah sorry, you say in your question, MySQL.
2

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).

Improve this answer

Comments

0

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.)

Improve this answer

2 Comments

The quotes didn't help. I used full capitals to stick with the rest of database. But I'm curious to learn why it's a bad habit, readability ?
Full capitals are used for keywords in SQL statements, mostly for readability.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.