Iam Srkfan Iam Srkfan - 3 months ago 5
MySQL Question

use of two table in a single div

I have one table as blog_posts

+---------+-----------+
| postID | categoryID |
+========+============+
| 1 | 1 |
+--------+------------+
| 2 | 1 |
+--------+------------+
| 3 | 2 |
+--------+------------+
| 4 | 4 |
+--------+------------+


and another is blog_category

+---------+-----------+
| CategoryID | catName |
+========+============+
| 1 | cricket |
+--------+------------+
| 2 | sports |
+--------+------------+
| 3 | football |
+--------+------------+
| 4 | tennis |
+--------+------------+


now i want to display the categories and how many post are there on category as well. like categoryID 1 is there in postID 1 and 2 so, it will show cricket , posts 2. right now i am only fetching the categories only which is easy :P

<ul>
<?php
if (mysqli_connect_errno()) {
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$result = mysqli_query($conn,"SELECT * FROM blog_category ");
while($row = mysqli_fetch_array($result)) {



?>
<li>
<a class="f-categories-filter_name" href="blog-cat.php?id=<?php echo $row['categoryID'];?>"><i class="fa fa-plus"></i><?php echo $row['categoryName'];?></a>
<span class="b-categories-filter_count f-categories-filter_count">**I want to display here number of posts of that category**</span>
</li>
<?php } ?>
</ul>

Answer

To get the number of posts in each category you need to use the following SQL query:

SELECT 
   blog_category.*, 
   COUNT(blog_posts.postID) AS cnt
FROM 
   blog_category
INNER JOIN
   blog_posts
ON
   blog_posts.categoryID=blog_category.categoryID
GROUP BY
   blog_category.categoryID;

So it will be something like this:

 <ul>
 <?php
   if (mysqli_connect_errno()) {
       echo "Failed to connect to MySQL: " . mysqli_connect_error();
   }
   //here we use a modified query
   $result = mysqli_query($conn, "SELECT 
   blog_category.*, 
   COUNT(blog_posts.postID) AS cnt
FROM 
   blog_category
INNER JOIN
   blog_posts
ON
   blog_posts.categoryID=blog_category.categoryID
GROUP BY
   blog_category.categoryID;");
       while($row = mysqli_fetch_array($result)) {
    ?>
     <li>
            <a class="f-categories-filter_name" href="blog-cat.php?id=<?php echo $row['categoryID'];?>"><i class="fa fa-plus"></i><?php echo $row['categoryName'];?></a>
<span class="b-categories-filter_count f-categories-filter_count"><?php echo $row['cnt'];?></span>
        </li>
        <?php } ?>
    </ul>