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!!!!!
Grouping and sorting results
Moderator: General Moderators
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: Grouping and sorting results
Not tested:
Or you might try joining both queries and using similar code:
-Shawn
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";
}
}Code: Select all
SELECT countries.country, cities.city FROM countries, cities WHERE countries.id=cities.countrymysql_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.