Survey help (PHP)

Hey Guys, Long time no posting for me.

I am going brain dead here.

This is whats going on

I have a survey on my website

then I have a MySQL tbl to add the results

the table is set out
FIELDNAME
q1
q2
q3
q4
......


Now, I am creating this so the results are automatic

What is the best way to do this.

I was going to do

$q1option1 = mysql_result(mysql_query("SELECT COUNT(*) FROM tbl WHERE q1 = 'option1' "),0);

$q1option2 = mysql_result(mysql_query("SELECT COUNT(*) FROM tbl WHERE q1 = 'option2' "),0);

But this seems a bit long winded, baring in mind, there are about 20 questions, and 4 options to most questions.

thats 80 Queries, ouch sorry Mysql.

Any ideas.

 

What are you trying to achieve in the results? An overall score, averages?

 

The results are not always numbers

I am using this code now

$queryq10c = "SELECT COUNT(q10c) AS quantity10c, q10c from surveys group by q10c order by q10c";

$resultq10c = mysql_query($queryq10c);

$options = array();
$quantity = array();

echo"</tr><tr>\n";



while ($rowq10c = mysql_fetch_assoc($resultq10c)){
$options[] = $rowq10c["q10c"];
$quantity[] = $rowq10c["quantity10c"];
}

At least it is only 1 query per question

 

why not add all of them in.

$queryq10c = "SELECT COUNT(q1) as quantityq1, q1, COUNT(q2) as quantityq2, q2, COUNT(q10c) AS quantity10c, q10c from surveys group by q10c order by q10c";

Code (markup):

 

cos each one is manipulated differently

 

I'll keep thinking, but right now I'm sure. :/

 

Having both a questions and answers table (separately) would maybe be better.