Ranking/numbering in php

I tried to achieve a method in which I create a scoreboard (while loop) and order the fetched results by a certain numeric field (points). But what I need to achieve is like the following

rank----username--point
1st------test-----------3200
2nd-----test2---------1200

etc.. I paginate my results and limit 25 results per page. I tried a while loop in the following way

while($row = mysql_fetch_array($query) || $i<=$totalnumberofrows ){
   echo out the results
$i++
}

What this does is number from 1 to 25 perfectly, but on the other hand, on page 2 it numbers again from 1-25. Is there a trick to achieve what I need to achieve? I.e. continuous numbering from 1 to (total number of rows) even when paginated.

Answers


What you're looking for is the MySQL LIMIT statement. For example, the following query would return entry 0-24 (so, the first 25 entries in your database):

SELECT * FROM entries ORDER BY `points` LIMIT 0, 25

Say that you want to fetch the second page (the next 25 entries), you can do:

SELECT * FROM entries ORDER BY `points` LIMIT 25, 25

Most important thing to note here is that the first argument is the row where it should start, and the second argument is not the last row, but the total amount of rows it should return.

To determine the starting point, simply do (($page_number - 1) * 25), assuming your first page is numbered 1. You could for example do the following:

<?php
$start = (($page_number - 1) * 25);
$query = "SELECT * FROM entries ORDER BY `points` LIMIT {$start}, 25";
// ... rest of your code goes here ...
?>

just add 25 to the number for each page, for the second page (in my example) - add 25 for the third page - add 50 etc...

$page = 2;
while($row = mysql_fetch_array($query) || $i<=$totalnumberofrows ){
   echo out the results
   echo (($page-1)*$totalnumberofrows)+$i;
   $i++
}

If you know the page number and the offset you should be able to do the following

$rank = $offset * $page_number;

Need Your Help

Doctrine MongoDB ODM nullable=false not working

mongodb symfony doctrine-orm nullable doctrine-odm

I'm using the master branch of Doctrine MongoDB ODM and Symfony2 from the git repositories along with mongo extension 1.2.10.

Specify MongoDb collection name at runtime in Spring boot

java spring mongodb spring-boot

I am trying to reuse my existing EmployeeRepository code (see below) in two different microservices to store data in two different collections (in the same database).