Querying MySQL with UNION and I'm getting an error on one of the columns I'm selecting

Question

The query works just fine like:

$query = '(SELECT contenttype, id, title, type, image, rating, date2, num_votes FROM videos)
UNION
(SELECT contenttype, id, title, type, image, rating, date2, num_votes FROM games)
UNION
(SELECT contenttype, id, title, type, image, rating, date2, num_votes FROM pics)
ORDER BY date2 DESC
LIMIT 10';
$r = mysql_query($query) or die(mysql_error());

but WILL NOT work if I try to pull the column, on. All of the tables in the database use a column named on. I get this 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 'on, type, image, rating, date2, num_votes FROM videos) UNION (SELECT contentty' at line 1

example of me adding on to me query:

$query = '(SELECT contenttype, id, title, type, on, image, rating, date2, num_votes FROM videos)
UNION
(SELECT contenttype, id, title, type, on, image, rating, date2, num_votes FROM games)
UNION
(SELECT contenttype, id, title, type, on, image, rating, date2, num_votes FROM pics)
ORDER BY date2 DESC
LIMIT 10';
$r = mysql_query($query) or die(mysql_error());

Doesn't make sense to me. Why would this occur?

score 2 · Answer 1 · edited Jan 20 '14 at 15:09

2

Escape the on field as it's one of MySQL's reserved keywords

$query = '(SELECT contenttype, id, title, type, `on`, image, rating, date2, num_votes FROM videos)
UNION
(SELECT contenttype, id, title, type, `on`, image, rating, date2, num_votes FROM games)
UNION
(SELECT contenttype, id, title, type, `on`, image, rating, date2, num_votes FROM pics)
ORDER BY date2 DESC
LIMIT 10';

edited Jan 20 '14 at 15:09

Funk Forty Niner

74,450
15
68
141

answered Jan 20 '14 at 14:58

Nouphal.M

6,304
1
17
28

You rock! Thanks. I actually used to have them all quoted that way. Works great! – user3207931 Jan 20 '14 at 15:03

score 1 · Answer 2 · edited Jan 20 '14 at 14:59

1

ON is a reserved keyword in MySQL. Quoting it should work. I.e:

(SELECT contenttype, id, title, type, `on`, image, rating, date2, num_votes FROM videos)
UNION
(SELECT contenttype, id, title, type, `on`, image, rating, date2, num_votes FROM games)
UNION
(SELECT contenttype, id, title, type, `on`, image, rating, date2, num_votes FROM pics)
ORDER BY date2 DESC
LIMIT 10

edited Jan 20 '14 at 14:59

Michael Berkowski

267,341
46
444
390

answered Jan 20 '14 at 14:58

Jim

22,354
6
52
80

You rock! Thanks. I actually used to have them all quoted that way. Works great! – user3207931 Jan 20 '14 at 15:03

Querying MySQL with UNION and I'm getting an error on one of the columns I'm selecting

2 Answers2