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

Try this

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

have you tried a space between the table name and the brackets?

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

what datatype are the fields in the first table ? field a,b,c

Thanks steelseth12

a-->varchar
b-->test
c-->varchar

can you please run the statement

show create table test

and give us the output?

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

I have tried with different name (somename) and I got the same error.

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());

Please also output the actual $query value so you can validate/provide it.

Table corruption shouldn't generate SQL syntax errors - can't imagine this is the root cause.

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?

Is there a command to repair the table? I'm n00b in mysql and even php.

Can you provide the full value of $query?

Here is the query value:

INSERT INTO test (`a`, `b`, `c`) VALUES ('test1', 'test2', '2007/04/25')

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();

Indeed it is odd.

The version is 5.0.24a-community-nt

What happens when you run the above INSERT statement in the command-line (not via PHP)?

I tried to run mysql from command line but I failed. I'm using xamp on windows.

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');

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

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

mysql>  SHOW CREATE TABLE test;

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.

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.

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

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

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

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`;