Coming up with a query for this...

[pocobueno1388]

Hello =D I have a project where I have to conduct a survey. I made the survey on my website and all the answers for each person to each question are stored in the database.

 

My database is set up like this.

 

q1

q2

q3

...all the way to q15 since there are 15 questions on the survey.

 

Under each q1, q2, etc. there is an answer choice of either a,b,c, or d. So here is some examples rows/cols.

 

<b>q1</b> | <b>q2</b>

d |  a

 

So far I have it where it takes the first question and gets the percentage of what people chose for each answer on the survey.

 

EX.

For question 1

46% chose answer "a"

53% chose answer "b"

 

I want to shorten this code to get ALL the percentages in one go without having to make almost identical code 15 times to cover every question. Here is the code I currently have which ONLY covers question 1.

 

<?php

//create array of possible answers             
$answers = array ('.','a','b','c','d');

//Get how many people took the survey
$get_amount = mysql_query("SELECT q1 FROM survey WHERE voteID > 0");
$num = mysql_num_rows($get_amount);

echo "<h3>Question 1</h3>";

for ($i=1; $i<5; $i++){  
   
   //Select all possible answers for question 1                                        
   $sql = mysql_query("SELECT q1 FROM survey WHERE q1 = '$answers[$i]'");
   $num1 = mysql_num_rows($sql);
   
      //If num1 is <= 0 skip it, because there were not as many answer choices for this question
      if ($num1 <= 0){
         continue;
      }
   
   //Find the percentage people voted for each answer choice
   $percent = $num1/$num;
   $percent = substr($percent, 2 , -10);
   
   //Display the answer choice and the percent of people who chose that answer
   echo "<b>$answers[$i]</b> - $percent%<p>";
}

?>

 

For the questions I was trying to put them in an array and try to make a for loop inside of the current for loop to try to tackle everything, but I failed.

 

array ('q1','q2','q3','q4','q5','q6','q7','q8','q9','q10','q11','q12','q13','q14','q15');

 

Hopefully I explained that well enough. I appreciate all your help ^__^

 

[trq]

You really ought to read up on some database normalization techniques. Having the fields q1,q2,q3... etc etc is just ridiculous.

[pocobueno1388]

Oh wow! I just thought of a much better way to set up the database....sorry about that.

 

I could have just had two fields:

question

answer

 

...wow, talk about major brain fart. I guess it is a little too late now though =/

[pocobueno1388]

Hmmm, so can anyone think of a way to do this around my stupid database setup?

[fenway]

I'd recommend simply populating a new table and fixing this before it gets worse.

[pocobueno1388]

Okay, I will do that.