CODEWITHSUNDEEP
phpmysql
user1124382
user1124382

Reputation: 23

Counting rows in MySQL (highscore)

I've created a game with a highscore table in MySQL.

I have a "My scores" button that needs to retrieve the users scores, e.g.:

10. John  395

42. John  340

90. John  10

How should I go out retrieving the rank (10th, 42th, 90th) of each score of the user? I could pull all the scores from the database and iterate through them but that doesn't seem like a good solution.

Let me try to expand: I retrieve all MY scores from the database. E.g. 10 scores. I want to display these 10 scores however I won't know what the rank of these scores is compared to the other scores in my database! (10th, 16th, etc) ..Hope that makes more sense...

Thanks

Upvotes: 2

Views: 411

Answers (4)

Eugen Rieck
Eugen Rieck

Reputation: 65274

SELECT 
  id, name, score,(select count(*) FROM highscores AS higherscores WHERE higherscores.score>currentscores.score)+1 AS rank
FROM
  highscores AS currentscores
WHERE name="john"
;

Upvotes: 1

White Elephant
White Elephant

Reputation: 1381

A simple SQL query would do it for you. For example, if you want the 10th score, you could use:

SELECT name, score FROM highscores ORDER BY score DESC LIMIT 1 OFFSET 9

The offset will always be the position required - 1.

If you want to have the associated rank as a column beside the score, you could do the following:

SELECT @rownum:=@rownum+1 position, name, score FROM (SELECT @rownum:=0) r, highscores ORDER BY score DESC

This doesn't work with an offset (the position number will always start at 1). The result would be something like the following:

+----------+-------------+-------+
| position | name        | score |
+----------+-------------+-------+
|        1 | Player 1    | 27681 |
|        2 | Player 2    | 14982 |
+----------+-------------+-------+

But I think the best solution is to just loop through the returned values with an index and use the index to keep track of the position.

Upvotes: 1

FWijmans
FWijmans

Reputation: 66

For the position in the total list you either need to build up a list every time you want this overview, or use a stored procedure to build a list for a given moment. You could 'cache' a list on a given interval. Or maybe update a list when some one played a game that would change the top 100.

As @WhiteElephant suggested, you'd be making the table every time you want the data. @stefandoorn suggest to not use the optimized count of sql, i think this is not efficient enough for these kind of computations.

Upvotes: 1

stefandoorn
stefandoorn

Reputation: 1032

Just use ORDER BYscoreDESC in the end of your query to sort them in reversed order (from high to low). When iterating and showing it in PHP you can use a count:

Query e.g.: SELECT * FROM scores ORDER BY score DESC

$count = 1;
$sql = 'SELECT * FROM scores ORDER BY score DESC';
$result = mysql_query($sql) or die(mysql_error());
while($fetch = mysql_fetch_object($result)) {
    echo $count . ' ' . $fetch->score . '<br />';
    $count++;
}

Upvotes: -1

Related Questions