[RESOLVED] counting arrays

[matthewod]

I am running a survey where I have 5 questions. Each question has an answer of a, b, or c. I am storing the results in a mysql db. I need to output the results and getting a 'count' of how many times a, b or c occurs in each question. I have tried a few things like:

$sql2="SELECT * FROM dad_contest";
$result2 = @($sql2, $connection) or die(mysql_error());
$num_rows=mysql_num_rows($result2);
if (mysql_num_rows($result2) >0 ) {
while ($row2 = mysql_fetch_array($result2)) {

//q1
if($row2[q1] == "a") {
for ($a=0; $a <= $num_rows; $a++) {
$q1a = $a;
}

}
elseif ($row2[q1] == "b") {
for ($b=0; $b <= $num_rows; $b++) {
$q1b = $b;
}

}
else {
for ($c=0; $c <= $num_rows; $c++) {
$q1c = $c;
}

}

This could be my lack of understanding of for loops like a previous post I just did with a similar issue. This needs to be run for all 5 questions (q1, q2, q3, q4, q5). Any ideas and/or help is always appreciated. The solution may be more complicated than my experience.

Thanks in advance for any advice.

Matt

[NogDog]

I think perhaps what you want to do is (adjusting column names to actual names):

$sql = "SELECT `question`, `answer`, COUNT(*) AS `count` FROM `dad_contest` GROUP BY `question`, `answer`";
$result = mysql_query($sql) or die(mysql_error());
$answers = array();
while($row = mysql_fetch_assoc($result))
{
   $answers[$row['question']][$row['answer']] = $row['count'];
}
echo "<ul>\n";
foreach($answers as $question => $value)
{
   echo "<li>Question $question:</li>\n";
   echo "<ul>\n";
   foreach($value as $answer => $count)
   {
      echo "<li>$answer: $count</li>\n";
   }
   echo "</ul>\n";
}
echo "</ul>\n";

[matthewod]

Thanks for the reply, but my db table is not set up that way. Here is what I initially set up for my db table:

name | q1 | q2 | q3 | q4 | q5
Joe | a | b | a | c | b
Matt | b | b | c | c | a
Frank | a | a | a | c | c

I need to see the totals for each question:

Total Replies: 3
Question1 A: 2
Question1 B: 1
Question1 C: 0

Question 2 A: 1
Question 2 B: 2
Question 2 C: 0

Question 3 A: 2
Question 3 B: 0
Question 3 C: 1

Etc...

If there are any more ideas please pass them on. Thanks for the advice.

Matt

[cgraz]

You should normalize your database first. Writing a query to get the totals by question would then be pretty easy.

Create a second table to store your questions, and link the two together.

[bogu]

Something like this?

(SELECT
	q1, COUNT(q1) as nr_q1, 'q1' as `field`
FROM `table`
GROUP BY q1)
UNION
(SELECT
	q2, COUNT(q2) as nr_q2, 'q2' as `field`
FROM `table`
GROUP BY q2)
UNION
(SELECT
	q3, COUNT(q3) as nr_q3, 'q3' as `field`
FROM `table`
GROUP BY q3)
UNION
(SELECT
	q4, COUNT(q4) as nr_q4, 'q4' as `field`
FROM `table`
GROUP BY q4)
UNION
(SELECT
	q5, COUNT(q5) as nr_q5, 'q5' as `field`
FROM `table`
GROUP BY q5)

[matthewod]

I had the survey results in the same table as the users who posted them. So I changed the answers from a letter to a number and put each answer as a field in the table:
q1a | q1b | q1c etc.

Then I just used:

$q1a += $row2['q1a'];
$q1b += $row2['q1b'];
$q1c += $row2['q1c'];

to show the totals. This worked for me because then I would have to rewrite how everything worked into separate tables. This saved me some time to get the thing done.

Thanks for all the posts.

Matt