I have a database table which stores products. Each product can have multiple colours. Each colour is represented by its ID rather than a textual description like 'Red', 'Yellow', etc. When the $_POST['colour'] array is imploded into a string (delimited by commas) it is then stored in the table:
product_id | colour
----------------------
1 | 1,2
2 | 10
3 | 7,9
Recently I've tried to create a search form which could select a number of colours and search the database table to see if there are any products which contain at least one of the colours in the search array. So if a visitor wanted to see products for colours 1 and 9, I need to search the 'colour' column for these two values.
I can't use WHERE colour IN (1,9) because I think that only works if you have one value in the column (rather than a delimited array of multiple values). Nor can I use WHERE colour LIKE 1 OR WHERE colour LIKE 9 because it would return products which have a colour ID of 10 or 11 or 12, etc.
Does anybody know how I can do this?
This table need to be normalized if you wanna get good performance, create a productColor table with a one-to-many relation with the product table.
That being said:
SELECT *
FROM Product
WHERE Colour LIKE '%,1,%' OR Colour LIKE '1,% OR COLOUR LIKE '%,1'
OR Colour LIKE '%,9,%' OR Colour LIKE '9,% OR COLOUR LIKE '%,9'
Create an association table for products and colors, rather than storing multiple vales in a single column. Like assocProductsColors with col for productId and colorId include a Id col as the key for the table
Similar to the comments you already have, I'd suggest modifying your database to have:
product_id | colour
----------------------
1 | 1
1 | 2
2 | 10
3 | 7
3 | 9
However, you could also use MySQL Regex Operators, in particular RLIKE ("regex like") to do this.
SELECT *
FROM Product
WHERE Colour RLIKE '[[:<:]][19][[:>:]]'
The regular expression [[:<:]][19][[:>:]] means "match a 1 or a 9 ([19]), where it's a whole word". The [[:<:]] and [[:>:]] mean "word boundary", so the given pattern will only match the 1 or 9 if it's an entire word on its own, and not if it's part of another number.
Normalize the table.
In the mean time, you could use:
SELECT *
FROM Product
WHERE FIND_IN_SET( 1, Colour )
OR FIND_IN_SET( 9, Colour ) --- etc