Using ORDER BY with a UNION query

[excelsior9]

Ok,

So I've got this UNION query running:

$query = "SELECT id,title,authorcomments,author,keywords,date_format(postdate, '%W %M %D, %Y') AS readable_date FROM artwork
WHERE authorcomments LIKE '%$trimmed%' OR title LIKE '%$trimmed%' OR keywords LIKE '%$trimmed%'
UNION SELECT id,title,poemtext,authorcomments,author,keywords,date_format(postdate, '%W %M %D, %Y') AS readable_date FROM poems
WHERE authorcomments LIKE '%$trimmed%' OR title LIKE '%$trimmed%' OR keywords LIKE '%$trimmed%' ORDER BY postdate DESC";


... but with that ORDER BY clause, I get this error message:

"unknown column 'postdate' in 'order' clause", but obviously there IS a postdate column being queried ...

Any ideas?

 

[DonQuichote]

I think you should include the postdate as a field in the query or sort by readable_date instead.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[r937]

you also have a column alignment problem, there is an extra column (poemtext) in the 2nd SELECT which will generate another error once you solve the postdate problem

r937.com | rudy.ca