在MYSQL PHP中对城市，州和国家进行分组

Question

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 - 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 I can further group the states and cities respectively as sought above?

Answer 1

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) :).