title: How to display all posts under main category and its subcategories?
body: <p>I have a custom php/mysql blog. In single post page, I have a sidebar where I am displaying categories. I want to display number of posts those belong to a main category and its subcategories in brackets as we usually see in WordPress. For now I have</p>
  
  <pre><code>Music
  Pop
  Nature
  Lakes
  Sea
  </code></pre>
  
  <p>I want to achieve</p>
  
  <pre><code>Music (5)
  Pop (3)
  Nature (8)
  Lakes (2)
  Sea (4)
  </code></pre>
  
  <p>As you see Music category has 5 posts under it and Pop has 3. But it doesn't mean there are 8 posts in reality. There are actually only 5 posts because Pop is subcategory of main category Music. 2 posts are directly under the main category Music while 3 posts are falling in its subcategory Pop. Thus 2 + 3 = 5. </p>
  
  <p>The categories table looks as (Note that it has nth level of subcategories):</p>
  
  <p><a href="https://i.stack.imgur.com/ikx6u.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ikx6u.png" alt="enter image description here"></a></p>
  
  <p>The posts table looks as (Note that a particular post can have its only one category parent i.e. a post can not have its multiple parents)</p>
  
  <p><a href="https://i.stack.imgur.com/HI2j5.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/HI2j5.png" alt="enter image description here"></a></p>
  
  <p>My code to fetch categories and their subcategories is</p>
  
  <pre><code>public function get_category_hierarchy(&amp;$output, $parent_url, $parent = 0)
  {
      $sql = "SELECT * FROM `tb_categories` WHERE `category_parent` = " . $parent . " ORDER BY `category_name`";
      $rows = $this-&gt;get_by_sql($sql);                    
  
      // Removing trailing slash
      $parent_url = rtrim($parent_url, "/");
  
      if(is_array($rows))
      {
          foreach($rows as $row) :
              $url = $parent_url . "/" . $row-&gt;category_url;
              $output.= "&lt;li&gt;&lt;a href='" . $url . "'&gt;" . $row-&gt;category_name . "&lt;/a&gt;&lt;/li&gt;";
              if($row-&gt;category_id != $parent)
              {
                  $this-&gt;get_category_hierarchy($output, $url, $row-&gt;category_id);
              }
          endforeach;
      }
      return $output;     
  }
  </code></pre>
  
  <p>On sidebar, I am displaying these categories by following code:</p>
  
  <pre><code>$output = '';
  // Create post object
  $post_obj = new Post();
  echo $post_obj-&gt;get_category_hierarchy($output, SITE_URL . 'category');
  </code></pre>
  
  <p>Now I'm wondering what code need to be add for fetching number of posts (post counts) that fall under a main category and subsequently its sub-sub-categories? Though I tried to fetch them but the result didn't come accurate.
  Thank you in advance.</p>
  
  <p><strong>EDIT:</strong></p>
  
  <p>I modified function get_category_hierarchy() by doing following amendment:</p>
  
  <pre><code>foreach($rows as $row) :
      $url = $parent_url . "/" . $row-&gt;category_url;
  
      // Build database query
      $sql = "SELECT COUNT(*) AS 'total_posts' FROM `tb_posts` WHERE `post_parent` = " . $row-&gt;category_id . " GROUP BY `post_parent`";
  
      // Execute database query
      $rows = $this-&gt;get_by_sql($sql);
  
      $output[] = "&lt;li&gt;&lt;a href='" . $url . "'&gt;" . $row-&gt;category_name . " (" . $rows[0]-&gt;total_posts . ")&lt;/a&gt;&lt;/li&gt;";
  </code></pre>
  
  <p>But oops! It is giving me following result:</p>
  
  <pre><code>Music (2)
  Pop (3)
  Nature (2)
  Lakes (2)
  Sea (4)
  </code></pre>
  
  <p>Obviously, the parent category is having total number of post which are directly associated to it and not to its children. How to fix it?</p>