Multiple queries to MySQL

[at0mic]

I have a table of answers to 19 Yes/No questions which people have answered.

 

I am querying each column twice; once to see how many people said 'Yes' to that question, and again to see how many people said 'no' to that question. This means a total of 38 queries.

 

Is this the right way to do it or is there a better way? Like just one BIG query?

 

Here's the code I used to query the first two questions (I left the rest out to keep my post smaller)

 

$q_count = ("SELECT count(intray_id) FROM survey1_results WHERE r1_answer = 'Yes' AND intray_id = ".$HTTP_GET_VARS['id']); 
$r_count = mysql_query($q_count);
if ($r_count)
{
while ( $a = mysql_fetch_row($r_count) )
{
	echo "<tr><td> ".$qrow['q2']." </td><td>" .$a[0] ." People</td>";
}	
}


$q_count = ("SELECT count(intray_id) FROM survey1_results WHERE r1_answer = 'No' AND intray_id = ".$HTTP_GET_VARS['id']); 
$r_count = mysql_query($q_count);
if ($r_count)
{
while ( $a = mysql_fetch_row($r_count) )
{
	echo "<td>" .$a[0] ." People</td></tr>";
}	
}


$q_count = ("SELECT count(intray_id) FROM survey1_results WHERE r2_answer = 'Yes' AND intray_id = ".$HTTP_GET_VARS['id']); 
$r_count = mysql_query($q_count);
if ($r_count)
{
while ( $a = mysql_fetch_row($r_count) )
{
	echo "<tr><td> ".$qrow['q2']." </td><td>" .$a[0] ." People</td>";
}	
}


$q_count = ("SELECT count(intray_id) FROM survey1_results WHERE r2_answer = 'No' AND intray_id = ".$HTTP_GET_VARS['id']); 
$r_count = mysql_query($q_count);
if ($r_count)
{
while ( $a = mysql_fetch_row($r_count) )
{
	echo "<td>" .$a[0] ." People</td></tr>";
}	
}

[at0mic]

nobody can help?

[Barand]

The "right way to do it" would have been a table with 19 rows, each with one question instead of a single row with 19.

 

In this case the sql is going to be pretty horrendous with lots of repetition.

 

I'd consider retrieving all the columns in a single query then processing them as an array in php.