如何为未序列化的类别编写查询

Question

so my function post() does the next thing:

$category = serialize($postdata['post_category']);
        $query = "
                 INSERT INTO posts(post_title, post_content, post_category)
                 VALUES ('$postdata[post_title]', '$postdata[post_content]', '$category')
                 ";   

inserts serialized data into the database, but i have a problem when calling a function to show only those posts which have same category, a function for that looks like this:

public function cat($cat){
        global $db;
        $query = "
                 SELECT * FROM posts
                 WHERE post_category LIKE '%$cat%'
                 ";
        return $db->select($query);
    }   

So my question is: is it better to insert unserialized data into database for this particular case (and how to do that) or is it better to pull serialized data and then unserialize it for showing some particular posts (same as previous, tried a couple of options and had no luck achieving the right result) ?

Answer 1

If $postdata['post_category'] is an array, you could also insert multiple categories in the same column:

$category = ',' . implode(',', $postdata['post_category']) . ',';
$query = "
     INSERT INTO posts(post_title, post_content, post_category)
     VALUES ('$postdata[post_title]', '$postdata[post_content]', '$category')
     ";

Mind that you always should have comma at the end and the beggining of the categories string like so: ,1,2,3,4,!

To select the posts from a category:

public function cat($cat){
    global $db;
    $query = "
             SELECT * FROM posts
             WHERE post_category LIKE ',$cat,'
             ";
    return $db->select($query);
}

But this method will not allow you to select posts from multiple categories.

Note: You should consider learning to use multiple tables and JOIN-ining them. One table for posts, one table for categories and one table post_to_categories, where all the categories with their specific posts are linked.