Honza Sedloň Honza Sedloň - 7 months ago 10
SQL Question

MySQL - Order by honor and write it in "for" together with username from different table

I have two tables. Player and Stats. In player is username and id. In stats is honor and id. IDs are same in both tables. One player, one id. I would like to order stats by honor and echo it together with username to the table.

Here is my try, but i can't do anything with order.
Player counter is count of player. +1 reason is that it starts from 2

$getPlayerCounter = mysql_query("SELECT `id` FROM `player`");
$playerCounter = mysql_num_rows($getPlayerCounter);
for ($i = 2; $i <= $playerCounter + 1; $i++) {
$username = mysql_query("SELECT `player`.*, `stats`.* FROM `player` INNER JOIN `stats` ON `player`.`id`=$i AND `stats`.`id`=$i") or die(mysql_error());;
$fetch = mysql_fetch_assoc($username);
echo "<tr>";
echo "<td>".$fetch['username']."</td>";
echo "<td>".$fetch['honor']."</td>";
echo "</tr>";
}

Answer

Without using your loop. How about a solution where you query a sorted list already? Kind of like:

SELECT * FROM player p
  JOIN stats s on p.id = s.id
ORDER BY s.honor DESC

Where honor is the name of your column for the stats value (hopefully it's a column you can sort like a number).

You will get an array of rows that is ordered by the stats value in descending order (maximum value on top). Now you can just fetch row by row in the order it is in the fetched array.

You walk through the array doing this:

$result = mysql_query(the_query_above);
while ($row = mysql_fetch_assoc($result)) {
    echo "<tr>";
    echo "<td>".$row['username']."</td>";
    echo "<td>".$row['honor']."</td>";
    echo "</tr>";
}