Order by --; but also ordering by another?

Question

K, I have one issue about PHP. I'm trying to make a highscore for my game that has levels and XP.

My issue is pretty simple, I want to order it by total level, so that the people with the highest total level come up as first, second, third, etc. But I also want it to be ordered by xp, so that if they have the same total level, than the person with the highest xp will be shown first, instead of it being just random.

This is what I have so far for ordering by level, but I don't know how to make it check for the xp.

$count = mysql_result(mysql_query("SELECT COUNT(*) FROM skillsoverall"),0) or     die(mysql_error());

        $from = (isset($_GET["from"]) && is_numeric($_GET["from"]) && $_GET["from"] < $count) ? $_GET["from"] : 0;

        $query = mysql_query ("SELECT * FROM skillsoverall ORDER BY lvl DESC limit $from, $ppls_page") or die(mysql_error());

        $i = $from;
        while($row = mysql_fetch_array($query)){
        $i++;
        if($i < $top_hiscore) {
if($i & 1) {
        echo '<tr class="row row1">
<td class="rankCol"><span>'.$i.'</span></td>
<td class="nameCol"><span><a href="'.$website.'/'.$pers.'?name='.$row["playerName"].'"     target="_self">'.BBCode($row["playerName"]).'</a></span></td>
<td class="lvlCol">'.dots($row["lvl"]).'</td>
<td class="xpCol">'.dots($row["xp"]).'</td>
</tr>   
';
} else {
        echo '<tr class="row row2">
<td class="rankCol"><span>'.$i.'</span></td>
<td class="nameCol"><span><a href="'.$website.'/'.$pers.'?name='.$row["playerName"].'"     target="_self">'.BBCode($row["playerName"]).'</a></span></td>
<td class="lvlCol">'.dots($row["lvl"]).'</td>
<td class="xpCol">'.dots($row["xp"]).'</td>
</tr>   
';

score 0 · Answer 1 · answered Sep 21 '12 at 08:32

0

Just do it in your query right off the bat:

SELECT * FROM skillsoverall ORDER BY lvl DESC, xp desc

SQL will allow multiple columns in the order by clause. In this case, it will now return data sorted by highest to lowest level and where there is more than one row with the same level, it sorts the data from highest to lowest XP. It will also work consistently using the limit clause.

answered Sep 21 '12 at 08:32

Fluffeh

33,228
16
67
80

Alright, thank you very much. It's working perfectly now. I was not aware that it allowed multiple columns. – hellman2741 Sep 21 '12 at 08:51
@hellman2741 Glad to help. You might also be interested in this rather length - but very detailed and filled with queries, outputs and explanations - [Question and Answer](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) I posted to help with SQL queries - it focuses mainly on getting data from multiple tables, but you could well benefit from reading it to get a better idea of some of the things you can do in SQL mate :) – Fluffeh Sep 21 '12 at 08:53

Order by --; but also ordering by another?

1 Answers1