CODEWITHSUNDEEP
phpmysqldatabase
Massa
Massa

Reputation: 31

Need help displaying only one result per user from database (PHP)

i am making a top 10 list of the best times from my bhop server in csgo. All the times get stored in the database, but the problem is that each time the user gets a new time it adds a new row. With the code i have now it displays the top ten list of all the times, i want it to only display one result per user(the best one)

My current code:

<div id="content">
        <table cellspacing="0">
        <h1 align="center">bhop_eazy_csgo</h1>
            <tr><th>Place</th><th>Name</th><th>Time</th></tr>
            <?php
            $i = 1;
                    $con=mysqli_connect("localhost","root","*******","timer");
                    // Check connection
                    if (mysqli_connect_errno()) {
                      echo "Failed to connect to MySQL: " . mysqli_connect_error();
                    }

                    $result = mysqli_query($con,"SELECT * FROM round WHERE map = 'bhop_eazy_csgo' ORDER BY time LIMIT 0, 10");
                    while($row = mysqli_fetch_array($result)) {

                          echo "<tr><td>" . ($i) . "</td><td>" . $row['name'] . "</td> <td>" . gmdate("i:s", $row['time']) . "</td></tr>";
                          $i++;
                    }

                mysqli_close($con);
            ?>
        </table>
</div>

How it looks:
http://gyazo.com/3653ec8a87fbf68c4137b67b75e20f8d

Edit
Now it looks like this:

1 Wildmine 00:49
2 Wildmine 00:50
3 Wildmine 01:02
4 ?J@rr3? 01:12

I want it to look like this:

1 Wildmine 00:49
2 ?J@rr3? 01:12
Thanks to Michael Wagner for helping me getting it working:)

$result = mysqli_query($con,"
                    SELECT DISTINCT name n, (
                        SELECT time
                        FROM round
                        WHERE map = 'bhop_eazy_csgo'
                        AND name = n
                        ORDER BY time
                        LIMIT 1
                    ) AS time
                    FROM round
                    WHERE map = 'bhop_eazy_csgo'
                    ORDER BY time
                    LIMIT 0 , 10
                    ");
                    while($row = mysqli_fetch_array($result)) {

                    echo "<tr><td>" . ($i) . "</td><td>" . $row['n'] . "</td> <td>" . gmdate("i:s", $row['time']) . "</td></tr>";
                    $i++;

}

Upvotes: 1

Views: 118

Answers (1)

Michael Wagner
Michael Wagner

Reputation: 1038

Try this: 

SELECT DISTINCT name n, (
    SELECT time
    FROM round
    WHERE map = 'bhop_eazy_csgo'
    AND name = n
    ORDER BY time
    LIMIT 1
) AS time
FROM round
WHERE map = 'bhop_eazy_csgo'
ORDER BY TIME
LIMIT 0 , 10

Upvotes: 3

Related Questions