Take the 2-minute tour ×

How can I store multiple values in a single SQL field and ensure this data is searchable?

For example I want to be able to do this

name | num
John | 21, 22, 34
Mike | 41, 32, 43
Dave | 12, 23, 34

$query = SELECT name from table WHERE num = '12' 
// result should be 'Dave'


$query = SELECT name from table WHERE num = '22' or num = 41 
// result should be John and Mike

I'm using PHP so I know I could serialize the data when its being input, then unserialize and search the resulting array, but the dataset is quite large so this will be quite a slow query as I will have to retrieve the entire dataset and loop through the resulting array.

Is there something I may have missed? I really don't want to have to create a table for each of the possible options as there are absolutely loads of groups and many options in each.

Any ideas? Thanks in advance

EDIT: The above example is just an example there could be any number of 'nums'

share|improve this question

4 Answers 4

up vote 1 down vote accepted

You should do it that way:

Craete an entry for each combination:

John | 21
John | 22
John | 23
Mark | 19
Mark | 22

If you have any aditional informaions in youtable create 2 tables:

UserId |FirstName |Lastname
1      |John      |Doe
2      |Mark      |Hamill

    UserId |Num
          1| 21
          1| 22
          1| 23
          2| 19
          2| 22

If you have to stick to a existing database structure with just one column you can store it as a text/varchar. just put a pipe (or any other char) at the start of your string and after each number|21|21|23|. Then you can search with LIKE '%|23|%'

This is slow and ugly as hell, but sometimes adding tables or aolums is not an option.

share|improve this answer
Would this be slower than doing it with a join? Problem for me is the sheer number of option groups and options within - which is currently stored as a PHP array and I am keen to find a suitable workaround. –  Ash Dec 17 '10 at 15:16
A LOT SLOWER! if you have ANY other option do not do it. Just did that once because we had some 3rd party software using the database that would crash if I modifyed the db –  Oliver A. Dec 17 '10 at 15:22
Joins can use indexes, they aren't as evil as you are making them sound, Ashley. They are lot better solution than this would be. –  Mike Dec 17 '10 at 15:28

For optimal speed and efficiency, put the 'nums' in a separate table linking to the ids.

Using LIKE will work too however it also needs to do a comparison against part of the string. Depending on the size of your data set this can also take some time.

Another option is mysql regex using word boundaries ie:

SELECT name from table where nums REGEXP '[[:<:]]##[[:>:]]';

Each of these options will work however moving the data to a separate table is the proper method especially if there are a lot of possibilities.

For this method you would need three tables




To get all 'people' with a specific option it's:

SELECT p.* FROM People p, Options_People op 
WHERE p.ID = op.PEOPLE_ID and OPTION_ID = #option#

To get all 'people' with any of multiple options:

SELECT p.* FROM People p, Options_People op 
WHERE p.ID = op.PEOPLE_ID and OPTION_ID IN ( #option1#,#option2#,#option#)
share|improve this answer

A name to num pivot table is probably the most elegant way to do this

share|improve this answer
do you mean a creating a table for each group of options, with a row for each option and another table which links the two? –  Ash Dec 17 '10 at 15:03
You should make an entry for each valid combination of 'name' and 'num'. John | 21 ; John | 22, John |23 –  Oliver A. Dec 17 '10 at 15:19

SELECT name,GROUP_CONCAT(num) FROM table_name GROUP BY name

share|improve this answer

Your Answer


