Nested loops and SQL queries; need for speed

I'm having trouble solving a problem with iterative SQL queries (which I need to do away with) and I'm trying to work out an alternative.

(Also; unfortunately, AJAX is not really suitable)

Given I have the following tables for location data:

Country
    country_id
    name

State
    state_id
    country_id
    name

City
    city_id
    state_id
    name

Now, I'm trying to pull all of the data, however it's actually quite tiny (147 cities, split between 64 states, split between 2 countries) however it's taking forever because I'm iteratively looping:

// this is pseudo-code, but it gets the point across

$countries = getCountries();
foreach($countries as &$country){
    $country['states'] = $states = getStates($country['country_id']);
    foreach($states as &$state){
        $state['cities'] = getCities($state['state_id']);
    }
}

The reason I'm going this way, is because my final result set needs to be in the form:

$countries = array(
    array(
        'name' => 'country_name',
        'id' => 'country_id',
        'states' => array(
            array(
                'name' => 'state_name',
                'id' => 'state_id',
                'cities' => array(
                    array(
                    开发者_运维技巧    'name' => 'city_name',
                        'id' => 'city_id',
                    ),
                    // ... more cities
                ),
            ),
            // ... more states
        ),
    ),
    // ... more countries
);

I can't seem to wrap my head around a faster approach. What alternatives exist to querying for hierarchical data?

---

Revised:

    $sql = "SELECT
                `dbc_country`.`name` as `country_name`,
                `dbc_state`.`name` as `state_name`,
                `city_id`,
                `dbc_city`.`name` as `city_name`,
                `latitude`,
                `longitude`
            FROM
                `dbc_city`
                    INNER JOIN
                `dbc_state` ON `dbc_city`.`state_id` = `dbc_state`.`state_id`
                    INNER JOIN
                `dbc_country` ON `dbc_state`.`country_id` = `dbc_country`.`country_id`";
    $locations = array();
    foreach($datasource->fetchSet($sql) as $row){
        $locations[$row['country_name']][$row['state_name']][] = array(
            $row['city_id'],
            $row['city_name'],
            $row['latitude'],
            $row['longitude'],
        );
    }

(I also removed the id values of states/countries, since they were uselessly taking up space)

---

it would be much faster to do joins in the sql

then iterate over the single (larger) result set.

---

I would either use one query:

SELECT co.name AS CountryName
     , st.name AS StateName
     , ci.name AS CityName
FROM Country AS co
  LEFT JOIN State AS st
    ON st.country_id = co.country_id
  LEFT JOIN City AS ci
    ON ci.state_id = st.state_id
ORDER BY CountryName
       , StateName
       , CityName

or three (if you have lots of records and you are worried of sending "United States of America" hundreds of thousands of times over the connection from MySQL to application code):

--- **GetCountries**
SELECT co.country_id
     , co.name AS CountryName
FROM Country AS co
ORDER BY CountryName

--- **GetStates**
SELECT co.country_id
     , st.state_id
     , st.name AS StateName
FROM Country AS co
  JOIN State AS st
    ON st.country_id = co.country_id
ORDER BY CountryName
       , StateName

--- **GetCities**
SELECT co.country_id
     , st.state_id
     , ci.city_id
     , ci.name AS CityName
FROM Country AS co
  JOIN State AS st
    ON st.country_id = co.country_id
  JOIN City AS ci
    ON ci.state_id = st.state_id
ORDER BY CountryName
       , StateName
       , CityName

---

The common approach to database design emphasizes doing as much work as possible, with as few queries as possible. Its look right. But quoting this thread title, “Query Efficiency”, that approach doesn’t apply as much to MySQL. FYI, MySQL was designed to handle connecting and disconnecting very efficiently and to respond to small and simple queries very quickly, so as long you immediate freeing memmory on your sequenced queries, i think its okay. Furthermore, if your record growing (into 100000 records for example), then maybe you will think twice to use JOIN statement.

---

What if your data looked like this instead?

Table: country 
iso_country_code        country_name
--
CA                      Canada
US                      United States of America

Table: state
iso_country_code    state_abbr    state_name
--
US                  NE            Nebraska
CA                  NB            New Brunswick

Table: city
iso_country_code    state_abbr    city_name
--
US                  NE            Lincoln
US                  NE            Arapahoe
CA                  NB            Dalhousie
CA                  NB            Miramichi

Would you be able to use the codes and abbreviations instead of the full names?

Even if you can't, you can get all the necessary rows with a single SELECT statement, then walk the rows to populate your array. (You can do that with ID numbers, too, but with ID numbers, you always have to do the joins. With codes and abbreviations, you can often satisfy your users with just the code or abbreviation.)