Group cities states and countries in PHP Mysql

PHP
1

hi!
I am new to php…and i have visited several sites for this but i can’t seem to understand thoroughly…

I have this query…

$result=mysql_query(“select Country, State, City From Address_Base order by Country, State, City”) or die(mysql_error());

—this gives the following data sample:
Canada - State 1 - City Apple

Canada - State 2 - City Grapes

Canada - State 1 - City Apple

Canada - State 2 - City Coffee

USA - State 1 - City Zebra

USA - State 2 - City Cat

USA - State 2 - City Lion

USA - State 3 - City Bird

USA - State 1 - City Zebra

My goal is to group the cities under the respective state and count city, group the states under the respective country & group similar countries and produce something like this

Canada -

{

State 1 - City Apple (2)

State 2 - City Coffee (1), City Grapes (1)

}

USA -
{

State 1 - City Zebra (2) 

State 2 - City Cat (1),   City Lion (1) 

State 3 - City Bird (1)

}
…
From some other site, i got this:
…

    $combinedResults = array();
		
while ($rec=mysql_fetch_assoc($result))   {
          $combinedResults[$rec['Country']][] =  array( 				
                   'S' => $rec['State'], 
                   'C' => $rec['City']                  );					         
                                               }
													
            foreach(array_keys($combinedResults) as $Country) { 
                   echo '<div class="country">'.$Country.'</div>';

	       foreach($combinedResults[$Country] as $Set)   { 
                        echo $Set['S'].'<br/>'.$Set['C'].'<br/><br/>'; 
							                } 
						                   
                                                               }

…
This only groups similar Countries and not states and the cities…i guess the above piece of code is trying to pre-process the data in some sort of multi-dimensional array and have a for loop display the results…i am not able to understand it clearly…i would be very thankful if someone could explain it for me and how do i further group the states and cities respectively as sought above?

thanks in advance…

2

This is best sorted out in your PHP logic.


$rows = mysql_fetch_assoc($result);

$last_country =''; // set a starting value

foreach( $rows as $row){
if($row['Country'] !== $last_country){
echo '<b>'. $row['Country'] .'</b><br />'. PHP_EOL ;
$last_country = $row['Country'];  // if it is a new one, overwrite the value
}
echo '-' . $row['State'] . ' - ' . $row['City'] .' <br />'. PHP_EOL ; // do this anyway
}

untested

That echos to screen, but could populate another multidimensional array. I you are not sure, holler.