Grouping cities, states and countries in MYSQL PHP

I am new to PHP and have visited several sites for this but 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 - C开发者_如何学编程ity 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 I can further group the states and cities respectively as sought above?

SQL queries will give you a set of results with a fixed number of columns. Your example has a variable number of columns (multiple city counts) so this won't work.

You can use COUNT and GROUP BY in the SQL to get something like

Country   State    Citiescount
Canada    state1      4
Canada    state2      3
USA       state2      2

etc, but you will have duplicates in the left hand column(s).

  SELECT country, state, COUNT(cities)
    FROM address_base
GROUP BY country, state

To get rid of the duplicates, loop over it to make an multidimensional array doing something like

$countries = array();
foreach ($results as $row) {
    if (!isset($countries[$row->country])) {
        $countries[$row->country] = array();
    }
    $countries[$row->country][$row->state] = $row->citiescount;
}

Alternatively, you can just echo the stuff instead. You can either get all the results in one go like this and loop over the whole set, or you can break it into smaller queries and do one per country or one per state. Be warned, however, that putting SQL in loops kills kittens (and DB performance) :).

$result = mysql_query('SELECT Country, State, City From Address_Base ORDER BY Country, State, City') or die(mysql_error());

$countries = array();

// fetch results and build multidimensional array of city counts
while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
    if(isset($countries[$row['Country']][$row['State']][$row['City']])){
        $countries[$row['Country']][$row['State']][$row['City']] += 1;
    } else{
        $countries[$row['Country']][$row['State']][$row['City']] = 1;
    }
}

// loop over all countries
foreach($countries as $country => $states){
    echo $country, PHP_EOL, '{'; // output country start

    // loop over all states
    foreach($states as $state => $cities){
        echo $state, ' - '; // output state start

        $cityCounts = array();
        foreach($cities as $city => $count){
            $cityCounts[] = $city.' ('.$count.')'; // add all city counts to array
        }

        // implode all city counts and output it
        echo implode(', ', $cityCounts);

        // output new line
        echo PHP_EOL;
    }

    // output country end
    echo PHP_EOL, '}';
}