Posted by Justin.Voelker on 04/02/07 16:16

On Apr 2, 3:56 am, "Bob Bedford" <b...@bedford.com> wrote:
> Hi all,
>
> don't know if I must ask in a mysql NG, but this seems more a php question.
>
> I've to provide a group of items the user may select or not (checkboxes).
> The user may choose from 0 to 35 choices and can select more than 1 choice
> at a time. (multiselect possible).
>
> How to store and retrieve on a mysql field ? how to automate this ? must I
> put in an array, how to build the query ?
>
> Thanks for help.
>
> Bob

Bob,

One easy yet difficult solution may be to have a field called "sports"
and inside of it, store something like 001011... where each number
represents each sport. So soccer is position 1, football is position
2, etc. Then when you want to display that information you can have
some simple case of if/then statements inside of a loop looking at
positions and values. For example, "if substr(sports,0,1) == '1' then
echo 'soccer';" etc. This isn't the prettiest solution and you will
have major issues on your hands if you ever add or remove a sport.

The solution I would choose, as mentioned by Jerry, is to have another
table where you can relate the two. Say my user_id is 1, and each
sport has a sport_id, and I had three sports with the sport_ids 3, 5,
and 6. You could have a table that just had the user_id and sport_id
fields: One record would be user_id 1, sport_id 3. The next record
would be user_id 1, sport_id 5... and so on. Then when you need to
query which sports user 1 has listed, you can do something like
"SELECT users_sports WHERE user_id = '1'" and you will have 3 records,
each one with a different sport_id that I have selected.

Although database normalization is the "correct" way to do things,
sometimes it isn't the easiest. In this case, it would be the easiest
to normalize your database with that middle table because if you ever
removed or added a sport, your data would be fine unlike the first
method I mentioned which would result in inaccurate data. I hope this
helps!

[Back to original message]