CODEWITHSUNDEEP
phpmysqlrankleaderboard
Mardoch
Mardoch

Reputation: 1

How can I display rank to leaderboard from score

I'm trying to create a leaderboard using a score table from Mysql. I only use "name" and "score" from my score table. I can display my leaderboard ordered by best score but can't display the rank with it. Here is my php file to get datas:

// Connect to server and select database.
$con = mysqli_connect($host, $db_username, $db_password, $db_name);

// Retrieve data from database
$sql = "SELECT *
        FROM scores
        ORDER BY score DESC
        LIMIT 10";  // The 'LIMIT 10' part will only read 10 scores. Feel free to change this value
$result = mysqli_query($con, $sql);

// Start looping rows in mysql database.
while($rows = mysqli_fetch_array($result)){
    echo $rows['name'] . "|" . $rows['score'] . "|";
// close while loop 
}

// close MySQL connection 
mysqli_close($con);
?>

I guess I probably need to have something like this in the end:

while($rows = mysqli_fetch_array($result)){
    echo $rows['rank'] . "|" . $rows['name'] . "|" . $rows['score'] . "|";

But can't manage to get it properly…

The result should be displayed like:

<br/> Mardoch        49507
<br/> Gunthylapointe 49504

What am I doing wrong?

Upvotes: 0

Views: 480

Answers (2)

Mardoch
Mardoch

Reputation: 1

Well, I change a bit my code and added this:

// Retrieve data from database
$sql = "SELECT *
        FROM scores
        ORDER BY score DESC";
        //LIMIT 10";    // The 'LIMIT 10' part will only read 10 scores. Feel free to change this value
$result = mysqli_query($con, $sql);
    
    $i = 0;
    
// Start looping rows in mysql database.
while($rows = mysqli_fetch_array($result)){
        $i++;
        
    echo ($i) . "|" . $rows['name'] . "|" . $rows['score'] . "|";
// close while loop 
}

It's work a bit better but it stop my leaderboard at a moment and display a lot of 0 after:

screen capture

Seems the problem is because the next score is exactly the same as the previous…So it breaks here

Upvotes: 0

GMB
GMB

Reputation: 222652

Do you want window functions?

select rank() over(order by score desc) rn, s.*
from scores
order by score desc

This adds another column to the resultset, called rn, that contains the rank of each row, ordered by descending score.

Upvotes: 1

Related Questions