SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    order by a number of votes in a certain time period

    right so what im trying to do is order a list of images by how many votes they got in a certain amount of time (most votes today, most votes this week, this month, etc), and i have two tables, one with the images information and the other with the votes... their structured something like this

    image_table
    -id
    -image_title
    -date_added
    -etc

    votes_table
    -vote_id
    -image_id
    -date_voted
    -user_id
    -etc

    and im completely clueless on how to do this... can anyone help?

    thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT i.id
         , i.image_title
         , i.date_added
         , v.votes
      FROM image_table AS i
    INNER
      JOIN ( SELECT image_id
                  , COUNT(*) AS votes
               FROM votes_table
              WHERE date_voted >= '2009-01-01
                AND date_votes  < '2009-02-01'
             GROUP
                 BY image_id
           ) AS v
        ON v.image_id = i.image_id
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanks, just had a couple of questions though, does it matter that the field date_voted is a timestamp, or does it have to be formated like you have it there "2009-01-01", also does that grab the votes from a certain day, or past 24 hours.

    oh and what if an image doesnt have any votes that day, will it still show up...

    sorry these are a lot of questions, this just seems a lot more complicated than i first imagined..

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Posts
    977
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try out rudy's suggestion to see it work.

    As for you questions:

    the data type for your column is determined by the content. If you only have an ISO date then the 'date' type should be adequate/best

    That script will grab votes from whatever dates you have in the query ~ again, try it out.

    bazz

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by franco714 View Post
    does it matter that the field date_voted is a timestamp
    depends on the DATATYPE

    all i had to go on was the name of the column

    Quote Originally Posted by franco714 View Post
    also does that grab the votes from a certain day, or past 24 hours.
    the example i used was an entire month

    Quote Originally Posted by franco714 View Post
    oh and what if an image doesnt have any votes that day, will it still show up...
    another new twist -- just use a LEFT OUTER JOIN instead
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    398
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    got it working, thanks a lot rudy


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
  •