How do I sort the result of an SQL join by a single column in PHP

Question

I have a database with a list of locations e.g.:

C_ID   |   Name 

1    |   Italy
2    |   Germany
3    |   France
4    |   Spain

and in a seperate table, a list of people who are from those countries,

P_ID  |  Name   |  C_ID

1   | John    | 1
2   | Mark    | 1
3   | Paul    | 2
4   | Pierre  | 3
5   | Alan    | 2

I have grabbed this data in an SQL query with a join...

SELECT locations.name as locaname, people.name as peoplename
FROM locations
INNER JOIN people
ON locations.c_id=people.c_id
ORDER BY locations.name";

Which provides me with:

Italy John
Italy Mark
Germany Paul
Germany Alan
France Pierre

However, what I am trying to achieve is a unordered list, echoed out by PHP. With the people names grouped by country name. As opposed to the country name being repeated. I've tried using arrays however my knowledge of PHP/MySQL is letting me down...

Answer 1

Try:

$sql = <<<END
SELECT locations.name as locaname, people.name as peoplename
FROM locations
INNER JOIN people
ON locations.c_id=people.c_id
ORDER BY locations.name"
END;
$query = mysql_query($query) or die($sql . ' - ' . mysql_error());
$result = array();
while ($row = mysql_fetch_array($query)) {
  $result[$row[0]][] = $row[1];
}
print_r($result);

$row[0] is the country, $row[1] is the person's name. The function in the while loop is key here. This does quite a lot. It basically adds an entry to the result array keyed by country name and appends the person's name to the array it stores there.

Edit: To order as a list:

echo '<ul>';
foreach ($result as $country => $persons) {
  echo "<li>$country\n<ul>\n";
  foreach ($persons as $person) {
    echo "<li>$person</li>\n";
  }
  echo "</ul></li>\n";
}
echo '</ul>';