title: PHP SQL, How do I query for the count of comments in a post (for blog posts)?
body: <p>I'm currently creating my own blog with a custom css built from scratch with PHP. I am still very new to the language and also with SQL. Right now I have a problem with displaying a <code>Comments(3)</code> link on each post that I query from the database. I have 3 tables: users, posts, and comments. </p>
  
  <p>USERS</p>
  
  <pre><code>id | username | password | name | email | privileges
  </code></pre>
  
  <p>POSTS</p>
  
  <pre><code>postid | title | date | content | userid | visible | active 
  </code></pre>
  
  <p>COMMENTS</p>
  
  <pre><code>commentid | c_name | c_email | c_ website | c_date | c_content | approved | postid
  </code></pre>
  
  <p>This is my current query for displaying posts content from the db:</p>
  
  <pre><code> $query = connect()-&gt;prepare(
      "SELECT * FROM posts JOIN users on posts.userid = users.id WHERE visible = 1 ORDER BY postid DESC");
  $query-&gt;execute();
  
  &lt;div id="posts"&gt;
              &lt;?php
                 while($posts = $query-&gt;fetch(PDO::FETCH_ASSOC)) {
                  $id = $posts['postid'];
                  $title = $posts['title'];
                  $date = $posts['date'];
                  $content = $posts['content'];
                  $name = $posts['name'];
              ?&gt;
              &lt;div id="entry" class="post-&lt;?php echo $id; ?&gt;"&gt;            
                  &lt;h1&gt;&lt;?php echo "&lt;a href='entry.php?id=".$id."'&gt;" .$title. "&lt;/a&gt;"; ?&gt;&lt;/h1&gt;
                  &lt;div class="entry_content"&gt;
                      &lt;?php echo $content; ?&gt;
                      &lt;p&gt;&lt;span class="author"&gt;&lt;?php echo $name; ?&gt; on &lt;?php echo $date;?&gt;&lt;/span&gt;&lt;/p&gt;
                    //this is where I want to put the "Comments(3)"
                  &lt;/div&gt;
              &lt;/div&gt;
              &lt;?php } ?&gt;
          &lt;/div&gt;
  </code></pre>
  
  <p>I tried doing the below query to retrieve the number of comments by finding it by its post id INSILDE the while loop.</p>
  
  <pre><code> &lt;?php 
         $query = connect()-&gt;prepare("SELECT COUNT(postid) as commmentno FROM comments WHERE postid = :postid");
         $query-&gt;execute(array(':postid' =&gt; $id)); 
         $commentnos = $query-&gt;fetch();
  
         echo $commentnos['commentno'];
    ?&gt;
  </code></pre>
  
  <p>But the results ended up that I only got one post displayed with the right number of comments... How do I get these results in one query?</p>