Get 50 before an after my rank?

[jmansa]

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:

$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:

$sql = "SELECT * FROM nf_users WHERE played > 0 ORDER BY points DESC LIMIT 100"; 

Any ideas how to do this? Thanks in advance :-)

[ServerStorm]

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

[cpradio]

Okay, I think the following would work

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

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

[r937]

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?

[cpradio]

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

$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.

[jmansa]

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 :-)