平均PHP的不同和有序结果

Question

I'm trying to get the averages from the rating column of my table, then display a row in a HTML table for each distinct div_id and its average rating, in descending order. I know this should probably be easy, but I'm having a hard time figuring it out. Any help would be greatly appreciated.

<?php
mysql_connect($db_server, $db_username, $db_password) or die(mysql_error());
mysql_select_db($db_database) or die(mysql_error());

$result = mysql_query("SELECT * FROM ratings") or die(mysql_error());  
while($row = mysql_fetch_object( $result )){

$ad = $row->div_id;

$result2 = mysql_query("SELECT div_id, avg(rating) AS avg, COUNT(*) FROM ratings WHERE div_id = '" . $ad . "' ORDER BY div_id DESC ") or die(mysql_error());  
while($row2 = mysql_fetch_array( $result2 )){

$adid = $row2[0];
$count = $row2[2];
$avg = round($row2[1],2);



echo "<tr><td>";
echo $adid;
echo "</td><td>";
echo $avg;
echo "</td></tr>";

}
}
?>

Answer 1

Instead do tow queries, you can use the GROUP BY term in only one query.

SELECT div_id, avg(rating) AS avg, COUNT(*) FROM ratings GROUP BY div_id ORDER BY div_id DESC

php code

<?php

mysql_connect($db_server, $db_username, $db_password) or die(mysql_error());
mysql_select_db($db_database) or die(mysql_error());

$result = mysql_query("SELECT div_id, avg(rating) AS avg, COUNT(*) FROM ratings GROUP BY div_id ORDER BY avg DESC") or die(mysql_error());

while($row = mysql_fetch_array( $result )){

    $adid = $row[0];
    $count = $row[2];
    $avg = round($row[1],2);

    echo "<tr><td>" . $adid . "</td><td>" . $avg . "</td></tr>";
}
?>