How to get multiple category posts in category page?

Question

I'm working on a custom CMS. My problem is I have a post with two categories 'tutorial' and 'language'. I want to display it as an item in category page.

This is my book_category table

and this is my books table.

Question 1: How to store book in database with multiple categories? Should I store category ids as a string value in database?

Question 2: How to query posts with multiple categories ?

This is my header.php code

<?php  include 'includes/db.php'; ?>
<html lang="fa-IR" dir="rtl">
   <head>
      <title>CMS کتابخانه</title>
      <script type="text/javascript" src="./assets/js/jquery.min.js"></script>
      <script type="text/javascript" src="./assets/js/bootstrap.js"></script>
      <link rel="stylesheet" href="./assets/css/bootstrap.min.css" type="text/css" >
      <link rel="stylesheet" href="./assets/css/bootstrap.rtl.min.css" type="text/css" >
      <link rel="stylesheet" href="./assets/css/main.css" type="text/css" >
      <link href="./assets/css/fontawesome-all.min.css" rel="stylesheet">
   </head>
   <body>
      <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
        <a class="navbar-brand" id="logo" href="#">کتابخانه من</a>
        <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarNav" aria-controls="navbarNav" aria-expanded="false" aria-label="Toggle navigation">
          <span class="navbar-toggler-icon"></span>
        </button>
        <div class="collapse navbar-collapse" id="navbarNav">
          <ul class="navbar-nav">
            <li class="nav-item <?php if($_SERVER['REQUEST_URI'] == '/cms/' || $_SERVER['REQUEST_URI'] == '/cms/index.php' ){echo 'active';} ?>">
              <a class="nav-link" href="index.php">خانه <span class="sr-only"></span></a>
            </li>
            <li class="nav-item dropdown">
              <a class="nav-link dropdown-toggle" href="#" id="navbardrop" data-toggle="dropdown">
               دسته بندی
              </a>
              <div class="dropdown-menu">
            <?php 
               $select_cat = mysqli_query($connection,"SELECT * FROM book_category");
                while($row = mysqli_fetch_array($select_cat)) {

                if(isset($_GET['category_name'])){
                  if($_GET['id'] == $row['category_id']){
                     $class='active';
                  }else{
                     $class='';
                  }
                }else{
                  $class='';
                }
                echo '<a class="dropdown-item '.$class.'" href="book_category.php?id='.$row['category_id'].'">'.$row['category_name'].'</a>';
                }
            ?>

              </div>
            </li>
            <li class="nav-item">
              <a class="nav-link" href="#">داغ ترین ها</a>
            </li>
            <li class="nav-item">
              <a class="nav-link" href="#">نویسندگان</a>
            </li>
            <?php if(!isset($_SESSION['user'])) : ?>
            <li class="nav-item">
              <a class="nav-link" href="register.php">عضویت</a>
            </li>
            <?php endif; ?>
          </ul>
        </div>
      </nav>
      <div class="p-2"></div>

and this is my category.php for displaying specific categories posts.

<?php include 'includes/header.php'; ?>
      <div class="container">
         <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
          <a class="navbar-brand" href="#">آخرین کتاب ها</a>
        </nav>
         <article class="row">
            <section class="col-lg-8">
            <div class="card-columns">
            <?php 
               $result = mysqli_query($connection,"SELECT * FROM books WHERE cat_id = ");
                while($row = mysqli_fetch_array($result)) :
            ?>
               <div class="card">
                 <img class="card-img-top rounded mx-auto d-block" src="<?php echo $row['image']; ?>" alt="<?php echo $row['title']; ?>" width="199" height="312">
                 <div class="card-body text-right">
                  <h3 class="card-title"><a href="post.php?id=<?php echo $row['id']; ?>"><?php echo $row['title']; ?></a></h3>
                  <p class="card-text"><?php echo mb_substr($row['description'],0,250); echo '...'; ?></p>
                   <a href="book.php?id=<?php echo $row['id']; ?>" class="btn btn-primary">ادامه مطلب</a>
                 </div>
               </div>
            <?php endwhile; ?>
            </div>
         </section>
         <?php include 'includes/sidebar.php'; ?>
         </article>
      </div>
      <div style="width:50px;height:50px;"></div>
<?php include 'includes/footer.php'; ?>
   </body>
</html>

Have another table book_categories, where you store each book's different categories. — jarlh, Jun 19 '18 at 10:57
You have to create a second table which will connect the two other tables. And you should use the INNER JOIN inside your queries. — J Quest, Jun 19 '18 at 10:58
You should make a another table where you can add book id and category id. And get data using join with three tables — rawathemant, Jun 19 '18 at 10:59
@JQuest i already have two tables , aren't they enough ? a book_categories table and books table. — Naderjlyr, Jun 19 '18 at 11:00
@rawathemant three tables ? book_categories table, books table, and books_term for example ? — Naderjlyr, Jun 19 '18 at 11:02
@Nader-J I wrote an example with an example query with INNER JOIN — J Quest, Jun 19 '18 at 11:19

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

You should search the internet for many to many relationships:

One book may have zero or more categories. On the other side one category may have zero or more book relations. This relation is called many to many.

To archive this goal you have to add an mapping table:

table: book   table: book_category   table: category
==========    ====================   ===============
- id          - book_id              - id
- name        - category_id          - name
- author                             - ...
- ...

To query the data there are examples here how to query many-to-many?. You should find many examples for php in the internet ...

Remark: OPs question switches between book and post entity. I decided to use book ... ;)

Edit 1:

OPs question in comment: how to add row for submitted book ...?

You have to know the relevant book and category ids. To connect a book to a catetory, you simply add the id's to the join table.

INSERT INTO book_category(book_id,category_id) values(40,50);

In this example the book with id 40 is joined to the category id 50.

If your question is targeting an php example, I can't tell you because I don't know how your DAO layer is implemented. But this should be a new question I guess.

when i submit a book ,it automatically submit book info in "book" table. how to add row for submitted book in "book_category" ? — Naderjlyr, Jun 19 '18 at 12:36

J Quest · Answer 2 · 2018-06-19T11:20:48.533

0

If a book always has one category you can simply add a field category_id to your book's table, but when a book can have multiple categories and you don't know how much. You can do best by creating a second table that connects with multiple rows of multiple categories.

Create a table books_category with fields like id, books_id, books_category_id. Also make indexes on books_id, books_category_id for fast searching.

For your Query, you should use the INNER JOIN.

SELECT * FROM `books` INNER JOIN `books_category`  ON `books_category`.`books_id` =  `books`.`id` WHERE `books_category`.`books_category_id` = 5

edited Jun 19 '18 at 11:20

answered Jun 19 '18 at 11:10

J Quest

593
2
17

@ChristophS He wants to display it inside a category page, so he should always have a category linked to a book. Otherwise, if he just want's every book with the category's on a book page the `LEFT JOIN` would be better. – J Quest Jun 19 '18 at 11:23
Sorry, deleted my comment (and can't bring it back) when noticed you concrete category id inside your query. – ChristophS Jun 19 '18 at 11:26
1

Yes, you are right. Querying books for a kind of selected category you have to use `INNER JOIN`, to list all books (without category also) you have to use `LEFT JOIN` (sorry again for deleting my first comment to quickly ...) – ChristophS Jun 19 '18 at 11:33

How to get multiple category posts in category page?

2 Answers2

Edit 1: