Trouble with MySQL WHERE clause and delimited strings

Question

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?

possible duplicate of [mysql select query within a array](http://stackoverflow.com/questions/8840232/mysql-select-query-within-a-array) — Wrikken, Jan 15 '12 at 01:45
[Is storing a comma separated list in a database column really that bad?](http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad) — ypercubeᵀᴹ, Jan 15 '12 at 01:45
@Jamie how is the column defined in the CREATE TABLE statement — Adrian Cornish, Jan 15 '12 at 02:16
It was something like VARCHAR(100) but now I've deleted the column and normalized the table — hohner, Jan 15 '12 at 02:26

score 4 · Accepted Answer · edited Jan 15 '12 at 01:48

4

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'

edited Jan 15 '12 at 01:48

Joseph Silber

214,931
59
362
292

answered Jan 15 '12 at 01:45

Bassam Mehanni

14,796
2
33
41

1

+1 Just stressing the point that this table *really* needs to be normalized. – Joseph Silber Jan 15 '12 at 01:49
Thanks - I've normalized the table now and refactored my code :) – hohner Jan 15 '12 at 02:08

score 2 · Answer 2 · answered Jan 15 '12 at 02:05

2

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

answered Jan 15 '12 at 02:05

ypercubeᵀᴹ

113,259
19
174
235

score 1 · Answer 3 · answered Jan 15 '12 at 01:50

1

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

answered Jan 15 '12 at 01:50

jamesTheProgrammer

1,747
4
22
34

score 1 · Answer 4 · answered Jan 15 '12 at 01:50

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.

Trouble with MySQL WHERE clause and delimited strings

4 Answers4