HELP NEEDED!

[dapcigar]

Hello all, 

 

Am trying to get the answers of a survey from my DB and display the percentage.. Everything am trying seems not to be working. please what am i doing wrong.

<?
	$question_query = "SELECT * FROM ".$tblprefix."question WHERE category = 'Service Availability' and section = 'Information Technology Services'";
	$rs = $db->Execute($question_query);
		$all_the_time		=	array();
		$most_of_the_time	=	array();
		$sometimes			=	array();
		$never				=	array();	
	while (!$rs->EOF)
	{
		
		$question_id = $rs->fields['question_id'];
		
		$question_query1 = "SELECT * FROM ".$tblprefix."user_answer, ".$tblprefix."question WHERE ".$tblprefix."user_answer.question_id = ".$tblprefix."question.question_id";
		$rs1 = $db->Execute($question_query1);
		
		$answer_id = $rs1->fields['user_answer_id'];
		
		$question_query2 = "SELECT * FROM ".$tblprefix."answer WHERE answer_id = '$answer_id'";
		$rs2 = $db->Execute($question_query2);
		
		$answer = $rs2->fields['answer'];
		

		
		if($answer == 'All the time')
		{
			
			array_push($all_the_time,'1');
			
			
		}
		else if ($answer == 'Most of the time')
		{
			array_push($most_of_the_time,'1');
			
			
		}
		else if ($answer == 'Sometimes')
		{
			array_push($sometimes,'1');	
				
		}
		else if($answer == 'Never')
		{
			array_push($never,'1');
			
		}
		
		
		$rs->MoveNext();	
	}

	

		$count_all_of_time	=	count($all_the_time);
		$count_most_of_time=	count($most_of_the_time);
		$count_sometimes	=	count($sometimes);
		$count_never		=	count($never);




?>

<table border="0" class="question_answer_table">
    <tr>
    	<td><strong>Service Availability</strong></td>
    </tr>
</table>
<table border="0" class="question_answer_table">
    <tr>
    	<td style="width:120px;">All of Time</td>
        <td style="width:70px;"><?=round($count_all_of_time/$totaluser*100,2) .'%'?></td>
    </tr>
    <tr>
    	<td>Most of Time</td>
        <td><?=round($count_most_of_time/$totaluser*100,2) .'%'?></td>
    </tr>
    <tr>
    	<td>Sometimes</td>
        <td><?=round($count_sometimes/$totaluser*100,2) .'%'?></td>
    </tr>
    <tr>
    	<td>Never</td>
        <td><?=round($count_never/$totaluser*100,2) .'%'?></td>
    </tr>
     
</table>

Only one of the field is displaying an output. the rest are not.

 

 

thanks in advance

 

 

 

[mac_gyver]

Only one of the field is displaying an output. the rest are not.

 

 

care to share which one is working and what is wrong with output from the other ones, what are you getting, what do you expect, and what is the data in your database tables that the code is using to produce the output?

 

btw - all your code can probably be replaced with one query (you shouldn't be running query(ies) inside of loops), then simply loop over and display the result from that one query (if someone has time, they will post an example.)

[dapcigar]

else if ($answer == 'Sometimes')
		{
			array_push($sometimes,'1');	
				
		}

This the only one working. and the rest are not displaying the values. it was suppose to calculate the total number of people that answer a particular question and show the percentages..

[mac_gyver]

the problem is with the logic, you are not getting any sort of count/accumulation for each possible answer.

 

as mentioned, you have too many queries and too much code to go along with them. see the following single query  - 

$query = "SELECT category, answer, ROUND(COALESCE(100*SUM(NOT ISNULL(ua.id))/(SELECT COUNT(*) FROM user_answer WHERE question_id = q.question_id),0),2) AS ave
 FROM question q JOIN answer a ON 1=1
 LEFT JOIN user_answer ua ON q.question_id = ua.question_id AND a.answer_id = ua.answer_id
 WHERE q.category = 'Service Availability' AND q.section = 'Information Technology Services'
 GROUP BY q.question_id, a.answer_id";

run this query using a tool like phpmyadmin to see what it does, then run it using whatever database class/wrapper you are using in your php code.

 

you can simply loop over the result from this query to DYNAMICALLY produce all the information in your html, including the category heading Service Availability, each answer All of Time, Most of Time, ... label, and each result. you should not have these things hard-coded into your code, let the query get the values, in fact, the query will work for any number of categories simply by altering the WHERE clause in the query and it could be expanded to operate on any section(s) that the query matches as well.

[mac_gyver]

you will need to put your $tblprefix value back into the above query, but only in the three places where the full table names are used.