many choices

[Bob Bedford]

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

[Jerry Stuckle]

Re: many choices

Bob Bedford 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,

You're looking at a many-to-many relationship here - you have more than
one user, and more than one checkbox.

Database normalization rules (a good thing to look up) show that you
need a separate table to hold the relationship.

So you would have a table with the user information, a table for the
checkbox descriptions (unless they would never, in a million years,
change) and a third table with entries for a userid and a selected checkbox.

Probably the easiest way is to get the data from the HTML code as an
array and insert each element into the table.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Bob Bedford]

Re: many choices

You're looking at a many-to-many relationship here - you have more than

one user, and more than one checkbox.

Absolutely not...You message tell me I didn't explain very well, that's my
fault sorry.

I've a page where a user can choose what it does like (it's interest
points).
For example:
soccer,
football,
baseball,
tennis,
rugby,
swimming....

So the user may choose none or many things he is interested in and I've to
store in his profile.
I don't know how to manage this. Every item has its own number but I don't
know how to save in a mysql field (wich kind of field must I use) and also
how to save from the form where the items are shown to the DB (the query).
Also how to retrive those items back when the user want to edit his profile.

Thanks for help

[Jerry Stuckle]

Re: many choices

Bob Bedford wrote:

>You're looking at a many-to-many relationship here - you have more than
>one user, and more than one checkbox.

Absolutely not...You message tell me I didn't explain very well, that's my
fault sorry.
>
I've a page where a user can choose what it does like (it's interest
points).
For example:
soccer,
football,
baseball,
tennis,
rugby,
swimming....
>
So the user may choose none or many things he is interested in and I've to
store in his profile.
I don't know how to manage this. Every item has its own number but I don't
know how to save in a mysql field (wich kind of field must I use) and also
how to save from the form where the items are shown to the DB (the query).
Also how to retrive those items back when the user want to edit his profile.
>
Thanks for help
>
>
>

Yes, you have more than one user, and more than one selection. This is
a many-to-many relationship (many users, many selections). My comments
are correct.

Do some searches on "database normalization".

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Justin.Voelker]

Re: many choices

On Apr 2, 3:56 am, "Bob Bedford" <b...@bedford.c omwrote:

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!