[RESOLVED] Building an Array From a Table of Answers

[whobutsb]

I have a SQL Database with customer responses from a PHP survey I setup. One of the questions that is asked is Rate the Quality of Food. The answers can only be from 1-4 (4 being the highest), this answer can't be skipped so there are no null values.

As of Right now I'm building querying the database like this :

$query_qualityFood_4 = "SELECT food_quality FROM surveyData WHERE food_quality = '4'";
$qualityFood_4 = mysql_query($query_qualityFood_4, $results) or die(mysql_error());
$row_qualityFood_4 = mysql_fetch_assoc($qualityFood_4);
$totalRows_qualityFood_4 = mysql_num_rows($qualityFood_4);

This gets tedious because I'am only getting the number of results for answer that equals 4, and I still need to get 3,2,1. Is there any quicker way to get the total number of respondants with one SQL query and put it into an array?

Thanks for the help

[jernhenrik]

Not sure if this is what you are after. Take a look at the code below and see if is of any use to you.

$query = "SELECT food_quality, COUNT(food_quality) as cnt FROM surveyData GROUP BY food_quality"; //query to group by the different foo_qualities
$result = mysql_query($query) or die(mysql_error()); //executing the query
while ($row = mysql_fetch_array($result)) {//extracting result one row at a time
   echo 'Quality: ' . $row['food_quality'] . ' has ' . $row['cnt'] . ' entries<br/>';//echoing result
   resArr[] = $row['cnt'];//putting the four results into an array for later fnu
}

hth

[whobutsb]

That looks about right. That code gets the counts of the different ratings and puts them into an array. How do I output each rating from the array? Before I was just using

$totalRows_qualityFood_4

which will give me the total number of people who voted for 4.

[jernhenrik]

http://php.net/foreach

foreach is a simple way of getting to all variables in your array.

e.g.

foreach ($resArr as $value) {
  echo $value;
}

or you can simply change the way it is put into the array to:

resArr[$row['food_quality']] = $row['cnt'];

and then access them as:

echo resArr[1];//being the number counted for food_quality 1
echo resArr[2];//being the number counted for food_quality 2
echo resArr[3];//being the number counted for food_quality 3
echo resArr[4];//being the number counted for food_quality 4

hth

[whobutsb]

I'm sorry for being such a bother. But I put this in to my code

$query = "SELECT food_quality, COUNT(food_quality) as cnt FROM surveyData GROUP BY food_quality"; //query to group by the different foo_qualities
$result = mysql_query($query) or die(mysql_error()); //executing the query
while ($row = mysql_fetch_array($result)) {//extracting result one row at a time
   echo 'Quality: ' . $row['food_quality'] . ' has ' . $row['cnt'] . ' entries<br/>';//echoing result
   foreach {
   resArr[$row['food_quality']] = $row['cnt']; //putting the four results into an array for later fun
   }
} 

And its giving me an error near the foreach statement and the resArr.

Could you explain what resArr does? Also I'am assuming cnt stands for "count". If I wanted to query other things like service_quality which is tabulated the same way what values would i need to change?

[jernhenrik]

resArr is just an array to hold your results.

The foreach loop is for extracting data not inserting it.

Try this:

$query = "SELECT food_quality, COUNT(food_quality) as cnt FROM surveyData GROUP BY food_quality"; //query to group by the different foo_qualities 
$result = mysql_query($query) or die(mysql_error()); //executing the query 
while ($row = mysql_fetch_array($result)) {//extracting result one row at a time 
   $resArr[$row['food_quality']] = $row['cnt']; 
} 

foreach ($resArr as $key => $value) {
  echo $key . ' => ' . $value . '<br/>';
  //$key representing the quality of the food, and $value representing the number of occurences of a given quality
}

If you wish to do other extracts like for service quality, you can just substitute food_quality for service quality in the $query variable:
e.g.

$query = "SELECT service_quality, COUNT(service_quality) as cnt FROM surveyData GROUP BY service_quality";

hth

[whobutsb]

Dude you are the man! Thanks alot for your patience and help. Here is what I got:
www.newportharbor.com/results/samplechart.php

Thanks!