unusual sql error

Question

so i've been stuck on this sql error for some time.

this is the SQL line i'm using:

INSERT INTO images (image_name, orientation, restored, commercial, automotive, bespoke, before, after, date_added) 
VALUES ('image4-after.jpg', 'portrait', '1', '1', '1', '1', '24', '5', '2012-07-08')

using this structure:

image_id - int(11) AUTO_INCREMENT
image_name - varchar(40)
orientation - varchar(4)
restored - tinyint(1)
commercial - tinyint(1)
automotive - tinyint(1)
bespoke - tinyint(1)
before - int(11)
after - int(11)
date_added - date

getting the error message:

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 'before, after, date_added) VALUES ('image4-after.jpg', 'portrait', '1', '1', '1'' at line 1

can anybody please tell me what i've done wrong?

thanks

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

Michael Berkowski · Accepted Answer · 2014-12-17T14:04:59.417

10

BEFORE is a MySQL reserved keyword. You need to quote it with backticks to use it as a table or column identifier.

INSERT INTO images (image_name, orientation, restored, commercial, automotive, bespoke, `before`, after, date_added) 
VALUES ('image4-after.jpg', 'portrait', '1', '1', '1', '1', '24', '5', '2012-07-08')

AFTER is not reserved, however.

Whenever a 1064 error points to something non-obvious syntactically in its right syntax to use near... indicator, look to the reserved words list.

edited Dec 17 '14 at 14:04

answered Jul 08 '12 at 19:44

Michael Berkowski

267,341
46
444
390

well that explains a lot! thanks for that insane response time :) – alsweet Jul 08 '12 at 19:48

score 3 · Answer 2 · edited Nov 07 '16 at 00:53

3

BEFORE is a keyword, according to MySQL documentation.

Try escaping the field name with backticks (`).

edited Nov 07 '16 at 00:53

fragilewindows

1,394
1
15
26

answered Jul 08 '12 at 19:45

akanevsky

1,009
2
9
18

score 1 · Answer 3 · answered Jul 08 '12 at 19:46

1

I am guessing it is because before is a reserved word in mysql (http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html).

Try surrounding before with backwards quotes: before.

answered Jul 08 '12 at 19:46

Gordon Linoff

1,242,037
58
646
786

unusual sql error

3 Answers3