PHP & MySQL – sorting / order by multiple columns
Ok so I have a website with news entries. They look something like [id, year, month, day, newsitem]. Now, I want them to be listed in descending order (most recent first). A way to do this is to convert the [year,month,day] tuple into a php date() and then sort it using php’s sort() function. An alternative is to let MySQL do the sorting for you.
An excellent article in the MySQL 5.0 Reference Manual explains something about this, though in my case the results were odd. The query looks like this:
SELECT year, month, day, newsitem FROM news ORDER BY year, month, day DESC;
But it seems that the DESC in this case only applies to the last field (the ‘day’ in this example). Not what we want. A fix is easy:
SELECT year, month, day, newsitem FROM news ORDER BY year DESC, month DESC, day DESC;
Which gives the exact (chronologically) ordering I want. Most recent news first. Strange enough, this syntax is not in the manual (though rather intuitive I’d say). Hope it helps someone googling for it.