SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard
    Join Date
    Oct 2005
    Location
    London
    Posts
    1,678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    A query that selects all the most updated/new content

    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:

    PHP Code:
    SELECT entries.*, categories.*, uploads.*, images.*, entries.blog_identries.category_identries.author_idDATE_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_idORDER 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
    Last edited by elduderino; May 9, 2007 at 02:31.

  2. #2
    SitePoint Wizard bronze trophy devbanana's Avatar
    Join Date
    Apr 2006
    Location
    Pennsylvania
    Posts
    1,736
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Wizard
    Join Date
    Oct 2005
    Location
    London
    Posts
    1,678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SitePoint Wizard
    Join Date
    Oct 2005
    Location
    London
    Posts
    1,678
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •