mysql search keywords in string table

Question

I have a catID column in product table, it is contain category ids as string,

Something like that '142,156,146,143'

and i Have a query '?catID=156,141,120'

i want to search each id in catID column.

I use this query:

SELECT * FROM product WHERE catID REGEXP '156|141|120'

this code return products which have any id in catID column , but I want to return products which is have all id,

So , I'am looking for and operator in REGEXP , but I'am couldn't find.

I want to use REGEXP or something like that which function provide to find product with one query , I don't wan to use

catID LIKE '156' AND catID LIKE '141' ....

if it is posibble.

EDIT : I don't want to perform a function one more time , because the query can be have 100+ id so it's make more harder to write code,

Answer 1

You need to use find_in_set() for each category id parameter in order to find the values in set,also if you can alter the schema then do normalize it, by having another junction table which holds the relation from this table to category table

select * from 
product 
where 
find_in_set('142',catID ) > 0

For multiple values like find_in_set('161,168,234,678',preferred_location ) > 0 no it can't be possible doing like this you have to perform for each location id like

select * from 
product 
where 
find_in_set('142',catID ) > 0
and find_in_set('156',catID ) > 0
and find_in_set('146',catID ) > 0
and find_in_set('143',catID ) > 0 ... for more

Database normalization

find_in_set

Sample Schema

Table

• Products (id,other columns)
• Categories (id,other columns)
• Product_categories (id,product_id,category_id)

Product_categories is a junction table which will hold product_id and one category_id per each product so each will have a relation with single category and single product at a time

For example

Products

id  name

1  product 1
2  product 2

Categories

id  name

142  category 1
156  category 2
146  category 3
143  category 4

Product_categories

id  product_id  category_id
1     1          142  
2     1          156  
3     1          146  
4     1          143  

Now you can join these tables and query like below using in() and count should be equal to the no of category ids provided as parameter

select p.* from
Products p
join Product_categories pc on (p.id = pc.product_id)
where pc.category_id in(142,156,146,143)
group by p.id
having count(distinct pc.category_id) = 4

Sample Demo

or if you can't count the provided category ids as parameter you can do this by following query

select p.* from
Products p
join Product_categories pc on (p.id = pc.product_id)
where pc.category_id in(142,156,146,143)
group by p.id
having count(distinct pc.category_id) = 
ROUND (   
        (
            LENGTH('142,156,146,143')
            - LENGTH( REPLACE ( '142,156,146,143', ",", "") ) 
        ) / LENGTH(",")        
    ) + 1

Sample Demo 2