Page 1 of 1

Avoiding this loop, looking for alternate method

Posted: Mon Nov 08, 2004 6:29 pm
by josh
I am setting up a php script to allow users to signup and take a survey, at the end of the survey it will show the results after their score has been averaged in. My database structure is as follows:

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);
?>

Posted: Mon Nov 08, 2004 7:13 pm
by Slippy
If you are concerned with the number of queries you could do it with 1 query and structure your code around that.

AKA - "SELECT * FROM answers";

...and then loop through the result array.

Posted: Mon Nov 08, 2004 7:37 pm
by josh
Ok my results.php now looks like this:

Code: Select all

<?php
include_once('class.php');
include(HEADER);
if ($authed) {
	$countt=0;
	$arr=array();
	$result=mysql_query("SELECT * from `answers`; ") or die ('error!');
	$results=array();
	while ( list($answer, $ip, $timestamp, $usert, $id) = mysql_fetch_array($result)) {
		$results["$usert"]["$id"]=$answer;
	}
	echo "</center><pre>";
	print_r ($results);
	echo "</pre><center>";
}
include(FOOTER);
?>


And my 2d array now will look like:

Code: Select all

Array
(
    &#1111;user1] =&gt; Array
        (
            &#1111;question1] =&gt; answer
            &#1111;question2] =&gt; answer

        )
    &#1111;user2] =&gt; Array
        (
            &#1111;question1] =&gt; answer
            &#1111;question2] =&gt; answer

        )


)
How can I check the occurances of an item in an array? Like is there a function similar to in_array() but returns an integer of the amount of occurances instead of a boolean if theres an occurance

I want my final results to look like this:

Question one
--Answer one: 10% of users picked this
--Answer two: 90% of users picked this
Question two
--Answer one: 5% of users picked this
--Answer two: 95% of users picked this


etc... So I am guessing I will need to either find a function to count the occurances of answer X foreach user as $user $results['$user']['X'];

something similar to that?

could someone just point me in the right direction because I haven't done anything like this before

Posted: Mon Nov 08, 2004 7:55 pm
by Slippy
Sorry I'm being lazy about syntax, but try this algo:

Code: Select all

1. Get a list of all answers and store it in an array
2. Dimension a new array with one increment for each answer (I think you can use your 'id' field to provide uniqueness.)
3. Loop through the answer array and check for each 'id' match... everytime there is a match you can increment a total for the responses in the "new" array.
4. When you are done you end up with a populated "new" array that has counts of the responses for each answer.
This get's a bit more complicated when you have more than one question; but it should just be a minor mod one you figure out how to do your looping.

As for your question about occurances in an array, it might be that I am lazy or that I just don't understand.

Maybe this helps, maybe not... hope it does.

Posted: Mon Nov 08, 2004 8:01 pm
by josh
sweet....


thanks for clearin it up!

Posted: Mon Nov 08, 2004 9:42 pm
by Slippy
No worries, hope it works out for you. 8)

Posted: Tue Nov 09, 2004 6:50 pm
by josh

Code: Select all

<?php
	foreach ($results as $key => $value) {
		echo "<b>Question $key</b><BR>";
		$array=array();
		$array=(array_count_values($value));
		foreach ($array as $key => $value) {
			$key=str_replace("%dash%", "-", $key);
			echo "$key = $value" . "<BR>";
		}
	}
?>
Didn't know there was a function to count the instances of items in arrays... very cool

Posted: Tue Nov 09, 2004 7:25 pm
by John Cartwright
weird .. I posted reference to that in this thread... but it disapeered 8O

Posted: Tue Nov 09, 2004 7:32 pm
by josh
hmmm... too bad if i had seen it earlier before it dissapeared it would have saved me like an hour of research lol