Styjun

My boss wants to show the most popular states that people request franchising data from. I'm working with zipcodes that users put in. I run it through a function to find the state, but the problem is I have a count for each zip code not a total for the state itself. I need to combine the totals for each zip code(into one total for the state).

I'm able to add all these up by putting a counter in the loop but it does not break for each state. So I guess the solution would be a way break the loop when the state changes? so currently the output is as such:

New York - 8
New York - 11
New York -236
New York - 7
California - 2
Alabama - 7

I need to add all of new york into one category.

<?php
include("databaseClass.php");
$databaseClass = new databaseClass();
$conn = $databaseClass->connect();
$countFranArr = array();
$running = 0;


$sqlFranCount = "select client_zip, count(*) as total from request_form_commits inner join franchise on franchise.franchise_id = request_form_commits.franchise_id 
 where time_of_request >= '2011-03-20 18:01:04' and time_of_request <= '2033-03-20 21:03:22' group by client_zip";
 $resultFranCount = $conn->query($sqlFranCount);
 if($resultFranCount->num_rows > 0)
 
 while($rowFranCount = $resultFranCount->fetch_assoc())
 
 //array_push_assoc($countCatArr, $rowCatCount['fdd_category'], $rowCatCount['total']);
 //echo $rowCatCount['total']."<br>";
 $countFranArr[$rowFranCount['client_zip']] = $rowFranCount['total'];
 
 


 //arsort($countFranArr);

 foreach($countFranArr as $zip=>$total)
 
 //echo $databaseClass->getState($zip)." - ".$total."<br>";
 $state = $databaseClass->getState($zip);
 if($state == "")
 
 echo "";
 
 else
 echo $state." - ".$total."<br>";
 $running = $running + $total;
 

 
echo $running;


?>

Expected results will be

New York 267
California - 3
etc...

One total count for each state so I can order them by most popular

There are several ways you can do it in your first loop, here's one:

while($rowFranCount = $resultFranCount->fetch_assoc()) 
 $state = $databaseClass->getState($rowFranCount['client_zip']);

 if(!isset($result[$state])) 
 $result[$state] = 0;
 
 $result[$state] += $rowFranCount['total'];

Then loop the result to display:

foreach($result as $state => $total) 
 echo "$state - $total<br>";