MySQL insert problem: You have an error in your SQL syntax

Can someone please help me find the error in this query?

mysql_query("INSERT INTO test(a, b, c)
                          VALUES ('$a', '$b', '$c')") or die (mysql_error());

It keeps giving me 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 'test(a, b, c) VALUES ('test', 'test', '5')' at line 1


LVL 1
bprof2007Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gamebitsCommented:
Try this

mysql_query("INSERT INTO test (`a`, `b`, `c`)
                          VALUES ('$a', '$b', '$c')") or die (mysql_error());
0
Raynard7Commented:
have you tried a space between the table name and the brackets?
0
bprof2007Author Commented:
Thanks for your help guys.

gamebits,

I tried it, but it gave me the same error.

Raynard7,

I tried it with space, same error.

I have the same query for another table, working just fine, I copied it and pasted it into the new script, and changed the table name, and fields names.

Any ideas?

Thanks again
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

steelseth12Commented:
what datatype are the fields in the first table ? field a,b,c
0
bprof2007Author Commented:
Thanks steelseth12

a-->varchar
b-->test
c-->varchar
0
Raynard7Commented:
can you please run the statement

show create table test

and give us the output?
0
bprof2007Author Commented:
Ya sure. I ran it and I got this 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 'test' at line 1
0
gamebitsCommented:
Isn't test a reserve word in mysql?
0
bprof2007Author Commented:
I have tried with different name (somename) and I got the same error.
0
todd_farmerCommented:
No, it is not (and it's mentioned that the same error is given for different table, as I understand it.)

Please build the query as a variable:

$query = "INSERT ... ";

then execute it:

mysql_query($query) or die (mysql_error());
0
todd_farmerCommented:
Please also output the actual $query value so you can validate/provide it.
0
Raynard7Commented:
test should not be a reserved word, I tried creating one just then and had no problems.

what I am guessing is that this table is corrupted - because you have tried show create table and this failed, where the only variable is "test" then I do not understand why there is a syntax error.

can you try repairing the table?
0
todd_farmerCommented:
Table corruption shouldn't generate SQL syntax errors - can't imagine this is the root cause.
0
bprof2007Author Commented:
Thanks todd,

I tried using the query as variable and I got the same error.

a->text b->adsf c->2007/04/25

The only thing that I can think of as a reason for the error is c value which 2007/04/25. Could it be the reason for the error?
0
bprof2007Author Commented:
Is there a command to repair the table? I'm n00b in mysql and even php.
0
todd_farmerCommented:
Can you provide the full value of $query?
0
bprof2007Author Commented:
Here is the query value:

INSERT INTO test (`a`, `b`, `c`) VALUES ('test1', 'test2', '2007/04/25')
0
todd_farmerCommented:
Truly odd - do you have access to the mysql command-line client (or phpMyAdmin or some other interface directly to MySQL)?  If so, what is the result of the following query:

SELECT VERSION();
0
bprof2007Author Commented:
Indeed it is odd.

The version is 5.0.24a-community-nt
0
todd_farmerCommented:
What happens when you run the above INSERT statement in the command-line (not via PHP)?
0
bprof2007Author Commented:
I tried to run mysql from command line but I failed. I'm using xamp on windows.
0
todd_farmerCommented:
When you say failed - what happens?

You want to open a command-line window, navigate to the folder that contains mysql.exe, and type:

mysql -uroot -ppass

where root is the user name and pass is the password.

Then you should get a mysql> prompt, at which point you would do:

mysql>  USE db_name  # where db_name is the name of your database
mysql>  INSERT INTO test (`a`, `b`, `c`) VALUES ('test1', 'test2', '2007/04/25');

0
bprof2007Author Commented:
Thanks for the clarification.

I tried it and I got the same result :(

ERROR 1064 (42000): 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 'test (
`a`, `_b`, `c`) VALUES ('test1', 'test2', '2007/04/25')' at line
1

0
todd_farmerCommented:
OK, from the command-line, what do you get when you issue:

mysql>  SHOW CREATE TABLE test;
0
bprof2007Author Commented:
Here is the error:

ERROR 1064 (42000): 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 'test'
at line 1

I really appreciate your help.
0
bprof2007Author Commented:
Thanks a lot todd and everyone, I've run the query on different machine and it worked fine. I don't know what is wrong with the other machine configurations. But the syntax is correct the code is correct, it was all in that machine.

0
todd_farmerCommented:
Very, very odd - I'm using a slightly later version (5.0.37), but still having no such problems.  Can you post the results of the following:

mysql>  SHOW TABLES LIKE 'test';
mysql>  SHOW CREATE TABLE `test`;

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
todd_farmerCommented:
Check the SQL mode:

SELECT @@global.sql_mode;
SELECT @@session.sql_mode;

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

IGNORE_SPACE

Allow spaces between a function name and the ‘(’ character. This causes built-in function names to be treated as reserved words. As a result, identifiers that are the same as function names must be quoted as described in Section 9.2, “Database, Table, Index, Column, and Alias Names”. For example, because there is a COUNT() function, the use of count as a table name in the following statement causes an error:

mysql> CREATE TABLE count (i INT);
ERROR 1064 (42000): You have an error in your SQL syntax

The table name should be quoted:

mysql> CREATE TABLE `count` (i INT);
Query OK, 0 rows affected (0.00 sec)

The IGNORE_SPACE SQL mode applies to built-in functions, not to user-defined functions or stored functions. It is always allowable to have spaces after a UDF or stored function name, regardless of whether IGNORE_SPACE is enabled.

For further discussion of IGNORE_SPACE, see Section 9.2.3, “Function Name Parsing and Resolution”.
0
bprof2007Author Commented:
>>mysql>  SHOW TABLES LIKE 'test';

+---------------------+
| Tables_in_test (test) |
+---------------------+
| test                 |
+---------------------+
1 row in set (0.00 sec)

mysql>  SHOW CREATE TABLE `test`;

ERROR 1064 (42000): 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 'mysql
>  SHOW CREATE TABLE `test`' at line 1
0
bprof2007Author Commented:
>>SELECT @@global.sql_mode;

+-------------------+
| @@global.sql_mode |
+-------------------+
|                   |
+-------------------+
1 row in set (0.05 sec)

>>SELECT @@session.sql_mode;
+--------------------+
| @@session.sql_mode |
+--------------------+
|                    |
+--------------------+
1 row in set (0.02 sec)

0
todd_farmerCommented:
ERROR 1064 (42000): 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 'mysql
>  SHOW CREATE TABLE `test`' at line 1

(looks like you might have copied the mysql> prompt I provided into the client)

If you are able to get it to run using the quoted table name, it's the SQL Mode issue:

SELECT * FROM `test`;

SHOW CREATE TABLE `test`;
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.