Adding more than one value to a field and spliting upon retrieval

[Taylor_1978]

Hiya,

I have seen this done before, but not quite sure exactly how to implement it.

In a HTML Form, I have the users choose at least one age group. The values are 1,2,3,4,5,6 and 7. The user selects from a multiple select box so they can choose more than one option.

I want these multiple options stored in the one field, so it may look something like this: 1, 2, 7.

I need to know how I actually go about this and then how I seperate/split it when retrieving. For example: If I do a search:

mysql_query("SELECT * FROM teams WHERE age=1")

That the rows that have the value of say:

1. 1,2
2. 1,5,6
3. 5,7
4. 1

Rows 1 2 and 4 will be retrieved because they have 1 in them.

Any suggestions would be greatly appreciated.

Thanks in advance!

Taylor.

[Kiwi]

You would be much better to set-up a child record that would allow you to add as many selections as your like. This would be a table, keyed by the parent ID (eg personID) and the selection. For each option a person selects, you create a new child record.

[Taylor_1978]

Well - yes if only it were that simple! But I'm never simple! LOL

See... there already is a parent/child record.

Basically what I have is a table called teams. teams has 8 fields in it.

id, userid, ageid, regionid, sportid, genderid, sectionid, levelid

Each of these are number values and each field, except 'id' has a parent table.

These are: ages, regions, sports, genders, sections, levels.

SO... A person creates a new team which has the age groups Under 12 and Under 14 (values are 1 and 2), and they play Basketball and Baseball (values 2 and 3).. If someone then browsing does a search for a Basketball Team that plays Under 12s it needs to find "WHERE sportid=2 AND ageid=1" - HOWEVER if they are looking for a team that plays BOTH Basketball and Baseball in Under 12s, I don't want the search coming up with 2 results - when it is the same team. The result should just be Team #1, plays Basketball and Baseball in Under 12... as opposed to two diff results of: Team #1, plays Basketball in Under 12, Team #2, plays Baseball in Under 12.

Did that make any sense at all?

[Kiwi]

Remember, we live in the 00s: parents can have as many children as they like and children can have more than one parent.

One of the first rules of normalisation is to create atomistic data - each field represents only one piece of information. Breaking this rule tends to make the design very messy.

The sort of selects you're talking about will be significantly easier with a normalised design (not to mention more efficient and effective - what you've proposed will mean both 'LIKE' clauses all over the place and a risk of losing data integrity). Add in good use of 'DISTINCT' clauses, the 'CONCAT' function and maybe even a sub-select or two, and you'll be able to get the results you need without sacrificing good design principles.

There are ways to do exactly what you're asking, but it is not a good idea.