php - 关于如何计算每个帖子的评论数的 SQL 查询

Question

我有两个名为“comments”和“posts”的数据库表

在“posts”表中我得到了post_id, post_title

在“comments”表中我得到了comment_id、post_id、message

comments 表中的 post_id 存储被评论的帖子的 ID。这样我就可以计算一个帖子有多少评论。

我尝试进行研究并最终得到以下代码:

$displaypost = "SELECT * FROM posts";
$result = $conn->query($displaypost);

if ($result->num_rows > 0) {

    while($row = $result->fetch_assoc()) {
        $postid = $row['post_id'];
        $posttitle =$row['post_title'];

        $countdata = "SELECT COUNT(post_id) FROM comments WHERE post_id='$postid'";
        $countresult = $conn->query($countdata);
        $countrow = mysqli_fetch_row($countresult); 
        $total_comment = $countrow[0];
        echo "Post Title: $posttitle";
        echo "Post Comment: $total_comment";
    }
} else {
    echo "0 results";
}

上面的代码结果为:

Unable to fetch mysqli_fetch_row()

Answer 1

您只需要一个查询，将“SELECT * FROM posts”替换为

    SELECT post_title,count(posts.post_id) as Total FROM posts JOIN comments WHERE posts.post_id = comments.post_id GROUP BY posts.post_id

然后你会得到

      $posttitle = $row['post_title'];
      $total_comment =$row['Total'];

      echo "Post Title: $posttitle";
      echo "Post Comment: $total_comment";

最终代码

 $displaypost = "SELECT post_title,count(posts.post_id) as Total FROM posts JOIN comments WHERE posts.post_id = comments.post_id GROUP BY posts.post_id";
 $result = $conn->query($displaypost);

if ($result->num_rows > 0) {

while($row = $result->fetch_assoc()) {
      $posttitle = $row['post_title'];
      $total_comment =$row['Total'];

      echo "Post Title: $posttitle";
      echo "Post Comment: $total_comment";
}
} else {
echo "0 results";
}