Sorting questionnaire data using PHP and SQL

Question

I have created a simple multiple choice questionnaire using HTML, PHP and SQL, which when submitted, adds numeric values to a table which each represent specific answers to each of the questions.

For example, I have four questions, and one submitted form may appear in my database cell as '0,1,3,1' (So answered the first option '0' for Question 1, and '1' for Question 2 etc;).

I'm now trying to output that data, and count how many people have selected which answer on each question.

How can I loop over that data in the table and get PHP to increment some kind of array each time so that I eventually have a count of 'This many people answered X for Question X'.

I'm a little confused! Thanks in advanced.

/* EDIT */

My database layout is simple (possibly where I'm going wrong), it's simply:

Questionnaire ID | Results

1 --------------------0,1,3,2

(Couldn't get the table to layout well but hopefully that's clear)

As for what I have tried so far, not much as I couldn't think what to do.

I've got two for loops, one nested inside the other, that execute relative to the amount of questions I have, which create empty arrays for each question and answer.

Now I need to fill these arrays with counts of how many times each answer has been selected.

Could you provide your question with some details about your database tables and perhaps some of the code you tried with yourself? — Repox, Nov 01 '10 at 10:28

score 2 · Answer 1 · answered Nov 01 '10 at 10:33

2

Why not do it in the original SQL, ie.

"select question, answer, count(answer)
group by question, answer"

This should then output a list of your questions, the answers that were chose, and how many times...

answered Nov 01 '10 at 10:33

SW4

69,876
20
132
137

The problem is, if I understand you correctly, that I've currently not saved the data in that format. All the answers are simply saved in a single cell of the 'Answers' column, so look like a string of numbers like '0,1,5,2'. Do you think I should restructure the way the data is stored? – Jack Roscoe Nov 01 '10 at 10:36
What if the student skip questions? Do you just have a succession of commas without numbers? – Alex Jasmin Nov 01 '10 at 10:45
I have an option for every question 'Unaswered', which is selected by default when the questionnaire loads, so if no choice is selected it will always be represented by '0' in the database. – Jack Roscoe Nov 01 '10 at 10:47

Alex Jasmin · Answer 2 · 2010-11-01T11:34:24.877

You should refactor your database to support these kind of queries.

But the following code may help in the meantime:

// 2 fake questionnaire copies. You will want to fetch these from your database
$test = array("1,2,3", "1,1,1");

// Sum up the answers
foreach ($test as $copy) {
    $answer_array = explode(",", $copy);
    foreach ($answer_array as $question => $answer) {
        $sum[$question][$answer]++;
    }
}

// Display the result
foreach ($sum as $question => $answer_sum) {
    echo "Question {$question}\n";
    foreach ($answer_sum as $answer => $sum) {
        echo "\t{$answer} answered {$sum} times\n";
    }
}

Sorting questionnaire data using PHP and SQL

2 Answers2

Linked