图书分类管理系统中的图书数量

Question

我正在建立一个图书馆管理系统使用PHP，Mysql。每本书都有一个类别。用户将能够看到每个类别中有多少本书。但是我不能计算出每个类别有多少本书。我想知道每一类有多少本书。

 <div class="row">
            <div class="col-md-12">
                <!-- Advanced Tables -->
                <div class="panel panel-default">
                    <div class="panel-heading">
                       Categories Listing
                    </div>
                    <div class="panel-body">
                        <div class="table-responsive">
                            <table class="table table-striped table-bordered table-hover" id="dataTables-example">
                                <thead>
                                    <tr>
                                        <th>#</th>
                                        <th>Category</th>
                                        <th>Book Amount</th>
                                        <th>Status</th>
                                        <th>Creation Date</th>
                                        <th>Updation Date</th>
                                        <th>Action</th>
                                    </tr>
                                </thead>
                                <tbody>
       <?php 

       $sql = "SELECT tblcategory.CategoryName, tblcategory.Status, tblcategory.CreationDate,         tblcategory.UpdationDate from tblcategory";
     $query = $dbh -> prepare($sql);
     $query->execute();
     $results=$query->fetchAll(PDO::FETCH_OBJ);
    $cnt=1;
   if($query->rowCount() > 0)
  {
  foreach($results as $result)
    {               ?>                                      
                                    <tr class="odd gradeX">
                                        <td class="center"><?php echo htmlentities($cnt);?></td>
                                        <td class="center"><?php echo  htmlentities($result->CategoryName);?></td>
                                        
                                        <td class="center">#</td>
                                        
                                        <td class="center"><?php if($result->Status==1) {?>
                                        <a href="#" class="btn btn-success btn-xs">Active</a>
                                        <?php } else {?>
                                        <a href="#" class="btn btn-danger btn-xs">Inactive</a>
                                        <?php } ?></td>
                                        <td class="center"><?php echo htmlentities($result->CreationDate);?></td>
                                        <td class="center"><?php echo htmlentities($result->UpdationDate);?></td>
                                        <td class="center">

                                        <a href="edit-category.php?catid=<?php echo htmlentities($result->id);?>"><button class="btn btn-primary"><i class="fa fa-edit "></i> Edit</button> 
                                      <a href="manage-categories.php?del=<?php echo htmlentities($result->id);?>" onclick="return confirm('Are you sure you want to delete?');"" >  <button class="btn btn-danger"><i class="fa fa-pencil"></i> Delete</button>
                                        </td>
                                     </tr>
                  <?php $cnt=$cnt+1;}} ?>                                      
                                 </tbody>
                             </table>
                         </div>
                        
                    </div>
                </div>
                <!--End Advanced Tables -->
            </div>
        </div>

我的数据库：

​

输出应为：

​

Answer 1

使用子查询计算分类图书数量。然后对主表使用左连接。使用COALESCE()将空值替换为0。

SELECT c.CategoryName
     , COALESCE(b.book_amount, 0) book_amount
     , CASE WHEN c.Status = 1 THEN 'Active' ELSE 'Inactive' END Status
     , c.CreationDate
     , c.UpdationDate
FROM category c
LEFT JOIN (SELECT catid
                , COUNT(id) book_amount
           FROM book
           GROUP BY catid) b
       ON c.id = b.catid
ORDER BY b.book_amount DESC