homepage Welcome to WebmasterWorld Guest from 54.197.215.146
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Checkbox Multiple Values
Best way with MySQL?
rover




msg:1580239
 4:39 pm on Oct 31, 2005 (gmt 0)

If I have a field that will use checkboxes on a php/html page that would have multiple values from a set list of options, should I be using a mysql field type like set or enum?

Or should I not try to capture this all in one field, and rather be using three tables (main table, attribute table, and a table that maps the relations between the main table and attribute table)?

I need users to be able to add the options to their profile, through checkboxes, and I would need to have it searchable by the attributes.

So if the field was colors, and the user would choose from red, blue, green (and they can choose more than one). Later they could log in and make changes to their selections when updating their profile.

(In the actual application there would be about 30 - 40 choices not just 3).

I think initially it might be easiest for me to use a set or enum field, but I don't know if this would be the right way to do it, or if I should be using the separate tables.

If anyone has any insight about this, I would appreciate it very much...

 

physics




msg:1580240
 7:55 pm on Oct 31, 2005 (gmt 0)

Why not just make a table like this

id color
1 red
2 green
3 blue
...

And draw the id, color from there to use in the checkboxes. Then in their profile store just store the id. Or is this what you were saying already above?

aspdaddy




msg:1580241
 8:30 pm on Oct 31, 2005 (gmt 0)

The normalised way is to have a 3 tables as you suggest.

This can be a bit of extra work writing queries if your datbase doesnt support Views, but it is the best approach.

An alternative to a SET or Enum type is to treat the textboxes as an ordered list of binary flags and calculate and store a single number that when converted to binary shows you which boxes are ticked:

Red, Blue, Green
1=Green
2=Blue
3=Blue,Green
4=Red
...
7=Red,Blue,Green

You can either solve it in the application or database (prefered).

HTH

rover




msg:1580242
 8:44 pm on Oct 31, 2005 (gmt 0)

Thanks very much for the input. I think I will spend the extra time initially (for setting up the queries, etc.) to create this in a more normalized way, using the tables to hold the values.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved