SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    how to get the neweset one week recored in mysql?

    how to write the code in php that get the articles' title with the most replies in latest one week in mysql? eg: the table name is "threads". the time field is "dateline" whose style is like this (1300774889), the article title is "subject". the reply field " replies" thank you.i want to get 10 records.

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    What SQL do you have now?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i guessed the articleid column
    Code:
    SELECT threads.subject
         , COUNT(replies.replies) AS replies
      FROM threads
    LEFT OUTER
      JOIN threads AS replies
        ON replies.replies = threads.articleid
       AND replies.dateline >= UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 7 DAY)
    GROUP
        BY threads.subject
    ORDER
        BY replies DESC LIMIT 10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Evangelist runeveryday's Avatar
    Join Date
    Jul 2009
    Posts
    437
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    i guessed the articleid column
    Code:
    SELECT threads.subject
         , COUNT(replies.replies) AS replies
      FROM threads
    LEFT OUTER
      JOIN threads AS replies
        ON replies.replies = threads.articleid
       AND replies.dateline >= UNIX_TIMESTAMP(CURRENT_DATE - INTERVAL 7 DAY)
    GROUP
        BY threads.subject
    ORDER
        BY replies DESC LIMIT 10
    could i use this (time()-3600*24*7) to represent the latest one week

    you command is complicated

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by runeveryday View Post
    could i use this (time()-3600*24*7) to represent the latest one week
    yes

    Quote Originally Posted by runeveryday View Post
    you command is complicated
    yes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •