邮寄评论的输出量

Question

I am trying to display the amount of comments of a post on the index page, where the title of post is displayed.

I am able to display the title, body, posted by, and date created on index except the amount of comments that it has. I am having a hard time selecting it from the database even though I have the queries setup right.

<?php if(empty($posts)): ?>
     <p>There are currently no posts.</p>
   <?php else: ?>

   <?php foreach($posts as $post): ?>

   <div class="post">
    <div class="title"><a href="<?php echo BASE_URL; ?>/post.php?post=<?php echo $post['id']; ?>"><?php echo $post['name']; ?></a></div>
    <div class="short-body"><?php echo $post['body']; ?> <a href="#">Read more</a></div>
    <div class="posted-by">Posted by <?php echo $post['user']; ?></div> <div class="date">On <?php echo $post['created']; ?> 

    | <?php foreach ($comments as $comment): echo $comment[0]; ?>   comments <?php endforeach; ?> </div>
   </div>

   <?php endforeach; ?>

    <?php endif; ?>

Everything is displayed on the page through the code above, except the amount of comments.

Here is the comments code and query behind it:

    $posts = $db->query("SELECT name,body,id,created,user FROM posts ORDER by id DESC")->fetchAll(PDO::FETCH_ASSOC);

    $id = $posts['id'];
    $comments = $db->prepare("SELECT COUNT(*) FROM comments where                                 $post_id=:post_id");
    $comments->execute(['post_id' => $id]);
    $comments = $comments->fetch(PDO::FETCH_NUM);

I am certain that my problem is that $id is not being properly used. Although I don't know what to put. post_id is assigned to the id of the post, where comments are stored in the database. I've tried a lot of things and still doesn't work.

Answer 1

I think you can retrieve posts information and the number of comments with a single query, which should avoid your problem and be more efficient; for example :

SELECT
  posts.name,
  posts.body,
  posts.id,
  posts.created,
  posts.user,
  count(comments.id) AS comments_count

FROM posts
   LEFT JOIN comments ON comments.post_id = posts.id

GROUP BY posts.id DESC

With your original queries, I think the problem is that $posts contains multiple lines, so $posts['id'] does not exists, but $posts[0]['id'] does, $posts[1]['id'] too, etc... If you want to keep your original queries you should iterate through $posts to associate the number of comments with each post in the result.