CODEWITHSUNDEEP
phpmysqldatabase
user3133586
user3133586

Reputation: 199

mySQL Order by Most Commented and Least Commented

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

Upvotes: 0

Views: 194

Answers (2)

Barmar
Barmar

Reputation: 781716

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.

Upvotes: 3

roun512
roun512

Reputation: 149

First of all you have error here

echo "divs in order from least to greatest "number = $num_rows"";

It should be

echo "divs in order from least to greatest number = " . $num_rows . "";

And about the most commented try with

$sql = "SELECT * FROM `table` WHERE `id` = '$id' ORDER BY column DESC/ASC";

Or if there is not count column try with 

$sql = "SELECT * FROM `table` WHERE `id` = '$id' ORDER BY COUNT(column) DESC/ASC";

Upvotes: 0

Related Questions