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:
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;.
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.