A query that selects all the most updated/new content

[elduderino]

hi, sorry this is a long post but i dont know how else to put it!

I have a blog system. I want to show all the recently updated content on the front page. Updated meaning either a comment has been posted on an article, an article has been edited by its author or an article has been published that is newer than enything else posted....so i have the foundations there....my comments table has a datetime field and gets set to now() when a comment is posted....when an article is edited its datetime field is also set to now() and when a new article is created a datetime field is set to now()...


Now my problem is the select query on the home page. How do i do a select that gives me say the ten articles that have been updated/created in one of the ways mentioned above? the existing query i have does a select that
gets out the 5 most recently posted articles...here it is:

SELECT entries.*, categories.*, uploads.*, images.*, entries.blog_id, entries.category_id, entries.author_id, DATE_FORMAT(entries.date_submitted, '%D %M %y') as date_submitted,
        ( SELECT COUNT(*) FROM comments WHERE comments.blog_id = entries.blog_id ) as comments FROM entries 
        INNER JOIN categories ON(categories.category_id = entries.category_id) 
        LEFT JOIN uploads ON (entries.blog_id = uploads.blog_id) 
        LEFT JOIN images on (entries.blog_id = images.blog_id) ORDER BY entries.date_submitted DESC LIMIT 5 

Now this works fine but i have no clue as to where to begin changin it to get it working as i want...could anyone point me in the right direction with this? Ive attached an image of the two tables concerned in the query...
I think the thing thats confusing me is that i need to do a query that does all the things that the above query does but somehow order it by either a comments been made, an article created or an article created and im probably making it more complicated than it need be.

Ive googled for this but cant find an answer specfic enough..any help greatly appreciated

[img=http://img177.imageshack.us/img177/2708/picture4qp9.th.png]

ps: you really dont need to point out how badly written that query is! I wrote it about 3 months ago...ive come on a bit since then

[devbanana]

How about have one column that is the creation date, and another that is the last modification date. When submitted, set the last modification date to the same as the creation date, but when it is modified only update the last modification date. Then you can order by that last modification date.

[elduderino]

ooh right thats good...so simple! Thanks for the reply

But how would i get the comments thing going with that.....i cant think how to do it....as you see from my db image i dont tie the comment table to entries in any way....i just do a seperate query to the comments table to get all the comments out where the blog_id is = to whats in the $_GET...i dont know how to tie them together

[elduderino]

aha...sorry stupid of me.....ive have it working now. I used devabana's idea of a last_updated column and then when someone posts a comment i just did another query straight after a successful comment query that updated the last_updated colummn in the entries table.....if that makes any sense to anyone

Thanks