View Full Version : [solved] Intermingling most current "Posted" and "Updated" MySQL query results

09-13-2005, 10:44 AM
I'm trying to figure out how to display articles stored in a MySQL database in the order they were either last updated or originally posted, whichever is later, and display the 5 most current results on the main page. Currently, these are two separate variables, so both "Posted" and "Updated" dates can be displayed on each article's page.

Example of what I want:

Posted: Sept 12
Updated: Sept 9
Posted: Sept 8
Posted: Sept 6
Updated: Sept 3

Could this be done in the query itself, or would I need two separate queries (SELECT...ORDER BY posted DESC LIMIT 0, 5) and (SELECT...ORDER BY updated DESC LIMIT 0, 5), then choose the 5 most current from those 10 results and integrate them with code from there? :confused:

"SELECT ... ORDER BY updated DESC, date DESC LIMIT 0, 5";
...obviously doesn't work, since it will pull updated before posted, regardless of whether something was posted after something else was updated or not.

Thanks in advance. :)

09-13-2005, 12:28 PM
why don't you add a timestamp (a column of type timestamp) and then just order on that? When the record is created + each time that the record is changed, the timestamp is automatically updated so if you just order on that ...

09-13-2005, 08:25 PM
Ah, so basically when a new article is added, a timestamp can be applied for $posted (as is already done) as well as a new variable like $listing_order (or whatever), and every time an article is updated, it would insert the same timestamp for both $updated and $listing_order, and then I could simply ORDER BY 'listing_order'.

I was so concerned with getting the variables out that were already in there that I hadn't even thought of adding a new one, lol.

Thanks, raf! :p