php - 在单个 div 中使用两个表

Question

我有一张表作为 blog_posts

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

另一个是 blog_category

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

现在我想显示类别以及类别上有多少帖子。就像 postID 1 和 2 中存在类别 ID 1 一样，它将显示板球、帖子 2。现在我只获取类别，这很简单: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 1

您必须更改 SQL 查询才能获取数据。有很多方法可以加入它。我已经写了一篇。

SQL

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