如何在类别页面中获取多个类别帖子？
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>
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.
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