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
<ul>
";
  foreach ($persons as $person) {
    echo "<li>$person</li>
";
  }
  echo "</ul></li>
";
}
echo  </ul> ;

友情链接