CODEWITHSUNDEEP
phpmysqlhtmlleaderboard
Daniel
Daniel

Reputation: 55

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

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!

Upvotes: 2

Views: 9150

Answers (4)

Rhys Broughton
Rhys Broughton

Reputation: 480

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.

Upvotes: 0

Marvo
Marvo

Reputation: 18143

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.

Upvotes: 1

MrPhoton
MrPhoton

Reputation: 11

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

Upvotes: 1

Josh
Josh

Reputation: 8191

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>

Upvotes: 2

Related Questions