SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Get 50 before an after my rank?

    I'm trying to create a rankingf list with 100 (101) listed users depending on the current users rank. So if the user is rank 257 I would like to list the 50 higher ranked than him and the 50 lower ranked than him but not sure how to go about this. I get the current users rank like this:
    PHP Code:
    $sql mysql_query("SELECT COUNT(*) rank FROM ".$prefix."_users WHERE points > (SELECT points FROM ".$prefix."_users WHERE id = $playerid) AND played > 0") or die(mysql_error());
    $row mysql_fetch_assoc($sql);

    $rank $row['rank']+1
    Normally I get the top 100 out of my list like this:
    PHP Code:
    $sql "SELECT * FROM nf_users WHERE played > 0 ORDER BY points DESC LIMIT 100"
    Any ideas how to do this? Thanks in advance :-)

  2. #2
    Foozle Reducer ServerStorm's Avatar
    Join Date
    Feb 2005
    Location
    Burlington, Canada
    Posts
    2,699
    Mentioned
    89 Post(s)
    Tagged
    6 Thread(s)
    Hi

    I moved this to the Database & MySQL forum as this is likely better served return the results exclusively using SQL and then presenting them with PHP. I may be wrong, so if this needs to be moved back to the PHP then can one of the experts in SQL say so and it can be moved back.

    I tackled this by Self-Joining but never quite got the results that you/I were looking for; otherwise I would have helped you myself .

    Regards,
    Steve
    ictus==""

  3. #3
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,220
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Okay, I think the following would work
    Code:
    SELECT * FROM ".$prefix."_users WHERE played > 0 AND id != $playerid ORDER BY points LIMIT $rank-50, 100
    Now to explain it. You only want those who have played (so hence the played > 0), and you don't want to include the existing player, so that explains the id != $playerid.
    Then I ordered by points, as that was the comparison used for rank, then I used LIMIT's ability to indicate a starting position, which would be the current person's rank minus 50 (may need to be 51, so watch your results) and told it to grab 100 records, which should be 49/50 higher and 50/51 lower ranked individuals (against watch your records and change the $rank-50 accordingly).

    Query in PHP syntax
    PHP Code:
    $sql mysql_query("SELECT * FROM ".$prefix."_users WHERE played > 0 AND id != $playerid ORDER BY points LIMIT ".($rank-50).", 100") or die(mysql_error()); 

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,327
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    cp, that's sweet, but where does $rank come from?

    also, what if $rank is less than 50? how do you prevent generating a LIMIT clause with a negative offset?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    Hosting Team Leader silver trophybronze trophy
    cpradio's Avatar
    Join Date
    Jun 2002
    Location
    Ohio
    Posts
    5,220
    Mentioned
    153 Post(s)
    Tagged
    0 Thread(s)
    Rank was defined in the first query (so my resolution, utilizes the first query to determine the current players rank and then a second query to get the others).

    Your concern for the less than 50 is valid, so I'll adjust my php to handle that scenario
    PHP Code:
    $limitStart = ($rank >= 50) ? $rank 50 0;
    $limitNumber = ($rank >= 50) ? 100 100 $rank;
    $sql mysql_query("SELECT * FROM ".$prefix."_users WHERE played > 0 AND id != $playerid ORDER BY points LIMIT ".$limitStart.", ".$limitEnd) or die(mysql_error()); 
    Okay, now to explain the calculations for $limitStart and $limitEnd

    $limitStart
    If $rank is greater than or equal to 50 (again, change to 50 to 51 if the results show it should be), subtract 50/51 from the $rank to get your start position.
    If the current player's rank is less than 50, start at 0 (although I think starting at a negative number is valid).

    $limitEnd
    If $rank is greater than or equal to 50, use 100, as that should give you 50 prior and 50 after (assuming there are at least 100 players).
    If the current player's rank is less than 50, subtract the current player's rank from 100, so that you end up with 50 after the player's rank and the X number prior to his rank.
    For example, if the players rank is 27, you would perform 100 - 27, which is 73. So you would start at 0, and get the next 73 records. 27 of those were ranked ahead of the current player, the other 50 are after his rank.

  6. #6
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by cpradio View Post
    Rank was defined in the first query (so my resolution, utilizes the first query to determine the current players rank and then a second query to get the others).
    Just what I was searching for allthough... I wanted the current player to also be a part of the list so I just removed the id != $playerid and everything was fine... 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
  •