flipmoe Posted February 14, 2007 Share Posted 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 ?> <!-- here's where the first row is displayed //--> <table><tr> <th ><?=$thisrow['problem']?></a></th><td ><?=$thisrow['SUM(answer.usage)']?></td> </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)){ ?> <tr> ><td width="450px"><?=$thisrow['problem']?></td> <td width="50px"><?=$thisrow['SUM(answer.usage)']?></td></tr> <? }//end inner while ?> <? } //end first while ?> </table><!--/data--> </div> <!--/content1--> 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!!! Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted February 15, 2007 Share Posted 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>'; } ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.