help with using checkboxes in a form

[someone]

i have a form that captures user's information and insert that into the db for later statistics. the form has 10 checkboxes of tv shows they like to watch. users can check as many boxes as they want. i am currently setting it this way:

<input type="checkbox" name="show1" value="Survivors">

<input type="checkbox" name="show2" value="Big Brother">

<input type="checkbox" name="show3" value="The Amazing Race">

......and so on

does that mean i would have to have 10 fields in my db to capture which checkbox the user has checked?

thanks

[CaseyRyan]

If you are always going to have those 10 choices, or if this is a simple solution and you don't mind changing the table structures everytime you change your questions: having 10 fields would be one solutions.

Alternate Solution 1:
A possible solution would be to have a single varchar field with a comma deliminated list of unique codes designating the choices that were made. This would allow you the flexibility of increasing or decreasing the number of choices that were made, while supporting different kinds of answers in the same field (eg: Free text answers, numeric answers, single choice answers). You would probably want to add a field in the table to indicate what kind of question it is so you can programmatically know how to parse the answer field.

Alternate Solution 2:
A more complicated solution that would grant you the flexibility of increasing/decreasing the number of choices you want would be to have a separate table with the list of choices you have. One record for each choice. Then tie the choices that they have made to their answer record for that question via an XREF table. It would use the following table structure, but would be an awesome survey system.

tbQuestion table ( 1 record for that question)
- questionID - unique id for this table.
- questionText - question text (eg: 'What show do you like?').
- questionAnswerType
- use this to drive how it is displayed
- eg: FreeText, MultipleChoice, SingleChoice

tbQuestionAnswerXref table (1 to many answers for a question)
NOTE: table would only be used for MultipleChoice and SingleChoice
- questionAnswerXrefID - unique id for this table.
- questionID
- ForeignKey to the tbQuestion.questionID table.
- ties to the question.
- answerID
- ForeignKey to the tbAnswer.answerID table.
- ties to the answer.
- sequenceNum - ordering for the overall answers to be displayed in.

tbResponse table (1 record for the user's answer to question)
- responseID - unique id for this table.
- questionID
- Foreign Key to the tbQuestion.questionID table.
- ties to the question they are responding to
- other information: datestamp, user information

tbResponseAnswerXref table (0 to Many records)
- ResponseAnswerXrefID - unique id for this table
- responseID
- Foreign Key to the tbResponse.responseID table.
- ties this record to the response and therefore the question.
- answerID
- Foreign Key to the tbAnswer.answerID table.
- ties this record to one of the choices they made.

tbAnswer table (10 records)
- answerID - unique id for this table
- answerText - display text for that answer (eg: 'Survivor')

Anyway, that's what I got. Most likely you're original or my first alternate solution would be the best. I left the last one in there because I got excited and typed it all out before realizing that it was prolly overkill :)

-=casey=-

[willybfriendly]

Another option would be to convert the responses to binary and store it in one field of the DB. Use bitwise operators (http://us2.php.net/manual/en/language.operators.bitwise.php) to manipulate and extract.

Ahhhh, this takes me back to the days of my C-64, Assembly and the need to use every "bit" of memory efficiently :)

Actually, a binary number is the perfect storage medium for a checkbox, the bit is either set or not, just as the box is. It is like a little line of switches on the wall. 00011100000011100000011100000011

WBF