MySQL Syntax Error; "check the manual that corresponds to your MySQL server version.."

Question

I've been looking all over the internet for a solution to the following error;

"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 'primary, username, password, password2) VALUES (null, 'hello', 'hello', 'hello')' at line 1"

I have no idea what is going on.. I know you will ask what my code is so here:

$con = mysql_connect("localhost","root","*****");
    if (!$con)
      {
      die('Server overload, please try again' . mysql_error());
      }

    mysql_select_db("users", $con);

    $sql = "INSERT INTO details (primary, username, password, password2) VALUES (null, '$_POST[username]', '$_POST[password]', '$_POST[password2]')";

    if (!mysql_query($sql,$con))
      {
      die('Error: Server overload, try again' . mysql_error());
      }
    echo "You have signed up successfully!";

    mysql_close($con);

I've been trying to figure it out for around 4/5 hours now and have had no success.
Thanks,
Lawrence

Solution 1

primary is a reserved keyword, in SQL, which means that you should either :

• rename that column -- would be a good idea, to avoid that kind od situation
• or use backticks arround that name

Here what the query would look like in the second case :

INSERT INTO details (`primary`, `username`, `password`, `password2`)
VALUES (null, 'hello', 'hello', 'hello')

Note : and you should escape your values, using mysql_real_escape_string, to avoid SQL Injections !

Solution 2

Try not to name your tables or columns with relitively common names like primary and details.

While they may not be reserved words in the flavor of SQL you are currently using, you never know when you might be supporting other types (Postgres, Oracle, etc.).

You can also use this handy-dandy reserved word checker.

Followup Question:
I would like to know who wrote the error statement you are getting, which essentially says RTM? Hilarious. I'm going to use that in my next try catch. :)

Solution 3

I'd rename that first column to something else: "primary" is a reserved word in MySQL:

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

Solution 4

Primary is a reserved word. What is the table definition?

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

View more solutions

Comments

[Stephano]

I just googled "You have an error in your SQL syntax; check the manual". Perhaps you were just googling the whole error? The first few results were correct answers.

[Lawrence]

Thanks :) I thought it would be a simple error! Sorted it all out.

[Pascal MARTIN]

You're welcome :-) ;; and don't forget about the "escape your values" part !