automatic ranking query

[JeroenNick]

Hi there,

 

I am making a darts competition table in mysql,php.

Currently i made i static html table to show the ranking of the results i get from my database.

This table is named "seizoentabel2".

 

I would like to remove this table and have the query to generate an automatic ranking.

Does anyone have experience with this and would like to help me?

 

This is my current code:

 

<?php
$servername = "localhost";
$username = "****";
$password = "****";
$dbname = "****";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
} 

$sql = "SELECT Speler, Gespeeld, honderdtachtig, Punten FROM seizoen16 ORDER BY punten DESC";
$result = $conn->query($sql);

echo "<table class='seizoentabel2' align='left'>
        <th>Pos.</th>
        <tr><td>1</td></tr>
        <tr><td>2</td></tr>
        <tr><td>3</td></tr>
        <tr><td>4</td></tr>
        <tr><td>5</td></tr>
        <tr><td>6</td></tr>
        <tr><td>7</td></tr>
        <tr><td>8</td></tr>
        <tr><td>9</td></tr>
        <tr><td>10</td></tr>
        <tr><td>11</td></tr>
        <tr><td>12</td></tr>
        <tr><td>13</td></tr>
        <tr><td>14</td></tr>
        <tr><td>15</td></tr>
        <tr><td>16</td></tr>
        <tr><td>17</td></tr>
        <tr><td>18</td></tr>
        <tr><td>19</td></tr>
        <tr><td>20</td></tr>
        </table>";

echo "<table class='seizoentabel'><th>Speler</th><th>Gespeeld</th><th>180</th><th>Punten</th>";

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo
        "<tr><td>" . $row["Speler"]."</td>".
        " " . "<td class='center'>" . $row["Gespeeld"]."</td>".
        "<td class='center'>" . $row["honderdtachtig"]."</td>".
        "<td class='center'>" . $row["Punten"]."</td></tr>";
    }
     echo "</table><br class='clear:both'>";
} else {
    echo "0 results";
}
$conn->close();
?>

 

If there is any unclarity don't hesitate to ask questions. Thanks in advance!

[Barand]

Test data

mysql> select * from seizoen16;
+--------------+--------+----------+----------------+--------+
| seizoen16_id | speler | gespeeld | honderdtachtig | punten |
+--------------+--------+----------+----------------+--------+
|            1 | Fred   |       20 |              8 |     40 |
|            2 | Peter  |       20 |             10 |     56 |
|            3 | Paul   |       22 |             15 |     63 |
|            4 | Mary   |       21 |              9 |     56 |
+--------------+--------+----------+----------------+--------+

SELECT cast(rank as char) as rank
      , speler
      , gespeeld
      , honderdtachtig
      , punten 
FROM (
    SELECT speler
      , gespeeld
      , honderdtachtig
      , @row :=@row+1 as row
      , @rank := IF(punten = @lastpunten, @rank, @row) as rank
      , @lastpunten := punten as punten
      FROM seizoen16
        JOIN (SELECT @row:=0, @rank:=0, @lastpunten:=0) init
      ORDER BY punten DESC 
    ) points

+------+--------+----------+----------------+--------+
| rank | speler | gespeeld | honderdtachtig | punten |
+------+--------+----------+----------------+--------+
| 1    | Paul   |       22 |             15 |     63 |
| 2    | Peter  |       20 |             10 |     56 |
| 2    | Mary   |       21 |              9 |     56 |
| 4    | Fred   |       20 |              8 |     40 |
+------+--------+----------+----------------+--------+