php - SQL query on how to Count the number of comments each post have -

i have 2 database table called "comments" , "posts"

in "posts" table got post_id, post_title

in "comments" table got comment_id, post_id, message

the post_id in comments table stores id of post being commented. way can count how many comments post have.

i tried doing research , end code below:

$displaypost = "select * 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) comments 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"; } 

the code above results to:

unable fetch mysqli_fetch_row()

you need 1 query, replace "select * posts" by

    select post_title,count(posts.post_id) total posts join comments posts.post_id = comments.post_id group posts.post_id 

then have

      $posttitle = $row['post_title'];       $total_comment =$row['total'];        echo "post title: $posttitle";       echo "post comment: $total_comment"; 

final code

 $displaypost = "select post_title,count(posts.post_id) total posts join comments posts.post_id = comments.post_id group 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"; }