Honza Sedloň Honza Sedloň - 2 years ago 67
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 Source

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

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>";
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download