Finding totals for multiple answer question

[guttyguppy]

I'm building a survey, and most of the questions are multiple choice, i.e. radio buttons. I can easily find the total for each answer with queries like:

 

select count(*) FROM survey where question3='answer5'

 

However, one question is multiple answer, i.e. checkboxes. In this case, each submission may have more than one answer. In my processor script, I'm storing the data using the php serialize function, like:

 

$multipleAnswer = serialize($_POST["multipleAnswer"]);

 

so the data should be getting stored correctly, I hope. But I'm not sure how structure the query to fetch totals for each answer, if the user has chosen more than one.

 

I hope I'm being clear enough, and thanks for your help.

 

 

[JustLikeIcarus]

The serialized array your storing needs to be unserialized by php. There isn't really a way for mysql to do that.  The best thing to do would create a new table to store answers. This would allow you to query the data easily.

[guttyguppy]

OK, I'm guessing that would entail making a table just for that one question? How would I query that table to tally up the votes for submissions that list more than one answer?

[fenway]

OK, I'm guessing that would entail making a table just for that one question? How would I query that table to tally up the votes for submissions that list more than one answer?

No -- one table of answers, linked to question_id.