如何在数据库中分解值并在选择查询中使用它？

Question

So, i have a checkbox in my blog for input category where the category value can be multiple. I insert them into database using implode function. Now i want my blog filter by categories if i use the below query it only gets where the category id is single. How can i fetch data where category id has multiple value. And it should be visible in every category it was checked.

public function getPublishedBlogInfoByCategory($id){
    $sql = "SELECT * FROM blogs WHERE category_id = '$id' ORDER BY id DESC";
    if(mysqli_query(Database::dbConnection(), $sql)){
        $queryResult = mysqli_query(Database::dbConnection(), $sql);
        return $queryResult;
    }else{
        die('Query problem'.mysqli_error(Database::dbConnection()));
    }
}

Here's i'm calling that function

<?php
require_once 'vendor/autoload.php';
$blog = new App\classes\Blog;
$app = new App\classes\Application;

$queryResultCategory = $blog->getAllPublishedCategory();

$id = $_GET['id'];
$queryResult = $app->getPublishedBlogInfoByCategory($id);
$queryResultCategoryName = $app->getBlogCategoryName($id);
$categoryName = mysqli_fetch_assoc($queryResultCategoryName);

$queryResultEvent = $app->getLimitedPublishedEvent();

?>

Answer 1

I agree with aynber that a pivot table would probably fit your needs better than a string column with multiple category ids concatenated together. However, to answer your question, rather than

= '$id'

which only matches if the entirety of the field matches the entirety of $id, you probably just need to use

LIKE '%$id%'

which treats the % as wildcards to match any number of characters before or after the $id

EDIT:

CREATE TABLE `blog_category_pivot` (
    `blog_id` INT(11) UNSIGNED NOT NULL,
    `category_id` INT(11) UNSIGNED NOT NULL,
    PRIMARY KEY (`blog_id`, `category_id`),
    CONSTRAINT `blog_fk` FOREIGN KEY (`blog_id`) REFERENCES `blogs` (`id`),
    CONSTRAINT `category_fk` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`)
);

Then to use the data you can just do a select from blogs and join the blog_category_pivot table to check if it has a given category. If you want to list the categories of a blog you already have an id for you can do a select name from categories joining the blog_category_pivot table on blog_id = $blog_id and category_id = categories.id