CODEWITHSUNDEEP
phpmysqlsqlregexdatabase
Omer Farooq
Omer Farooq

Reputation: 4074

Find Items in a comma separated database column

I am working on searching algorithm for an ecommerce platform. The platform is able to create variations for different products and all the available variations for a products are included in a comma separated column. Here are all the fields.

  1. product_id - eg 1
  2. variation_combo - eg 54,35,49 (these are comma separated ids for different variations)
  3. product_name - eg "Nike branded Tshirt"

Here is what i would like to achieve, something like

Upvotes: 0

Views: 75

Answers (3)

Kapil
Kapil

Reputation: 987

Refer this: MySQL query finding values in a comma separated string

select product_id from table where find_in_set('35',variation_combo) <> 0   and 
find_in_set('54',variation_combo) <> 0 and find_in_set('49',variation_combo) <> 0

Upvotes: 2

Dmitry Egorov
Dmitry Egorov

Reputation: 9650

I strongly recommend to re-design and move the list values into a detail table.

But if you're absolutely bound to the list in a field design, you may use RLIKE to check for a value in the list. If the IDs are just alphanumeric (or more precisely don't contain word boundaries such as 001-01), any single ID may be matched with the \bID\b regex. So your statements would turn to

SELECT product_id
    FROM table 
    where variation_combo RLIKE '\\b35\\b'
        AND variation_combo RLIKE '\\b54\\b'
        AND variation_combo RLIKE '\\b49\\b'

SELECT product_id
    FROM table 
    where variation_combo RLIKE '\\b35\\b'
        AND variation_combo RLIKE '\\b54\\b'

SELECT product_id
    FROM table 
    where variation_combo RLIKE '\\b49\\b'

Upvotes: 1

Sulaiman Adewale
Sulaiman Adewale

Reputation: 112

You can use the LIKE keyword

SELECT product_id FROM table where variation_combo LIKE '%35%' AND LIKE '%54%' AND LIKE '%49%'

I hope this helps

Upvotes: 2

Related Questions