PHP MySQL Searching multiple values in one field

Question

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'

or

$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'

RDL · Accepted Answer · 2010-12-17 15:34:20Z

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

People
-------
ID
NAME

Options
-------
ID
OPTION

Options_People
--------------
OPTION_ID
PEOPLE_ID

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#)

Oliver A. · Accepted Answer · 2010-12-17 15:43:01Z

2

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:

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

   Options:
    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.

edited Dec 17, 2010 at 15:43

answered Dec 17, 2010 at 15:10

Oliver A.

2,9002 gold badges20 silver badges21 bronze badges

3 Comments

Mazatec Over a year ago

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.

Oliver A. Over a year ago

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

Mike Over a year ago

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.

robjmills · Accepted Answer · 2010-12-17 15:02:46Z

0

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

answered Dec 17, 2010 at 15:02

robjmills

18.6k16 gold badges81 silver badges124 bronze badges

2 Comments

Mazatec Over a year ago

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?

Oliver A. Over a year ago

You should make an entry for each valid combination of 'name' and 'num'. John | 21 ; John | 22, John |23

user2044770 · Accepted Answer · 2013-02-05 21:33:04Z

0

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

answered Feb 5, 2013 at 21:33

user2044770

893 silver badges12 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Searching multiple values in one field

4 Answers 4

Comments

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related