HELP w/ an array, thanks ahead of time!

[mikefrederick]

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;

		}}
              
            
              ?>

[mikefrederick]

maybe it needs to select from regions before candidates

[Psycho]

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";

}

?>

 

[mikefrederick]

Hey buddy- Thanks a lot, and good tip, I should learn that.

[mikefrederick]

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.

[mikefrederick]

nevermind