SQL Syntax Error near ADD

Question

I've been staring at this thing for a while now and I can't seem to figure out what the syntax error is. I've been in this situation before and last time it was something so unbelievably simple I felt stupid afterwards. But here's to another attempt:

//update database
$q = "
 UPDATE 
  users 
 SET 
  id='$edit_id',
  name='$edit_name',
  bdm='$edit_bdm',
  add='$edit_add',
  pc='$edit_pc',
  location='$edit_outletL',
  style='$edit_outletS',
  coName='$edit_coName',
  coNum='$edit_coTel',
  coEmail='$edit_coEmail',
  password='$edit_pass'
 WHERE
  id='$query_title'
";
$edit_query = mysql_query($q) or die("Database Query Error: ". mysql_error());

Database Query 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 'add='Llancadle, Nr Barry', pc='CF62 3AQ', location='rural', style='food', coName' at line 1

escape field names by surrounding them with back-tick like ``add`` — Nishant, Jan 11 '11 at 10:20
Why is there a vote to close? It's a perfectly acceptable question to ask is it not? — Dan Hanly, Jan 11 '11 at 12:41

score 4 · Accepted Answer · answered Jan 11 '11 at 10:19

4

You neeed to backquote add since it is a keyword:

`add` = ...

answered Jan 11 '11 at 10:19

Nylon Smile

8,990
1
25
34

I suppose that's the danger of only scratching the surface of MySQL's capabilities, I can't recognise errors like this. Cheers mate! – Dan Hanly Jan 11 '11 at 10:23
The general advice is to backquote all the field names and table names to avoid such errors. – Nylon Smile Jan 11 '11 at 10:26
lol - that's just a ridiculous suggestion. – Jon Black Jan 11 '11 at 10:38
If you create a SELECT query in phpMyAdmin, it backquotes everything for you so I can see where @Nylon's suggestion came from – Dan Hanly Jan 11 '11 at 12:39

score 3 · Answer 2 · answered Jan 11 '11 at 10:19

3

I think add is a reserved word in MySQL.

answered Jan 11 '11 at 10:19

Shikiryu

10,180
8
49
75

score 2 · Answer 3 · answered Jan 11 '11 at 10:20

your problem is that "add" is a MySQL reserved word. See: http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html. If you have a column named "add", escape it like this:

//update database
    $edit_query = mysql_query("UPDATE users SET id='$edit_id', name='$edit_name', bdm='$edit_bdm', `add`='$edit_add', pc='$edit_pc', location='$edit_outletL', style='$edit_outletS', coName='$edit_coName', coNum='$edit_coTel', coEmail='$edit_coEmail', password='$edit_pass' WHERE id='$query_title'") or die("Database Query Error: ". mysql_error());

score 2 · Answer 4 · answered Jan 11 '11 at 10:21

2

as bobby noted in a comment, add is a mysql reserved word

`add`='$edit_add'

will tell mysql you are talking about a column

answered Jan 11 '11 at 10:21

Alan Whitelaw

16,171
9
34
51

SQL Syntax Error near ADD

4 Answers4

Linked