Ordering by number of rows?

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.

I would appreciate any help, thank you.

Answer 1

But the result of the query into an array indexed by $boots.

AKA:

while(..){
    $boot = mysql_num_rows($query);
    $results[$boot][] = $result_array;
}
ksort($results);
foreach($results as $array)
{
    foreach($array as ....)
    {
          // display table
    }

}

You can switch between ksort and krsort to switch the orders, but basically you are making an array that is keyed by the number in $boot, sorting that array by that number, and then traversing each group of records that have a specific $boot value.

Answer 2

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.