MySQL using LIKE command to find between semi-colon

mysql, php

Solution

The penalty of a denormalized schema like this is you have to do more work with your query to ensure an accurate match:

SELECT *  
FROM `products` 
WHERE `category` = 'swarovski' 
AND (
    `colours` = 'silver' 
    OR `colours` LIKE 'silver;%' 
    OR `colours` LIKE '%;silver;%' 
    OR `colours` LIKE '%;silver' 

)

SQL Fiddle Example

Problem

This is my data ``` 385_white 99 swarovski 12 black;blue 386_white 99 swarovski 12 black;blue;green 387_white 99 swarovski 12 yellow;green 389_white 99 swarovski 12 white;silver 385_white 99 swarovski 12 silver ``` This is an example query ``` SELECT * FROM `products` WHERE `category` LIKE 'swarovski' AND `colours` LIKE 'silver' ``` The results returned are empty. If the user was to have black, then it should return both the first records. There is no way for me to edit the `color;color;color` format. That is fee sable at this time.

Original source