PHP/MySql Problem, looping in second set of results

flipmoe · February 14, 2007

Hi, I've been digging around on this discussion board as well as searching all over the internet, and I haven't been able to find an example of what I'm trying to do.

I have two mysql tables Problem and Answer.

Problem table has 3 fields:

ID category problem

Answer table has 5 fields:

ID answer year week usage

In the problem table the ID is unique, and correlates to an ID in the Answer table, but there are many IDs that will have the same category and problem. The first query I run aggregates the category and does a SUM on the usage for that week. Then I need a second query to go in and say to display an aggregate of that category's problem and the usage SUM by problem. MY Sql runs and the script runs on it's own, but I need to loop it through multiple categories instead of one at a time. Here's the php:

<? //all sql connection and database selection is taken care of above the <head>

$sql1="SELECT problem.category as problem, SUM(answer.usage)

FROM `answer`,`problem`

WHERE answer.ID = problem.ID

AND answer.week ='week 17'

GROUP BY problem ORDER BY SUM(answer.usage)";

$result1 = mysql_query($sql2) or die(mysql_error());

while($thisrow = mysql_fetch_array($result1)){

$category=mysql_fetch_array($result1, 'problem'); //I think this is wrong too

?>

</tr></table>

<table>

<? // here's where I need to figure out the second while statement

$sql2="SELECT problem.problem, SUM(answer.usage)

FROM problem, answer

WHERE problem.ID = answer.ID

AND problem.category = '$category'

AND answer.week ='week 17'

GROUP BY problem.problem

ORDER BY SUM(answer.usage) DESC";

$result2 = mysql_query ($sql2) or die("Nope, something's wrong here");

while ( $thisrow = mysql_fetch_assoc($result)){

?>

<? }//end inner while ?>

<? } //end first while ?>

</table>

</div>

I want the final result to look like this:

Problem 1 600

Answer 1 300

Answer 2 150

Answer 3 100

Answer 4 50

Problem 2 500

Answer 5 200

Answer 6 150

Answer 7 150

I found one example where everything is echoed, but I can't use that as each table is also collapsable with Javascript (not in the example).

I hope that I phrased the question correctly, Thanks for any help that anyone can provide!!!

hitman6003 · February 15, 2007

You are using the same name for the temporary variable in your while loops...

<?php
$sql1="SELECT problem.category as problem, SUM(answer.usage) " .
      "FROM `answer`,`problem` " .
      "WHERE answer.ID = problem.ID AND answer.week ='week 17' " .
      "GROUP BY problem ORDER BY SUM(answer.usage)";

$result1 = mysql_query($sql2) or die(mysql_error());

while ($thisrow = mysql_fetch_array($result1)) {
$category=mysql_fetch_array($result1, 'problem');

echo '
	<table>
		<tr>
			<th>' . $thisrow['problem'] . '</th>
			<td>' . $thisrow['SUM(answer.usage)'] . '</td>
		</tr>';

$sql2="SELECT problem.problem, SUM(answer.usage) " .
	  "FROM problem, answer " .
	  "WHERE problem.ID = answer.ID " .
	  "AND problem.category = '$category' " .
	  "AND answer.week ='week 17' " .
	  "GROUP BY problem.problem " .
	  "ORDER BY SUM(answer.usage) DESC";

$result2 = mysql_query($sql2) or die(mysql_error());

while ($thisrow2 = mysql_fetch_assoc($result)){
	echo '
		<tr>
			<td width="450px">' . $thisrow2['problem'] . '</td>
			<td width="50px">' . $thisrow2['SUM(answer.usage)'] . '</td>
		</tr>';
}

echo '
	</table>';
}
?>

Sign In

PHP/MySql Problem, looping in second set of results

Recommended Posts

flipmoe

Link to comment

Share on other sites

hitman6003

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information