What's wrong with this SQL statement - Syntax Error #1064?

Question

SELECT id, content, UNIX_TIMESTAMP(timestamp) AS unixtime 
FROM data
ORDER BY id ASC 
WHERE unixtime <= CURDATE() LIMIT 10;

or

SELECT id, content, UNIX_TIMESTAMP(timestamp) AS unixtime 
FROM data
ORDER BY id ASC 
WHERE unixtime < CURDATE() LIMIT 10;

all phpAdmin is telling me is:

#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 'WHERE unixtime <= CURDATE() LIMIT 10' at line 1

Answer 1

Try the following:

SELECT id, content, UNIX_TIMESTAMP(timestamp) AS unixtime 
FROM data 
WHERE UNIX_TIMESTAMP(timestamp) <= CURDATE() 
ORDER BY id ASC LIMIT 10;

Answer 2

You have the ORDER BY and WHERE clauses reversed.

SELECT id, content, UNIX_TIMESTAMP(timestamp) AS unixtime 
FROM data
WHERE unixtime < CURDATE()
ORDER BY id ASC LIMIT 10;

or

SELECT id, content, UNIX_TIMESTAMP(timestamp) AS unixtime 
FROM data
WHERE unixtime <= CURDATE() 
ORDER BY id ASC LIMIT 10;

I just realized that part of your issue because you are referencing an alias in your WHERE clause.:

WHERE UNIX_TIMESTAMP(timestamp) <= CURDATE() 

or

WHERE UNIX_TIMESTAMP(timestamp) < CURDATE() 

Answer 3

try this

SELECT id, content, UNIX_TIMESTAMP(timestamp) AS unixtime 
FROM data
WHERE (UNIX_TIMESTAMP(timestamp)) < CURDATE() ORDER BY id ASC  LIMIT 10;

Answer 4

You need the where statement before the order by statement.