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:
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...
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
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
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