Solved

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

Posted on 2007-04-03
31
271 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
ID: 18847160
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
ID: 18847165
have you tried a space between the table name and the brackets?
0
 
LVL 1

Author Comment

by:bprof2007
ID: 18847202
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 20

Expert Comment

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

Author Comment

by:bprof2007
ID: 18847244
Thanks steelseth12

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

Expert Comment

by:Raynard7
ID: 18847274
can you please run the statement

show create table test

and give us the output?
0
 
LVL 1

Author Comment

by:bprof2007
ID: 18847290
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
ID: 18847295
Isn't test a reserve word in mysql?
0
 
LVL 1

Author Comment

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

Expert Comment

by:todd_farmer
ID: 18847329
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
ID: 18847333
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
ID: 18847338
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
ID: 18847351
Table corruption shouldn't generate SQL syntax errors - can't imagine this is the root cause.
0
 
LVL 1

Author Comment

by:bprof2007
ID: 18847440
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
ID: 18847444
Is there a command to repair the table? I'm n00b in mysql and even php.
0
 
LVL 30

Expert Comment

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

Author Comment

by:bprof2007
ID: 18847535
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
ID: 18847579
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
ID: 18847617
Indeed it is odd.

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

Expert Comment

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

Author Comment

by:bprof2007
ID: 18848115
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
ID: 18848139
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
ID: 18848386
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
ID: 18848393
OK, from the command-line, what do you get when you issue:

mysql>  SHOW CREATE TABLE test;
0
 
LVL 1

Author Comment

by:bprof2007
ID: 18848413
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
ID: 18848440
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
ID: 18848441
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
ID: 18848457
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
ID: 18848458
>>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
ID: 18848466
>>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
ID: 18848477
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses four methods for overlaying images in a container on a web page
This article discusses how to create an extensible mechanism for linked drop downs.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

786 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