Leaderboard rank based on time played MySQL PHP

Question

Trying to make a leaderboard for players in a game for their playtime. giving players a rank based on their "time" descending. Below is how I have tried to make a rank however does not work;

<thead class="table-time">
<tr>
    <th>Rank</th>
    <th>Name</th>
    <th>SteamID</th>
    <th>Playtime</th>
</tr>
</thead>
<tbody>
<?php

// Select Time
$SelectTime = $db->query("SELECT * FROM `users` ORDER BY time DESC");
$ranking = 1;
$rank = $ranking + 1;

// Print Output
foreach ($SelectTime as $PrintTime) {
    echo "<tr>
            <td><b>" . $rank . "</b></td>
            <td><b>" . $PrintTime['name'] . "</b></td>
            <td><a href='https://steamcommunity.com/profiles/" . $PrintTime['steamid'] . "' target='_blank'>" . $PrintTime['steamid'] . "</a></td>
            <td>" . $PrintTime['time'] . "</td>
        </tr>";
}
?>
</tbody>

What PHP database extension are you using, `mysqli` or `PDO`? What specifically is your code supposed to do that it is not? — Will B., Jul 30 '20 at 17:18
I am using PDO. I want the table to have a "rank" column, showing playtime descending. — Dan Miller, Jul 30 '20 at 17:36
@DanMiller The rank value not incrementing 1 issue is caused by a typo, You need to rename `$ranking = 1` to `$rank = 0;` And move `$rank = $ranking + 1` into your `foreach()` loop and rename it to `$rank = $rank + 1;` or `$rank++` — Will B., Jul 30 '20 at 17:40
*"Call to undefined method PDOStatement::fetch_assoc()"* - That is an MySQLi_ function, not PDO. — Funk Forty Niner, Jul 30 '20 at 21:16
@FunkFortyNiner The OP copy/pasted the answer provided by Riggs who assumed they the OP was using `mysqli` and not `PDO` and caused the issue with `fetch_assoc()`, I mentioned the issue in a comment on their answer which was addressed. The current syntax in the question is acceptable for `PDO`. — Will B., Jul 30 '20 at 21:20
@fyrye You're right and I've rectified the close. Thing is here, that Riggs was *kind of* right where they didn't fetch anything. Same thing applies to your answer btw. — Funk Forty Niner, Jul 30 '20 at 21:22
@FunkFortyNiner [`PDO::query()`](https://www.php.net/manual/en/pdo.query.php) returns a `PDOStatement` that is iterable, If `PDO::prepare()` was used, the OP would indeed require `PDO::execute()` and `PDOStatement::fetch()` or `PDOStatement::fetchAll()` Example: https://3v4l.org/eDgMD I assumed that the OP was using `mysqli` and not fetching records also, until they left their comment in the answer by Riggs. — Will B., Jul 30 '20 at 21:32
@DanMiller updated [my answer](https://stackoverflow.com/a/63178981/1144627) with the `time` in seconds conversion to `HH:MM:SS` in [your other question](https://stackoverflow.com/questions/63181091/php-mysql-convert-seconds-to-hours-minutes), since none of the duplicates that were mentioned answer your question specifically. — Will B., Jul 30 '20 at 22:11

Will B. · Accepted Answer · 2020-07-30T22:26:36.997

To add a rank column to your query in MySQL >= 8.0 you can use the RANK() window function.

Updated

To convert the time column seconds into HH:MM:SS as a TIME data-type, up to 838:59:59, you would compare two points in time. You can accomplish this by using TIMEDIFF to compare NOW() with the time column value added to the current time using NOW().

TIMEDIFF(NOW() + INTERVAL `time` SECOND, NOW()) AS `played`

MySQL 8.0+

To specify the ranking order from highest to lowest time column value, you would use.

RANK() OVER(ORDER BY `time` DESC) AS `rank`

DB-Fiddle

<?php

// Select Time and Rank based on highest to lowest time
$SelectTime = $db->query('SELECT 
`name`, 
`steamid`,
`time`,
TIMEDIFF(NOW() + INTERVAL `time` SECOND, NOW()) AS `played`,
RANK() OVER(ORDER BY `time` DESC) AS `rank` 
FROM `users` 
ORDER BY `rank`'); //order lowest to highest rank 1-10

// Print Output
foreach ($SelectTime as $PrintTime) {
    echo "<tr>
            <td><b>" . $PrintTime['rank'] . "</b></td>
            <td><b>" . $PrintTime['name'] . "</b></td>
            <td><a href='https://steamcommunity.com/profiles/" . $PrintTime['steamid'] . "' target='_blank'>" . $PrintTime['steamid'] . "</a></td>
            <td>" . $PrintTime['played'] . "</td>
        </tr>";
}

?>
</tbody>

Result

| name | time | played   | rank |
| ---- | ---- | -------- | ---- |
| D    | 1000 | 00:16:40 | 1    |
| B    | 600  | 00:10:00 | 2    |
| C    | 300  | 00:05:00 | 3    |
| A    | 10   | 00:00:10 | 4    |

MySQL < 8.0

Alternatively in MySQL < 8.0, you can use a user-defined variable to increment the rank.

DB-Fiddle

<?php 

$db->exec('SET @rank = 0');

// Select Time
$SelectTime = $db->query('SELECT 
`name`, 
`steamid`,
`time`,
TIMEDIFF(NOW() + INTERVAL `time` SECOND, NOW()) AS `played`,
@rank := @rank + 1 AS `rank` 
FROM `users` 
ORDER BY `time` DESC');

// Print Output
foreach ($SelectTime as $PrintTime) {
    echo "<tr>
            <td><b>" . $PrintTime['rank'] . "</b></td>
            <td><b>" . $PrintTime['name'] . "</b></td>
            <td><a href='https://steamcommunity.com/profiles/" . $PrintTime['steamid'] . "' target='_blank'>" . $PrintTime['steamid'] . "</a></td>
            <td>" . $PrintTime['played'] . "</td>
        </tr>";
}

$db->exec('SET @rank = NULL');

?>
</tbody>

Leaderboard rank based on time played MySQL PHP

1 Answers1

MySQL 8.0+

MySQL < 8.0