Searching comma separated database field with comma separated user input

Question

I have a data stored as comma separated in the database field. Like say in a field colors there are following rows.

red,blue, green
blue, white, purple, pink
yellow, khaki, maroon, orange
gray, lemon, black, brown

Next user has a textbox in a html form, where he enters a colors in a comma separated form. I want to search the database to return records matching his input. For example.

If I enter red, purple, orange, then the query should return three rows because first row has red, second row has purple and third has orange.

I m not getting the way to easier than breaking the user inputs into array and looping through each colors and search the database to get results. Is there any easier way to do this?

Edit

I know storing data in comma separated form is not a good practice but I m working on a updates on application which was made by other programmer. And at this stage I cannot afford to change table structures.

Thanks

The reason you're not finding it easy it that you haven't normalised your data structures. If you had, you'd be skipping through a field of daisies right now. — podiluska, Oct 10 '12 at 14:53
http://stackoverflow.com/questions/3653462/is-storing-a-comma-separated-list-in-a-database-column-really-that-bad — Alex K., Oct 10 '12 at 14:55
There are some answers here (find_in_set) http://stackoverflow.com/questions/738133/comma-separated-values-in-a-database-field?rq=1 — Alex K., Oct 10 '12 at 14:59
How often are records inserted/updated as opposed to searched? — Tim Lehner, Oct 10 '12 at 15:08
@TimLehner, I think search is done little more than inserting and updating them — WatsMyName, Oct 10 '12 at 15:12

score 1 · Answer 1 · answered Oct 10 '12 at 14:58

Unfortunately, there is no simpler way.

However, the better way would be to normalize the DB structure, keep the colors in a separate table, and create a pivot table between your main one and the colors table.

After that, you'd also have to split the user entered values, take the ID of the colors then search on the pivot table.

The main advantage of this would be that you'd make use of DB indexes when you do the search.

eggyal · Accepted Answer · 2012-10-10T15:07:46.447

Of course normalisation is the best approach if it is an option:

CREATE TABLE colors ( color VARCHAR(6) NOT NULL PRIMARY KEY );

INSERT INTO colors VALUES
  ('red'), ('blue'), ('green'), ('white'), ('purple'), ('pink'),
  ('yellow'), ('khaki'), ('maroon'), ('orange'), ('gray'),
  ('lemon'), ('black'), ('brown');

CREATE TABLE itemColors (
  item_id BIGINT UNSIGNED NOT NULL,
  color   VARCHAR(6)      NOT NULL,
  FOREIGN KEY (item_id) REFERENCES my_table (id),
  FOREIGN KEY (color)   REFERENCES colors   (color)
);

INSERT INTO itemColors (item_id, color)
  SELECT my_table.id, colors.color
  FROM   my_table JOIN colors ON FIND_IN_SET(colors.color, my_table.colors);

ALTER TABLE my_table DROP COLUMN colors;

Then one would merely need do:

SELECT *
FROM   my_table
WHERE  id IN (
               SELECT item_id
               FROM   itemColors
               WHERE  color IN ('red','purple','orange')
             )

Otherwise you could build a query (in your application) that uses MySQL's FIND_IN_SET() function:

SELECT *
FROM   my_table
WHERE  FIND_IN_SET('red',    colors)
    OR FIND_IN_SET('purple', colors)
    OR FIND_IN_SET('orange', colors)

I think I will go with second option. For this I have to break user input into array and loop through it to create a `FIND_IN_SET` query like you have done. Thanks buddy — WatsMyName, Oct 10 '12 at 15:18

Searching comma separated database field with comma separated user input

2 Answers2