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>
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>
Yuo can obtain the data direcly with a query
the full list
select blog_category.catName group_concat(blog_posts.postID)
from blog_posts
inner join blog_category on blog_category.CategoryID = blog_posts.CategoryID
group by blog_category.catName ;
or a singole category
select blog_category.catName group_concat(blog_posts.postID)
from blog_posts
inner join blog_category on blog_category.CategoryID = blog_posts.CategoryID
where blog_category.CategoryID = 1
group by blog_category.catName ;
You have to change your SQL query to fetch data. There are many way to join it. I've write one.
SELECT blog_category.* , count(*) as total_post
FROM blog_category INNER JOIN blog_posts
ON blog_category.CategoryID = blog_posts.categoryID
GROUP BY blog_category.Category
<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"><?php echo $row['total_post']; ?></span>
</li><?php
}
?></ul>
SQL join : http://www.w3schools.com/sql/sql_join_inner.asp
SQL Count : http://www.w3schools.com/sql/sql_func_count.asp