Page 1 of 1

Grouping and sorting results

Posted: Wed Nov 04, 2009 9:05 am
by bcart
Please help with this query.

I have 2 tables in a database, countires and cities. states looks like this

countries

id country
1 uk
2 us
3 japan

cities
id country city
1 1 London
2 1 Manchester
3 2 New York
4 2 Washington
5 2 San Francisco
6 2 Las Vegas
7 2 New Orleans
8 3 Tokyo


I have queried the database as follows:

///////////////////////// Make the country query //////////////////////////////
$q_country = "SELECT * FROM countries";
$result_country = mysqli_query($dbc, $q_country);
$row_country = mysqli_fetch_array($result_country);

///////////////////////// Make the city query //////////////////////////////
$q_city = "SELECT * FROM cities";
$result_city = mysqli_query($dbc, $q_city);
$row_name = mysqli_fetch_array($result_name);

I want to display the data as follows:

Country - UK
Cities - London, Manchester

Country - US
Cities - New York, Washinton, San Francisco, Las Vegas, New Orleans

Country - Japan
Cities - Tokyo

I'm new to PHP and just can't work it out. Please can somebody point me in the right direction?!!!! PLEASE!!!!!

Re: Grouping and sorting results

Posted: Wed Nov 04, 2009 1:00 pm
by AbraCadaver
Not tested:

Code: Select all

foreach($row_country as $country) {
    foreach($row_city as $city) {
        if($city['country'] == $country['id']) {
            $city_list[] = $city['city'];
        }
    }
    if(is_array($city_list)) {
        echo 'Country - ' . $country['country'] . "\n";
        echo 'Cities - ' . implode(', ', $city_list) . "\n\n";
    }
}
Or you might try joining both queries and using similar code:

Code: Select all

SELECT countries.country, cities.city FROM countries, cities WHERE countries.id=cities.country
-Shawn