Avoiding this loop, looking for alternate method

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Avoiding this loop, looking for alternate method

Post 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);
?>
User avatar
Slippy
Forum Contributor
Posts: 113
Joined: Sat Jul 12, 2003 11:31 pm
Location: Vancouver eh!

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
User avatar
Slippy
Forum Contributor
Posts: 113
Joined: Sat Jul 12, 2003 11:31 pm
Location: Vancouver eh!

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

sweet....


thanks for clearin it up!
User avatar
Slippy
Forum Contributor
Posts: 113
Joined: Sat Jul 12, 2003 11:31 pm
Location: Vancouver eh!

Post by Slippy »

No worries, hope it works out for you. 8)
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post by John Cartwright »

weird .. I posted reference to that in this thread... but it disapeered 8O
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post 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
Post Reply