Mysql – How to Paginate a MySql Ranking Query

MySQL

I have the following ranking query working fine with a limit of 25 records per page, it shows 1st – 25th for the first 25 records, but when i go to the next page, the rank stays the same 1st – 25th when it should say 26th – 50th. 

SELECT image, name, wins, losses, level, xp, wins AS W, losses AS L,
TRUNCATE(wins/IF(wins=0,1,wins+losses)*100,0) AS PCT, 
@curRank := @curRank + 1 AS rank 
FROM teams p
, (SELECT @curRank := 0) r 
WHERE `console` = 3 AND `game` = 23 AND `laddertype` = 1 
ORDER BY xp DESC LIMIT " . mysql_real_escape_string($limit) . ", 25"

Best Answer

Here

FROM teams p
, (SELECT @curRank := 0) r

you are resetting the value for @curRank to 0 every time you execute the query. Make this dynamic as well, adjust it according to your LIMIT clause.

On the second run change it to 

FROM teams p
, (SELECT @curRank := 25) r
Related Question