我已经连接了两个表。查询如下所示:
SELECT *, AVG(rate)
FROM comments c
LEFT JOIN supps s
on c.tutorialid = s.tutid
WHERE category = 'Protein'
GROUP BY tutorialid
ORDER BY $orderby $sort LIMIT $startrow,$limit";
我有下面的代码来获得总注释:
//find the number of comments
$commentNum = mysql_num_rows($result);
当我使用echo $commentNum
时,它显示每行上所有注释的总数,例如,如果我有:
echo "".$row['title'] ."<br>";
echo $commentNum .
它会给我所有帖子的评论总数,而不是每个帖子的评论总数。谁能告诉我哪里做错了?我怎样才能得到每个帖子的总评论?
我会尝试在初始SELECT
中添加COUNT(*)
。所以你的查询应该是
SELECT *, COUNT(*), AVG(rate)
FROM comments c
LEFT JOIN supps s
ON c.tutorialid = s.tutid
WHERE category = 'Protein'
GROUP BY tutorialid
ORDER BY $orderby $sort LIMIT $startrow,$limit";