Creating a leaderboards, how would I go about displaying rank/position?

Question

I am creating a leaderboards which will display the following: Rank, Username, Score

I currently have the table to it will display Username and Score from the data in a mysql table, I am just wondering how would I go about displaying a rank for each user, number 1 being the user with the highest score then descending.

Thanks!

Hi, I already have that I am trying to make it show a rank/position on the leaderboard, such as this: http://i.imgur.com/ed9Zx.png (quickly done that on excel) — Daniel, Aug 17 '11 at 17:00

score 2 · Accepted Answer · answered Aug 17 '11 at 17:09

I recommend reading up on PHP/MySQL.

HTML Header: Open your table, create your headers

<table>
    <tr>
        <td>Rank</td>
        <td>User</td>
        <td>Score</td>
    </tr>

PHP: Dynamically generate the rows for each user

    <?php

        $result = mysql_query("SELECT user, score FROM leaderboard ORDER BY score DESC");
        $rank = 1;

        if (mysql_num_rows($result)) {
            while ($row = mysql_fetch_assoc($result)) {
                echo "<td>{$rank}</td>
                      <td>{$row['user']}</td>
                      <td>{$row['score']}</td>";

                $rank++;
            }
        }
    ?>

HTML Footer: need to close the table

</table>

score 1 · Answer 2 · answered Aug 17 '11 at 17:41

1

You can do the whole thing in SQL:

SET @row = 0;
SELECT
    @row := @row + 1 AS rank
    userName,
    score
FROM
    leaderboard
ORDER BY
    score DESC

answered Aug 17 '11 at 17:41

MrPhoton

11
3

Considering that the asker didn't specify the need to compute ties in the original question, I didn't take that into consideration. If that's a requirement, it'd be easier to handle in a loop outside of SQL. – MrPhoton Aug 17 '11 at 18:32

score 1 · Answer 3 · answered Aug 17 '11 at 17:53

You need to 1) get the score of the record you're trying to rank 2) count the number of records with a "better" score ("better" is dependent on your type of game. Basketball, higher scores are better. Golf, lower scores are better.)

So, something like

select records in order
for each record
    score = record.score
    rank = select count(*) + 1 from table where score_column is better than score
    display data
end for

The problem is that performing the count(*) on any significant amount of data is slow. But you can see that once you have the rank of the first TWO differing scores, you can determine the rank of the remaining rows in your code without a query. But remember: you probably need to account for ties.

score 0 · Answer 4 · answered Apr 04 '22 at 23:42

I agree with

    $result = mysql_query("SELECT user, score FROM leaderboard ORDER BY score DESC");
    $rank = 1;

    if (mysql_num_rows($result)) {
        while ($row = mysql_fetch_assoc($result)) {
            echo "<td>{$rank}</td>
                  <td>{$row['user']}</td>
                  <td>{$row['score']}</td>";

            $rank++;
        }
    }
?>`

But I had an issue with procedural mysql so I opted for the OOP version that I couldn't find a lot of documentation on. But this should give you the same result if @Josh's answer isn't working for you.

<?php
        $query = 'SELECT user, score FROM leaderboard ORDER BY score DESC';
        $result = $conn ->query($query);
        while ($row = $result->fetch_row()){
            echo"<td>{$row[0]}</td>
                <td>{$row[1]}</td>";
        }
        ?>

And of course if you want the rank to show you would add this also like above.

Creating a leaderboards, how would I go about displaying rank/position?

4 Answers4

Related