Gjert Ingar Gjersund Gjert Ingar Gjersund - 6 months ago 11
PHP Question

Selecting posts where category is like multiple category IDs using REGEXP

I have a string of

category IDs
that look like this
1;2;3;4;
.

Now I want to fetch all the posts that contain each of these variables in the
category
column of the
posts
table.

The
category
column also have the content like this
1;2;3;
depending on what categories have been attached to the post.

How can I set up a
PDO query
that checks if any of the value from the primary string is found within the
category
column in the post table?

I am looking for something which would look like
category LIKE = IN (2;3;4;)
, also it has to work with double/triple ... digits like this:
2;44;23;
.

Example:


  • Post 1: 1;2;

  • Post 2: 3;

  • Post 3: 1;



I use the string
1;
to fetch from the post table, and the result I want back is
Post 1
and
Post 3
because they both contain
1;
.

Answer

Best option is to refactor the table into a posts table (without the categories list field), and posts_categories table (with post_id, and category_id fields).

Then use a simple query like this:

SELECT DISTINCT p.*
FROM posts_categories AS pc
INNER JOIN posts AS p ON pc.post_id = p.post_id
WHERE pc.category_id IN ([your list of values])
;

Unfortunately, most database libraries do not support arbitrary lists of parameters, so you may need to generate the exact series of ?,?,?,... in code; but I am unfamiliar with pdo and am only marginally acquainted with php.

Edit: Tweaked query to only show data from posts, and only once per post.


If you want the list of categories as well....

SELECT DISTINCT p.*
     , GROUP_CONCAT(assoc_pc.category_id SEPARATOR ';') AS catList
FROM posts_categories AS filtering_pc
INNER JOIN posts AS p ON filtering_pc.post_id = p.post_id
INNER JOIN posts_categories AS assoc_pc ON p.post_id = assoc_pc.post_id
WHERE filtering_pc.category_id IN ([your list of values])
GROUP BY p.post_id
;

GROUP_CONCAT is MySQL specific; if you want a bit more platform independence, you may want to SELECT p.*, assoc_pc.caetgory_id and just ORDER BY p.post_id and build the catList in code when processing the ungrouped results.

Edit: fixed typo, incorrect alias, in second query example.