CODEWITHSUNDEEP
phpmysql
user3670146
user3670146

Reputation: 25

How to write a query for unserialized categories

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) ?

Upvotes: 0

Views: 110

Answers (2)

Shah Amit
Shah Amit

Reputation: 281

If you want to store the categories array, you can use implode(',', $postdata['post_category'])

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

and if you want to search the post based on some category you can use

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

Upvotes: 1

machineaddict
machineaddict

Reputation: 3236

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.

Upvotes: 0

Related Questions