2

I have the following lines of PHP code in my file along with some other code:

$command = "INSERT INTO inventory_items (Index, Name, Price) VALUES (NULL, 'Diamond', '3.99')";
$insertion = mysql_query($command) or die(mysql_error());
if ($insertion == FALSE)
{
 echo "Error: Insert failed.";
}
else
{
 echo "Insert successful.";
}

It keeps returning this 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 'Index, Name, Price) VALUES (NULL, 'Diamond', '3.99')' at line 1

myAdmin says I am using MySQL client version 5.0.91. What am I doing wrong? I just can't figure it out! I tried searching a lot...

Improve this question
1
  • Did any of the answers here solve your problem? Commented Jan 4, 2011 at 21:17

3 Answers 3

Reset to default
4

Index is a reserved word in MySQL and as such, you need to either change the name of the column, or escape it with backticks. Try this $command:

$command = "INSERT INTO inventory_items (`Index`, Name, Price) VALUES (NULL, 'Diamond', '3.99')";

Read more about reserved words here: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

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

4 Comments

I don't see "db" on that list, but I ran into the same problem as the OP. I eventually changed my table name to "dbentry" and it worked. So, is there an additional reserved list I don't know about or was I doing something else wrong?
@Bryan: If all you changed was your table name, then I guess db is another reserved word. Not sure if there's another list other than that one though.
As a general rule you should always enclose column names and table names with backticks. If a future version of (my)SQL introduces a new reserved word your once working query may fail.
@DeveloperChris: I agree. That actually happened to me a few years ago. I can't recall the column name, but I believe it was actually date or id
3

Try this:

$command = "INSERT INTO inventory_items (`Index`, Name, Price) VALUES (NULL, 'Diamond', '3.99');";

MySQL reserved words and how to treat them.

Improve this answer

6 Comments

I have tried as you have said but have gotten the same 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 '@Index, Name, Price) VALUES (NULL, 'Diamond', '3.99')' at line 1
It's not @ but Index instead.
yeeeeeeeeah thanks brother :D:D:D so...whats up with the weird quotations?
@user: It's how MySQL handles quoting identifiers, not entirely sure why they picked those, but that's the way the cookie crumbles :P
It must be backticks = ( ` ).
|
0

Can you verify that the columns in your inventory_items table are:

Index
Name
Price

And that you have the Index field set to AUTO_INCREMENT.

The best thing is probably to remove that field from your insert statement.

Try

$command = "INSERT INTO inventory_items (Name, Price) VALUES ('Diamond', '3.99')";

Since you're not inserting an Index anyway.

Hope that helps!

Improve this answer

Comments

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.