Php and MySQL GROUP_CONCAT with separator comma and search where concat by comma

Question

Is it that possible to select in Group_concat with separator comma and search where concat by comma Here My sample of mysql :

products_attributes_id  |products_id|   options_id|     options_values_id 
39                  |   31      |       3     |         3   
35                  |   30      |       2     |         2   
38                  |   30      |       1     |         1   
40                  |   31      |       2     |         2   
41                  |   30      |       1     |         4   
42                  |   30      |       1     |         5   
43                  |   31      |       1     |         4

I want to GROUP_CONCAT options_values_id

SELECT * , GROUP_CONCAT(options_values_id SEPARATOR ',') FROM products_attributes GROUP by products_id

products_attributes_id  |   products_id |   options_id  |   options_values_id   |   options_values_id
35                  |   30          |   2           |   2                   |   2,1,4,5
39                  |   31          |   3           |   3                   |   3,2,4

Now my problem is How I will find the product_id by searching ( option_values_id with comma) if my value is 1,2 or 2,4 then I will get only product_id=30. But if I got value 1,3 I will get nothing. Because product_id 30 don't have option_values_id 3 and product_id 31 don't have option_values_id 1.

I try this code but I can't get the product_id

    SELECT * , GROUP_CONCAT( options_values_id
SEPARATOR ',' )
FROM `products_attributes` WHERE CONCAT(',',options_values_id,',') LIKE '%,1,2,%'
GROUP BY products_id

Maybe there is a way to do this without using GROUP_CONCAT? Can you give us some more background info? — Halcyon
– Halcyon, Commented Jun 6, 2013 at 9:50

Stephan · Accepted Answer · 2013-06-06 09:52:52Z

9

Try this:

SELECT 
    products_id , 
    GROUP_CONCAT(options_values_id SEPARATOR ',') as ops 
FROM 
    products_attributes 
GROUP by 
    products_id
HAVING
    FIND_IN_SET('1',ops)
    OR FIND_IN_SET('3',ops)

answered Jun 6, 2013 at 9:52

Stephan

8,1103 gold badges40 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mickmackusa Over a year ago

"Try this" answers are low value on Stack Overflow because they do very little to educate/empower thousands of future researchers.

Collectives™ on Stack Overflow

Php and MySQL GROUP_CONCAT with separator comma and search where concat by comma

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related