0

first of all, i have a query like this:

SELECT * FROM post WHERE category_ids IN (24,36,50,60,12);
  • category_ids column have data like this: 16,24,18

now, i want to search in multi values column with multi value string. this query have a problem. fetch rows with just id (24). and cant search with 36,50,60,12

in other word, i have two table : 1. category 2. post . so i selected multi category for a post and save the category ids in post table like this (ex: 12,24,16) now i want fetch all post that have category id 24 AND 36 for example

how can i write my query to work? please help! Thanks

imanic
  • 1
  • 1
  • "_this query have a problem_" What problem does it have? If your `category_ids` are 16, 24 and 18 then obviously 36, 50, 60 and 12 can't be found - because they don't exist. – brombeer Apr 07 '20 at 06:37
  • 2
    Normalize your data and your searching criteria. Or at least one of them (worse). – Akina Apr 07 '20 at 06:37

1 Answers1

0

if your column value is seperated comma

SELECT * FROM post WHERE category_ids REGEXP ',24|24|24,';

can't filtering '24%' or '%24%'

edit like this

SELECT * FROM post WHERE category_ids =24 or category_ids REGEXP '^24,|,24$|,24,';

Community
  • 1
  • 1
Seonsoo Park
  • 31
  • 8
  • 1
    Won't this also match 244 or 124 or any string with 24 anywhere? – ryantxr Apr 07 '20 at 06:55
  • i have two table : 1. category 2. post . so i selected multi category for a post and save the category ids in post table like this (ex: 12,24,16) now i want fetch all post that have id 24. – imanic Apr 07 '20 at 09:07