mikefrederick Posted February 22, 2008 Share Posted February 22, 2008 Thanks for any help....For each candidate field 'name' from table 'candidates', I need to go through every 'region' by 'id' and select from table 'newvotes' and record the number of times that the 'answerid' (equal to the 'id' field from table 'candidates') occurs for the region. At the end of going through each region, I need to record which answerid showed up the most times. At the end, I need to record the total number of times each candidateid showed up the most for a region (this shows the total number of regions that the candidate won) and be able to echo it in a <td> . I almost had it working, but the way I was doing it was not efficient and so I am starting over and asking for help. Thanks ahead of time (the code below at least helps with the setup of the while loops and the tables)! <? $l=mysql_query("select * from candidates order by name"); while($i=mysql_fetch_assoc($l)) { $d=mysql_query("select * from regions order by id"); while($s=mysql_fetch_assoc($d)) { $u=mysql_query("select * from newvotes where answerid='$i[id]' and regionid='$s[id]'"); $o=mysql_fetch_assoc($u); $number="number:" . mysql_num_rows($u) . $i['id'] . "---region:" . $s['id']; echo $number; }} ?> Quote Link to comment Share on other sites More sharing options...
mikefrederick Posted February 22, 2008 Author Share Posted February 22, 2008 maybe it needs to select from regions before candidates Quote Link to comment Share on other sites More sharing options...
Psycho Posted February 22, 2008 Share Posted February 22, 2008 You need to learn how to do JOINS in your queries. NEVER do looping queries - it is terribly inefficient and a resouorce hog. Also you will do yourself a great favor by using descriptive variable names. This "should" get what you want. I can't test it becuause I don't have your table structures, but the logic is sound. <?php //Only 1 query needed $query = "SELECT COUNT(*) as votecount, candidates.id as c_id, candidates.name as c_name, regions.id as r_id, regions.name as r_name FROM newvotes JOIN candidates ON newvotes.answerid = candidates.id JOIN regions ON newvotes.regionid = regions.id GROUP BY candidates.id, regions.id ORDER BY candidates.name"; //Run the query $result = mysql_query($query) or die(mysql_error()); //Display the results $current_candidate = ''; while ($record = mysql_fetch_assoc($result)) { if ($current_candidate!=$record['c_name']) { $current_candidate==$record['c_name']; echo "<b>$current_candidate (".$record['c_id'].")</b>\n"; } echo " --- Region: " . $record['r_name'] . "(".$record['r_id']."), "; echo "Count: " . $record['votecount'] . "<br>\n"; } ?> Quote Link to comment Share on other sites More sharing options...
mikefrederick Posted February 22, 2008 Author Share Posted February 22, 2008 Hey buddy- Thanks a lot, and good tip, I should learn that. Quote Link to comment Share on other sites More sharing options...
mikefrederick Posted February 22, 2008 Author Share Posted February 22, 2008 Hey can you tell me how that grabs information related to regions? I see regions in the query but after that it does not look like it has anything to do with regions, and I tried to duplicate the code for another table called departments and it still shows the same info it does for regions. Quote Link to comment Share on other sites More sharing options...
mikefrederick Posted February 22, 2008 Author Share Posted February 22, 2008 nevermind 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.