Output amount of comments of post
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; ?> 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.
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['id'] does, $posts['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.