Adding a rank to my mySQL output data

Question

The code below is returning a table with data from mysql, but what I'm missing in the output is a generated column with a rank based on a column's data (wilks in this example). I've looked for examples and answers to similar questions, but I can't get it to work.

In short: I need an extra column next to the ID column where a rank beginning from 1 is displayed, based on the data from the Wilks column.

Thank you so much!

<?php
$con=mysqli_connect("localhost", "user", "password", "dbname");
// Check connection
if (mysqli_connect_errno())
{
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM `Mannen` ORDER BY `Wilks` DESC");

echo "<table border='1'>
<tr>
<th>#</th>
<th>Naam</th>
<th>Lichaamsgewicht</th>
<th>Vereniging</th>
<th>Squat</th>
<th>Bench</th>
<th>Deadlift</th>
<th>Totaal</th>
<th>Wilks</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
    echo "<tr>";
    echo "<td>" . $row['ID'] . "</td>";
    echo "<td>" . $row['Naam'] . "</td>";
    echo "<td>" . $row['Lichaamsgewicht'] . "</td>";
    echo "<td>" . $row['Vereniging'] . "</td>";
    echo "<td>" . $row['Squat'] . "</td>";
    echo "<td>" . $row['Bench'] . "</td>";
    echo "<td>" . $row['Deadlift'] . "</td>";
    echo "<td>" . $row['Totaal'] . "</td>";
    echo "<td>" . $row['Wilks'] . "</td>";
    echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

It is not really clear what you are asking here. Do you mean you want a new column added to your table i.e. `rank` or do you want to base the ranking on the `wilks` columns as it currently exists — RiggsFolly, Sep 24 '16 at 22:44
Edit your question and provide sample data and desired results. — Gordon Linoff, Sep 24 '16 at 22:48
@RiggsFolly Yes, you are right. My apologies for being unclear. I have 10 random athletes in the database and I'm outputting all the data in table on a web page, ordered by their Wilks score. What I need is an extra column that is being generated in that table adding a rank based on their place in the table. Athlete with ID 5 has a score of 400, and athlete with ID 1 has a score of 300. The athlete with 400 is displayed first based on his score and needs to get Rank 1 (which is displayed in a column). — Mirza, Sep 25 '16 at 12:05
So ordering by Wilks is what you want. Right? So just add a counter to your while loop and use that as the `rank`. If you physically store it on the database you will be forever having to amend it, and it is likely to be wrong most of the time — RiggsFolly, Sep 25 '16 at 12:08
@RiggsFolly That's correct. I want it this way, because if a new athlete with ID 132 participated and he has a score of 500, I just add his details in the database and the php script updates the table on the webpage, adding him on place 1. — Mirza, Sep 25 '16 at 12:27
@RiggsFolly It works! I couldn't imagine it being so simple! Thanks. — Mirza, Sep 25 '16 at 12:59

score 0 · Answer 1 · answered Sep 24 '16 at 22:50

First, if you just want an enumerated value, you can get that using PHP.

But, it is easy enough in MySQL, using variables:

SELECT m.*, (@rn := @rn + 1) as rank
FROM Mannen m CROSS JOIN
     (SELECT @rn := 0) params
ORDER BY Lichaamsgewicht DESC;

Technically, this implements the related function of row_number() rather than rank(). Your question is unclear on what you mean by "rank".

Adding a rank to my mySQL output data

1 Answers1