Grouping results in rows

[Mutley]

I have a query that selects a bunch of fields and loops them into a display like this:

 

<?php
{ // Start Looping ?>
<tr>
<td><?=$id?></td>
<td><?=$name?></td>
</tr>
<? }// End Looping?>

 

What I wish to do, is instead of create lots of rows, is if there are several results with the same $name, that they group them in a new row/table underneath so I can apply some javascript to collapse/expand the matching results. Like so:

<tr>
<td><?=$id?></td>
<td><?=$name?></td>
</tr>
<div id="collapse">
<tr>
<td><?=$id?></td>
<td>Another matching result... <?=$name?></td>
</tr>
<tr>
<td><?=$id?></td>
<td>Another matching result... <?=$name?></td>
</tr>
</div>

 

How would I approach this?

 

Thanks in advance.

 

[ted_chou12]

 
<?php 
while (query...) {
$array[] = array($row['id'], $row['name']);
$namearray [] = $row['name'];}
array_unique($namearray);
foreach ($array as $a) {
foreach ($namearray as $b) {
echo "<tr>
<td>name: $b</td>";
if ($b = $a[0]) {
echo "<td> id: {$a[1]}</td>";}
echo "</tr>";}}}
?>

sounds pretty complicated

[Barand]

(Data from tutorial on main site)

SELECT h.house_name, p.pupil_name
FROM pupil p
INNER JOIN house h USING (houseID)
+------------+------------------+
| house_name | pupil_name       |
+------------+------------------+
| Laker      | Adam Simms       |
| Laker      | David Powell     |
| Laker      | Gearge Wilson    |
| Laker      | Jane Morrison    |
| Laker      | Peter Adamson    |
| Laker      | Wayne Jones      |
| Grace      | Allan Blair      |
| Grace      | Anna Hamilton    |
| Grace      | Caroline Freeman |
| Grace      | John Watson      |
| Grace      | John Williams    |
| Grace      | Mary Whitehouse  |
| Jardine    | Anne Bailey      |
| Jardine    | John Patterson   |
| Jardine    | John Tully       |
| Jardine    | Mary Sheldon     |
| Jardine    | Michael Grove    |
| Jardine    | Peter Appleby    |
| Cowdrey    | Anthony Bell     |
| Cowdrey    | Emma Watson      |
| Cowdrey    | Henry Irving     |
| Cowdrey    | Margaret Norton  |
| Cowdrey    | Mary Blake       |
| Cowdrey    | William Smith    |
+------------+------------------+

 

Using GROUP_CONCAT:

mysql> SELECT h.house_name, GROUP_CONCAT(p.pupil_name SEPARATOR ', ') as pupils
    -> FROM pupil p
    -> INNER JOIN house h USING (houseID)
    -> GROUP BY h.house_name;
+------------+-------------------------------------------------------------------------------------------+
| house_name | pupils                         |
+------------+-------------------------------------------------------------------------------------------+
| Cowdrey    | Anthony Bell, Emma Watson, Henry Irving, Margaret Norton, Mary Blake, William Smith       |
| Grace      | Allan Blair, Anna Hamilton, Caroline Freeman, John Watson, John Williams, Mary Whitehouse |
| Jardine    | Anne Bailey, John Patterson, John Tully, Mary Sheldon, Michael Grove, Peter Appleby       |
| Laker      | Adam Simms, David Powell, Gearge Wilson, Jane Morrison, Peter Adamson, Wayne Jones        |
+------------+-------------------------------------------------------------------------------------------+