Grouping and sorting results

[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!!!!!

[JonnoTheDev]

You should first build an array from your query then loop through it. You only require one query.

<?php
$result = mysql_query("SELECT co.country, c.city FROM countries co INNER JOIN cities c ON(c.country = co.id) ORDER BY co.country, c.city ASC");
$places = array();
while($row = mysql_fetch_assoc($result)) {
$places[$row['country']][] = $row['city'];
}

foreach($places as $country => $cities) {
print "<h2>".$country."</h2>";
print "<p>".implode(", ",$cities)."</p>";
}
?>

[bcart]

You should first build an array from your query then loop through it. You only require one query.

<?php
$result = mysql_query("SELECT co.country, c.city FROM countries co INNER JOIN cities c ON(c.country = co.id) ORDER BY co.country, c.city ASC");
$places = array();
while($row = mysql_fetch_assoc($result)) {
$places[$row['country']][] = $row['city'];
}

foreach($places as $country => $cities) {
print "<h2>".$country."</h2>";
print "<p>".implode(", ",$cities)."</p>";
}
?>

 

Worked like a charm. Thanks very much. What if i wanted to display every country but limit the number of cities to be listed against that specific country to a maximum of 2

[JonnoTheDev]

Just print the first 2 of the array

<?php
$result = mysql_query("SELECT co.country, c.city FROM countries co INNER JOIN cities c ON(c.country = co.id) ORDER BY co.country, c.city ASC");
$places = array();
while($row = mysql_fetch_assoc($result)) {
$places[$row['country']][] = $row['city'];
}

foreach($places as $country => $cities) {
print "<h2>".$country."</h2>";
print "<p>".$cities[0].", ".$cities[1]."</p>";
}
?>

[bcart]

Just print the first 2 of the array

<?php
$result = mysql_query("SELECT co.country, c.city FROM countries co INNER JOIN cities c ON(c.country = co.id) ORDER BY co.country, c.city ASC");
$places = array();
while($row = mysql_fetch_assoc($result)) {
$places[$row['country']][] = $row['city'];
}

foreach($places as $country => $cities) {
print "<h2>".$country."</h2>";
print "<p>".$cities[0].", ".$cities[1]."</p>";
}
?>

Thanks again. I just have one more question. What if I now wanted to bring in a third table - suburbs?

 

so in the output I would want to display the following

 

Country 1

City 1 - Suburb 1, Suburb 2

City 2 - Suburb 1

City 3 - Suburb 1, Suburb 2, Suburb 3

 

Country 2

City 1 - Suburb 1, Suburb 2

City 2 - Suburb 1

City 3 - Suburb 1, Suburb 2, Suburb 3 etc etc

 

Many thanks for your help

[JonnoTheDev]

Join the suburbs table in the query and add to the multidimensional array. You need to learn how to structure an array so you can create a simple loop.

 

i.e.

 

array('uk' =>

array(0 => array('city' => 'Manchester', 'suburbs' => array('x', 'y')),

array(1 => array('city' => 'Liverpool', 'suburbs' => array('a', 'b')));

 

etc

[bcart]

Join the suburbs table in the query and add to the multidimensional array. You need to learn how to structure an array so you can create a simple loop.

 

i.e.

 

array('uk' =>

array(0 => array('city' => 'Manchester', 'suburbs' => array('x', 'y')),

array(1 => array('city' => 'Liverpool', 'suburbs' => array('a', 'b')));

 

etc

sorry but can you show me what the query would be and also how to add a 3rd tier of the array?

 

Many thanks

[JonnoTheDev]

sorry but can you show me what the query would be and also how to add a 3rd tier of the array?

Sorry, I am not completing your work for you. You should take the advice given and learn yourself. There is enough code there for you to try yourself.

 

Learn arrays:

http://uk2.php.net/manual/en/language.types.array.php

 

Loops

http://uk2.php.net/manual/en/control-structures.for.php

http://uk2.php.net/manual/en/control-structures.foreach.php

 

Mysql Joins

http://dev.mysql.com/doc/refman/5.1/en/join.html