more PHP mySQL INSERT fun

Question

mysql_query("INSERT INTO dictionary ('word', 'definition') VALUES ('".$word."','".$definition."');")

That just will not execute, when I echo it - I get this:

INSERT INTO dictionary ('word', 'definition') VALUES ('monkey','monkey');

So the values are being brought into it properly, if I out put mysql_error() I get:

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 ''word', 'definition' VALUES ('monkey','monkey')' at line 1

Any ideas? I'm stumped.

How are the tables set up in the database? Could be a field value mistake — Phil, Feb 05 '11 at 19:26
If this is a recurring problem, learn to use the error messages: the first character of "right syntax to use near:" is where the parser stopped because you did something wrong. That's a single quote, which should've signaled to you that putting single quotes there is wrong. — Dan Grossman, Feb 05 '11 at 19:28
Way to go. Someone downvoted a question, that actually could be an example of what information should be provided in order to fix the issue. — Mchl, Feb 05 '11 at 19:31

score 6 · Accepted Answer · edited May 23 '17 at 12:04

6

You need to use backticks for field names:

INSERT INTO dictionary (`word`, `definition`)

(or, of course, no quotes at all. But it is better to have them.)

edited May 23 '17 at 12:04

Community

1
1

answered Feb 05 '11 at 19:26

Pekka

442,112
142
972
1,088

I will argue that it's better not to have them, but yeah... that's the reason ;) – Mchl Feb 05 '11 at 19:28
Why is it better to have them? – BoltClock Feb 05 '11 at 19:30
1

@BoltClock http://stackoverflow.com/questions/261455/using-backticks-around-field-names however, they are not ANSI SQL compliant so I guess if you are targeting a different DBMS, you need to switch mySQL to ANSI mode and use double quotes `"` - interesting, didn't know that (Pinging @Mchl) – Pekka Feb 05 '11 at 19:30
It's better to NOT have them, because this forces you (if you are responsible for that of course) to use table/column names, that are not reserved words or are otherwise difficult to work with. MySQL will let you create a table called `.` if you want (backticks allow that!), but this might not be the best idea. See: http://bugs.mysql.com/bug.php?id=49636 – Mchl Feb 05 '11 at 19:35
@Mchl but you never know what words will become reserved words in the future (although this admittedly has a very small real-world probability). But you can use backticks and still choose table and column names responsibly – Pekka Feb 05 '11 at 19:37
@Pekka: Yeah, the 'future' reserved words issue is probably the best argument FOR using backticks. One from the link you posted is a bit weaker. I'm still convinced that it's better to avoid them though. – Mchl Feb 05 '11 at 19:43

score 0 · Answer 2 · answered Feb 05 '11 at 19:26

0

Yeh remove the quotes from the column definitions. You only need them around the strings you are inserting.

answered Feb 05 '11 at 19:26

diagonalbatman

17,340
3
31
31

score 0 · Answer 3 · answered Feb 05 '11 at 19:26

When referencing column names for INSERT you should be using backticks (`) not single quotes. (Single quotes is telling MySQL those values are strings and not column references).

Either remove the single quotes or use the backticks and the problem should resolve itself.

score 0 · Answer 4 · answered Feb 05 '11 at 19:26

0

Change your single quotes around word and dictionary to backticks:

INSERT INTO dictionary (`word`, `definition`) VALUES ('monkey','monkey');

answered Feb 05 '11 at 19:26

Ryan Doherty

38,580
4
56
63

score 0 · Answer 5 · answered Feb 05 '11 at 19:27

0

Correct Method:

mysql_query("INSERT INTO `dictionary` (`word`, `definition`) VALUES ('".$word."','".$definition."');")

which will be ouput as this:

INSERT INTO `dictionary` (`word`, `definition`) VALUES ('monkey','monkey');

answered Feb 05 '11 at 19:27

Adil

3,183
5
28
30

score 0 · Answer 6 · edited Feb 05 '11 at 19:30

0

mysql_query("INSERT INTO dictionary (`word`, `definition`) VALUES ('".$word."','".$definition."');")

Note the apostrophes. The field names should either use no apostrophes, or use the ones shown here.

edited Feb 05 '11 at 19:30

BoltClock

700,868
160
1,392
1,356

answered Feb 05 '11 at 19:27

Bob

41
1
5

score 0 · Answer 7 · answered Feb 05 '11 at 19:30

0

if this is not working:

mysql_query("INSERT INTO dictionary (word,definition) VALUES ('".$word."','".$definition."')");

then you have problem with field names... check your name in table... or maybe you missing something! what your table look like?

answered Feb 05 '11 at 19:30

FeRtoll

1,247
11
26

more PHP mySQL INSERT fun

7 Answers7