Simple MySQL INSERT error...

Question

No doubt I'm missing something really simple here but I just can't see the problem with this query which is producing the following error:

SQL query:

INSERT INTO ads(
    ad_id, author, ad_date, category, title,
    description, condition, price, fullname,
    telephone, email, status, photo, photothumb
)
VALUES (
    NULL , 'justal', '1225790938', 'Windsurf Boards',
    'test', 'test', 'Excellent', '12', 'test',
    'test', 'test', '', '', ''
);

MySQL said: Documentation
#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 ''ad_id', 'author',
'ad_date', 'category', 'title', 'description',
'condition', '' at line 1

Can someone with a fresh pair of eyes spot the problem?

Thanks, Al.

Answer 1

Shouldn't you use back ticks instead of single quotes in column names?

INSERT INTO ads( `ad_id`, `author`, `ad_date`, `category`, `title`, `description`, `condition`, `price`, `fullname`, `telephone`, `email`, `status`, `photo`, `photothumb` )
VALUES (
NULL , 'justal', '1225790938', 'Windsurf Boards', 'test', 'test', 'Excellent', '12', 'test', 'test', 'test', '', '', ''
);

Answer 2

Single quotes are used for string literals. In MySQL, by default, double quotes are also used for string literals although this is incompatible with standard SQL and you should stick to single quotes in your code.

For column names, you normally wouldn't quote them at all. If you need to - and you don't for any of yours - then quote them with a backquote (`), or set it to strict ANSI compatible mode (ANSI_QUOTES) and use double quotes.

Answer 3

Thanks.... I didn't originally have any quotes on the column names and that didn't work either.

SQL query:

INSERT INTO ads( ad_id, author, ad_date, category, title, description, condition, price, fullname, telephone, email, status, photo, photothumb )
VALUES (
'', 'justal', '1225790938', 'Windsurf Boards', 'test', 'test', 'Excellent', '12', 'test', 'test', 'test', '', '', ''
)

MySQL said: Documentation
#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 'condition, price, fullname, telephone, email, status, photo, photothumb) VALUES ' at line 1

This query was working fine until the server was updated to php5 if that helps anyone?

Thanks, Alan.

Answer 4

Aha.... The backticks seems to do the trick... I think the column with the name status was causing the problems.

Thanks, Al.

Answer 5

It might be that CONDITION is a mysql keyword, and not allowed as column name.

http://dev.mysql.com/doc/refman/5.1/en/declare-conditions.html

You definitely do not need the ' or `