Order MySQL results by number of related rows in foreign table

Question

Alright, so I have a table outputting data from a MySQL table in a while loop. Well one of the columns it outputs isn't stored statically in the table, instead it's the sum of how many times it appears in a different MySQL table.

Sorry I'm not sure this is easy to understand. Here's my code:

$query="SELECT * FROM list WHERE added='$addedby' ORDER BY time DESC";
$result=mysql_query($query);
while($row=mysql_fetch_array($result, MYSQL_ASSOC)){
    $loghwid = $row['hwid'];
    $sql="SELECT * FROM logs WHERE hwid='$loghwid' AND time < now() + interval 1 hour";
    $query = mysql_query($sql) OR DIE(mysql_error());
    $boots = mysql_num_rows($query);

    //Display the table
}

The above is the code displaying the table.

As you can see it's grabbing data from two different MySQL tables. However I want to be able to ORDER BY $boots DESC. But as its a counting of a completely different table, I have no idea of how to go about doing that.

Answer 1

There is a JOIN operation that is intended to... well... join two different table together.

SELECT list.hwid, COUNT(log.hwid) AS boots 
FROM list WHERE added='$addedby'
LEFT JOIN log ON list.hwid=log.hwid
GROUP BY list.hwid
ORDER BY boots

I'm not sure if ORDER BY boots in the last line will work like this in MySQL. If it doesn't, just put all but the last line in a subquery.