Most efficient way to sort a leaderboard in SQL and update it in a table using PHP

Question

I might not have worded this right, but basically here is what I want to do:

• Display the leaderboard in a HTML table
• Each heading in the leaderboard is a link (e.g. score, time, kills)
• When each link is click it sorts the table depending what link is click (e.g. if kills is clicked it sorts the data showing the move kills at the top)

Here is a link to my current leaderboard which works fine.

This is the code I have to far:

<div id="board">
    <table border="1" cellspacing="0" cellpadding="2" width="620"><tbody>
        <thead>
            <tr>
                <td>Name</td>
                <td>Score</td>             <---- WHEN CLICK = SORT IT BY SCORE
                <td>Wave Reached</td>      <---- WHEN CLICK = SORT IT BY WAVE
                <td>Seconds Survived</td>  <---- WHEN CLICK = SORT IT BY SECONDS
                <td>Kills</td>             <---- WHEN CLICK = SORT IT BY KILLS
                <td>Deaths</td>            <---- WHEN CLICK = SORT IT BY DEATHS
            </tr>
        </thead>
        <tbody>
            <?php

                $connect = mysql_connect("localhost","localhost", "password");
                if (!$connect) {
                    die(mysql_error());
                }
                mysql_select_db("staroids");
                $results = mysql_query("SELECT name, score, wave, seconds, kills, deaths FROM scores ORDER BY score DESC LIMIT 10");
                while($row = mysql_fetch_array($results)) {
                $name = $row['name'];
                $score = $row['score'];
                $wave = $row['wave'];
                $seconds = $row['seconds'];
                $kills = $row['kills'];
                $deaths = $row['deaths'];
            ?>
                <tr>
                    <td><?php echo $name;?></td>
                    <td><?php echo $score;?></td>
                    <td><?php echo $wave;?></td>
                    <td><?php echo $seconds;?></td>
                    <td><?php echo $kills;?></td>
                    <td><?php echo $deaths;?></td>
                </tr>
            <?php
                }
                mysql_close($connect);
            ?>
        </tbody>
    </table>
</div>

I hope this explains what I intend to do.

The code above displays the scores in descending order. Is there away to run a PHP script when a link is pressed in HTML like with JavaScript so it runs a specific query?

Answer 1

In your table header, you could link to the current page with a different parameter per link:

<tr>
    <td><a href="currentpage.php?sort=name">Name</a></td>
    <td><a href="currentpage.php?sort=score">Score</a></td>           
    ...       
</tr>

then in php, you set the column you wanna sort depending of the value of the $_GET["sort"] parameter.
The value of $sort being the actual database column title.

if (isset($_GET["sort"])) {
    if ($_GET["sort"] == "name")
        $sort = "name";
    else if ($_GET["sort"] == "score")
        $sort = "score";
    else
        $sort = "score"; //default value
}
else
    $sort = "score"; //default value

$results = mysql_query("SELECT name, score, wave, seconds, kills, deaths FROM scores ORDER BY ".$sort." DESC LIMIT 10");

It's not the cleanest code, but it show the idea.
Good luck with this.