Where is the SQL syntax error in this query?

Question

mysqli_error() clearly states:

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 'group, uploaded_by, date_uploaded, url) VALUES (1, 1, NOW(), '/gallery/1/h' at line 2

But this is the generated SQL query

INSERT INTO
gallery_images (group, uploaded_by, date_uploaded, url)
VALUES (1, 1, NOW(), '/gallery/1/halflife2180z97stmydo1600x1200.jpg')

It's practically identical to another SQL query I have running on the same site, without errors.

I would understand if I'm trying to insert an invalid value into a field in MySQL, but it clearly states that I have a syntax error, and I just can't see it.

Reply if you can see it. Below is the PHP behind the query

$res = $con->query("
        INSERT INTO
        gallery_images (group, uploaded_by, date_uploaded, url)
        VALUES ($group, {$_SESSION[user]->id}, NOW(), '$escaped_name')
    ");

Just a remark, you should avoid naming a field `group` since it's a word used for query. — Shikiryu, Jan 11 '11 at 08:16
@ClemDesm: agree with you. `grp` or `group_id` are good alternatives: the meaning remains clear and you don't have query problems. — nico, Jan 11 '11 at 08:20
HAHA, I didn't know that was the problem and the answer. \o/ — Shikiryu, Jan 11 '11 at 08:39
possible duplicate of [Syntax error due to using a reserved word as a table or column name in MySQL](http://stackoverflow.com/questions/23446377/syntax-error-due-to-using-a-reserved-word-as-a-table-or-column-name-in-mysql) — Ian Ringrose, May 06 '14 at 10:18

score 4 · Accepted Answer · answered Jan 11 '11 at 08:14

4

group is a reserved keyword. Put backticks around it.

$res = $con->query("
        INSERT INTO
        gallery_images (`group`, uploaded_by, date_uploaded, url)
        VALUES ($group, {$_SESSION[user]->id}, NOW(), '$escaped_name')
    ");

answered Jan 11 '11 at 08:14

nico

50,859
17
87
112

score 1 · Answer 2 · answered Jan 11 '11 at 08:13

1

Group is a reserved keyword, change that column name.

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

answered Jan 11 '11 at 08:13

Dejan Marjanović

19,244
7
52
66

It's better to escape it than change it. – cherouvim Jan 11 '11 at 08:15
Yes, I know, but he might do that again :) Anyway I thing proper way is 'group_id' or 'gid'... – Dejan Marjanović Jan 11 '11 at 08:16

score 0 · Answer 3 · answered Jan 11 '11 at 08:14

0

group is reserved MySQL keyword, you cannot use it like that. Surround it with backtick `group ` (the symbol near 1 key)

answered Jan 11 '11 at 08:14

nan

19,595
7
48
80

1

You need backticks, not single quotes. – cherouvim Jan 11 '11 at 08:16
Assuming you have a US keybord the symbol next to 1 is a backtick not a quote (` vs '). – nico Jan 11 '11 at 08:17
No problem. To be completely correct, the name of that mark is "grave accent", but "backtick" is used (generally in programming), when it is used by itself. – nico Jan 11 '11 at 08:23

score 0 · Answer 4 · answered Jan 11 '11 at 08:14

0

gallery_images (group, uploaded_by, date_uploaded, url)

group is keyword

answered Jan 11 '11 at 08:14

score 0 · Answer 5 · answered Jan 11 '11 at 08:16

0

$res = $con->query("
        INSERT INTO
        gallery_images (`group`, `uploaded_by`, `date_uploaded`, `url`)
        VALUES ($group, {$_SESSION[user]->id}, NOW(), '$escaped_name')
    ");

Should do it.

answered Jan 11 '11 at 08:16

Dennis Kreminsky

2,117
15
23

Could you explain what the ` backticks do compared to single quotes ' ? – Hubro Jan 11 '11 at 08:26
Check this question on stack overflow: http://stackoverflow.com/questions/261455/using-backticks-around-field-names – Dennis Kreminsky Jan 11 '11 at 08:32

score 0 · Answer 6 · answered Jan 11 '11 at 08:17

0

group is a reserved word in MySQL. You might have to put it in quotes:

INSERT INTO
gallery_images (`group`, uploaded_by, date_uploaded, url)
VALUES (1, 1, NOW(), '/gallery/1/halflife2180z97stmydo1600x1200.jpg')

answered Jan 11 '11 at 08:17

Anax

9,122
5
34
68

Where is the SQL syntax error in this query?

6 Answers6