最受评论和最少评论的mySQL排序

Question

I'm trying to order a list of items based on the amount of comments for each topic as shown below:

$page = $_GET['page'];

$query = mysql_query("SELECT * FROM topic WHERE cat_id='$page' LIMIT $start, $per_page");

if (mysql_num_rows($query)>=1)
{
while($rows = mysql_fetch_array($query))
{
$number = $rows['topic_id'];
$title = $rows['topic_title'];
$description = $rows['topic_description'];

//get topic total
$sqlcomment = mysql_query("SELECT * FROM comments WHERE topic_id='$number'");
$commentnumber = mysql_num_rows($sqlcomment);
// TRYING TO ORDER OUTPUT ECHO BY TOPIC TOTAL ASC OR DESC
echo "
<ul>
<li><h4>$number. $title</h4>
<p>$description</p>
<p>$topictime</p>
<p>$commentnumber</p>
</li>
</ul>
";
}
}
else
{
echo "<p>no records available.</p><br>";
}

What would be the best way to order each echo by $num_rows (ASC/DESC values)? NOTE: I've updated with the full code - I am trying to order the output by $commentnumber

Answer 1

The first query should be:

SELECT t.*, COUNT(c.topic_id) AS count
FROM topic AS t
LEFT JOIN comments AS c ON c.topic_id = t.topic_id
WHERE t.cat_id = '$page'
GROUP BY t.topic_id
ORDER BY count
LIMIT $start, $per_page

You can get $commentnumber with:

$commentnumber = $rows['count'];

You don't need the second query at all.