0

I'm trying to write a leaderboard where I can show the player's position and all the scores around him (maybe 10 above him and 10 below). Maybe this user's score is at #95,000 so my first question is: how do I know his position, and how can i grab the scores above and below him based on that information?

$sql = "SELECT id, firstname, lastname, facebookid, score FROM users ORDER BY score";
$result = $conn->query($sql);

That sorts the scores, but what if I want to find where the facebook id "0002941" is ranked?

I'm using this script to go through and view all of them, but it takes too long to scan to find this user. I'm hoping it's possible to jump to them instantly:

if ($result->num_rows > 0) {
  while($row = $result->fetch_assoc()) {
   echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"] . " " . $row["score"] . " end";
   echo '<br>';
  }
}
user3617651
  • 43
  • 1
  • 3
  • so you want to know their position, in relation to the entire table? but just get them, and 10 before/after? – Sean Nov 23 '15 at 05:25
  • how many rows do you have. What is the table schema with indexes – Drew Nov 23 '15 at 05:25
  • Yeah, after sorting by score, I want to know where someone is in the leaderboard - without having to scan the entire table. I have about half a million rows. I want someone to be able to jump right to where their score is in the leaderboard. If I scan the entire thing it takes about 1.2 seconds – user3617651 Nov 23 '15 at 05:29
  • 1
    and do you know you have 4 questions (you are new) that have answers on them, and you gave no feedback or checked any as answered. Before anyone spends 20 minutes on this? – Drew Nov 23 '15 at 05:29
  • 1
    Possible duplicate of [MySQL get row position in ORDER BY](http://stackoverflow.com/questions/3614666/mysql-get-row-position-in-order-by) – Sean Nov 23 '15 at 05:36

0 Answers0