BOTJr. BOTJr. - 1 month ago 7
MySQL Question

MYSql search in concated columns

I have a table that looks something like this

products_table
ID | code | product_variants_id | product_name | variants | variants_value
1 | 1| 123451 | beer cake | color | blue
2 | 1| 123451 | beer cake | temperature | hot
3 | 1| 123451 | beer cake | weight | 0.5
4 | 2| 123453 | ad wrap | color | green
5 | 2| 123453 | ad wrap | weight | 1


I ran the below query to get unique rows for products with the respective variants.

SELECT xx.code, GROUP_CONCAT(concat(xx.variants,':',xx.variants_value))
AS variants_and_values, xx.product_name, xx.product_variants_id
FROM products_table xx
GROUP BY xx.product_variants_id, xx.product_name, xx.code


The below table is obtained as a result of the above query.Now i can simply run through this table and display the products.

code | product_variants_id | product_name | variants_and_values
1 | 123451 | beer cake | color:blue,temperature:hot,weight:0.5
2 | 123453 | ad wrap | color:green,weight:1


Now the real question is that ,If i were to search through the above table and to display only those products with the variant as
hot
, how am i gonna do it?

Answer
select * from t where variants like '%:hot' or variants like '%:hot,%'
Comments