嵌套循环和SQL查询; 需要速度

Question

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)

Answer 1

it would be much faster to do joins in the sql

then iterate over the single (larger) result set.