Solved

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

Posted on 2007-04-03
31
267 Views
Last Modified: 2013-12-12
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


0
Comment
Question by:bprof2007
  • 14
  • 11
  • 3
  • +2
31 Comments
 
LVL 28

Expert Comment

by:gamebits
Comment Utility
Try this

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

Expert Comment

by:Raynard7
Comment Utility
have you tried a space between the table name and the brackets?
0
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
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
 
LVL 20

Expert Comment

by:steelseth12
Comment Utility
what datatype are the fields in the first table ? field a,b,c
0
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
Thanks steelseth12

a-->varchar
b-->test
c-->varchar
0
 
LVL 35

Expert Comment

by:Raynard7
Comment Utility
can you please run the statement

show create table test

and give us the output?
0
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
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
 
LVL 28

Assisted Solution

by:gamebits
gamebits earned 100 total points
Comment Utility
Isn't test a reserve word in mysql?
0
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
I have tried with different name (somename) and I got the same error.
0
 
LVL 30

Expert Comment

by:todd_farmer
Comment Utility
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
 
LVL 30

Expert Comment

by:todd_farmer
Comment Utility
Please also output the actual $query value so you can validate/provide it.
0
 
LVL 35

Assisted Solution

by:Raynard7
Raynard7 earned 100 total points
Comment Utility
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
 
LVL 30

Expert Comment

by:todd_farmer
Comment Utility
Table corruption shouldn't generate SQL syntax errors - can't imagine this is the root cause.
0
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
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
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
Is there a command to repair the table? I'm n00b in mysql and even php.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 30

Expert Comment

by:todd_farmer
Comment Utility
Can you provide the full value of $query?
0
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
Here is the query value:

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

Expert Comment

by:todd_farmer
Comment Utility
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
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
Indeed it is odd.

The version is 5.0.24a-community-nt
0
 
LVL 30

Expert Comment

by:todd_farmer
Comment Utility
What happens when you run the above INSERT statement in the command-line (not via PHP)?
0
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
I tried to run mysql from command line but I failed. I'm using xamp on windows.
0
 
LVL 30

Expert Comment

by:todd_farmer
Comment Utility
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
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
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
 
LVL 30

Expert Comment

by:todd_farmer
Comment Utility
OK, from the command-line, what do you get when you issue:

mysql>  SHOW CREATE TABLE test;
0
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
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
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
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
 
LVL 30

Accepted Solution

by:
todd_farmer earned 300 total points
Comment Utility
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
 
LVL 30

Expert Comment

by:todd_farmer
Comment Utility
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
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
>>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
 
LVL 1

Author Comment

by:bprof2007
Comment Utility
>>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
 
LVL 30

Expert Comment

by:todd_farmer
Comment Utility
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Things That Drive Us Nuts Have you noticed the use of the reCaptcha feature at EE and other web sites?  It wants you to read and retype something that looks like this.Insanity!  It's not EE's fault - that's just the way reCaptcha works.  But it is …
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now