Everytime a user answers a question on the survey I insert a new record into `answers` which stores the timestamp for when they answered it, their ip adress, username, question number that they answered, and the answer to the question....
Ok, now It's time to call back the results, I decided I want to be able to show each question and show what percentage of users choose each option, options where no one choose it will not be on the results page, so I wrote this script which returns a 2 dimensional array, which is all good except the way I wrote it uses too many mysql queries.
It will use N queries where N = [number_of_possible_answers] * [number_of_questions]
So, with only a few users and maybe like 10 questions with no more then 3 or for possible answers for each question it will only query the database 10-20 times or so, but when I conduct a full scale survey with hundreds of users, every time some one views the results it will put a heavy load on the database, besides.. im on a shared host and they only give us a certain amount of queries per hour (like 5,000)
So my question is: is there a more efficient way to do this?
Heres my source
surveyresults.php
Code: Select all
<?php
include_once('class.php');
include(HEADER);
if ($authed) {
$countt=0;
$arr=array();
//This line gets all the question numbers
$result=mysql_query("SELECT `id` from `answers`; ") or die ('error!');
$countt++;
while ( list($count) = mysql_fetch_array($result)) {
//This line gets the answers for the questions
$r=mysql_query("select count(`answer`), `answer` FROM `answers` WHERE `id` = '$count' group by `answer`; ") or die(mysql_error());
$countt++;
while ( list($value, $v) = mysql_fetch_array($r)) {
$value=str_replace("%dash%", "-", $value);
$v=str_replace("%dash%", "-", $v);
$arr[$count][$v] = $value;
}
}
echo '</center><pre>';
print_r ($arr);
echo "</pre>Script used $countt calls to the database<center>";
}
include(FOOTER);
?>