Grouping and sorting results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bcart
Forum Newbie
Posts: 10
Joined: Fri Oct 30, 2009 8:09 am

Grouping and sorting results

Post 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!!!!!
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

Re: Grouping and sorting results

Post 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
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
Post Reply