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'

Answer 1

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.