SitePoint Sponsor

User Tag List

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

    ORDER BY Algorithm

    Hello

    I am working on a php/mysql driven website where users can make posts.
    Other users can vote up/down on these posts.

    I am working on an Algorithm to order these posts.
    I want the posts with the highest NET points to be displayed on top, however I also want to give priority to new posts.

    This is what I have so far,

    Code:
    ORDER BY points_up - points_down DESC, timestamp DESC
    However I want to do something along these lines.

    Code:
    (points_up - points_down) / minutes
    The minutes being the number of minutes the post has been active. However I'm not sure how to implement the minutes feature using my timestamp.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    "the number of minutes the post has been active"

    where is this information coming from?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am using the timestamp along with a php script to display how many minutes old a post is.

    PHP Code:
    $todaydate date("Y-m-d H:i:s");
    $ago strtotime($todaydate) - strtotime($row['timestamp']);
    if (
    $ago >= 86400) {
    $diff floor($ago/86400).' Days ago';
    } elseif (
    $ago >= 3600) {
    $diff floor($ago/3600).' Hours ago';
    } elseif (
    $ago >= 60) {
    $diff floor($ago/60).' Minutes ago';
    } else {
    $diff $ago.' Seconds ago';
    }
    $diff2 floor($ago/60).' Minutes ago'

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    i'm sorry, i don't do php

    i was actually asking about your ORDER BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well currently all I have is the timestamp that contains the time and date the post was submitted.

    I want to know how I can divide the NET total by the timestamp.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Jaynesh View Post
    I want to know how I can divide the NET total by the timestamp.
    i thought you actually wanted to divide by minutes since the post has been active

    what is the DATATYPE of the timestamp column?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Addict
    Join Date
    Sep 2006
    Posts
    238
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi

    The datatype is 'timestamp'

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    Code:
    ORDER
        BY (points_up - points_down) / 
             ( ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(timestamp) / 60 )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,264
    Mentioned
    115 Post(s)
    Tagged
    1 Thread(s)
    Are you missing a closing parenthesis? I count two opening and one closing on the timestamps...

    Quote Originally Posted by r937 View Post
    Code:
    ORDER
        BY (points_up - points_down) / 
             ( ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(timestamp) / 60 )
    Should it be....
    Code:
    ORDER
        BY (points_up - points_down) / 
             ( ( UNIX_TIMESTAMP() - UNIX_TIMESTAMP(timestamp)) / 60 )
    ???
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,251
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    nicely spotted, thanx
    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
  •