Checkbox Multiple Values

Best way with MySQL?

Full Member

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...

Senior Member

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: