Capturing multiple selections

[Omzy]

This isn't strictly PHP related - more to do with object orientated design / database principles.

 

Basically I'm creating a form that has a set of checkboxes (about 75 in total), the user can have multiple selections here. How should the database be designed to store multiple selections? Do I have one field that stores the selections in a comma-separated array, or do I create separate fields in the database for each checkbox?

 

What is the recommended way of doing this for OO PHP?

[Adam]

Your storage method / design isn't related to OO PHP at all. Personally I'd refrain from using a comma-separated list. Not only does it make removing/updating certain values more complex than necessary, you'll also loose a lot of the functionality / search-ability of your database platform. Instead go with one row per-selection; may seem like it would be more resource heavy but in actual fact when it came to removing/updating that would be more efficient than using the comma-separated list.

[Omzy]

Thanks MrAdam. So you mean have a separate table for the checkboxes and populate that with the selections?

[FaT3oYCG]

you should have two tables for example using a survey

 

table: survey

 

surveyid

surveyusername

 

table: surveyanswer

 

surveyanswerid

surveyid

surveyanswervalue

 

and then in your case the survey answer table would have 75 entries for one survey but they would all have the same surveyid so that they could be related back to the survey that was taken. that is pretty basic but should give you the idea.

 

[Omzy]

FaT3oYCG - cheers. i take it if there were several multi-select questions, you'd need a separate table for each multi-select question right?

[FaT3oYCG]

well, if you incorporated a question table and then added a questionid field to the surveyanswer table then you would be able to have more than one multiple selection question as they would all have different combinations of id's.

 

for example:

 

survey 1

question 1

answer 1

answer 2

answer 3

answer 4

 

would be different to

 

survey 1

question 2

answer 1

answer 2

answer 3

answer 4

 

as you see putting the id's side by side you would get something along the lines of:

 

111

112

113

114

 

for the first set and

 

121

122

123

124

 

for the second set.