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[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.

Answers


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.


Need Your Help

Check for a list of strings (words) in a text (phrase)

r

Is there an elegant way, other than looping, to test if a word which belong to a list is found in a phrase?

.NET 3.5 SP1 and aspnet_client Crystal Reports

.net asp.net .net-3.5 crystal-reports

I recently (a few days ago) installed .NET 3.5 SP1 and subsequently an aspnet_client folder with a bunch of Crystal Reports support code has been injected into my .net web apps.

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.