Group mysql rows by column value and split with headings

Hi All

I have searched for the last 3 hours to answer to this but with no luck.

I am not an expert with PHP but can usually put things together which do the job.

The code below looks at a mysql table of UK postcodes and their corresponding cities in the following format:

abbreviation | name | description

AL | St Albans | East Anglia
IP | Ipswich | East Anglia
B | Birmingham | Midlands
CV | Coventry | Midlands
BD | Bradford | North East
Dh | Durham | North East

Currently, the code lists all post codes with it's name in a 4-across grid. However, what I need it to do is group the postcodes by the description to create blocks with the description as the heading, for example:

East Anglia:
[] AL (St Albans) [] IP (Ipswich) ....

Midlands:
[] B (Birmingham) [] CV (Coventry) ......etc


Any help would be greatly appreciated!


<?php

function system_getUKServiceAreas(){
$sql = "SELECT * FROM UKServiceAreas ORDER BY description";
$result = $dbObj->query($sql);

if($result){
while($row = mysql_fetch_array($result))
$rows[]= $row;
return $rows;
}
return false;

}


function system_countUKServiceAreas(){
$sql = "SELECT count(abbreviation) as count FROM UKServiceAreas";
$result = $dbObj->query($sql);

if($result){
while($rows = mysql_fetch_array($result))
$countuk= $rows["count"];
return $countuk;
}
return 0;
}


unset($check_ukservicearea);
$check_ukserviceareas = system_getUKServiceAreas();
$count_servedareas = 0;
unset($show_served_array);
if($served_areas)
$show_served_array = explode("|",$served_areas);
?>

<? $count_table = 0;?>
<table class="standard-table" cellspacing="0" cellpadding="2" border="0">
<? foreach($check_ukserviceareas as $check_ukservicearea ){?>
<td class="td-checkbox">
<input type="checkbox" id="served_areas<?=$count_servedareas?>" name="served_areas<?=$count_servedareas?>" class="inputCheck" value="<?=$check_ukservicearea["abbreviation"]?>" <?
if($show_served_array){
foreach($show_served_array as $show_served_){
if($show_served_ == $check_ukservicearea["abbreviation"])
echo "checked=\"checked\"";
}
}
?>style="vertical-align:middle;" />
</td>
<td>
<label for="served_areas<?=$count_sort_of_experience?>">
<?=$check_ukservicearea["abbreviation"]?> (<?=$check_ukservicearea["name"]?>)
</label>
</td>
<? if($count_table==3){?>
</tr>
<tr>
<? }?>
<?
$count_table++;
if($count_table==4) $count_table = 0;
?>
<? $count_servedareas ++; ?>
<? } ?>
</table>

AL | St Albans | East Anglia
IP | Ipswich | East Anglia


select col3,group_concat(concat(col1,'delimiter',col2))
group by col 3


should return row like:

East Anglia|AL delimiter St Albans,IP delimiter Ipswich

out of head.

thanks for that

after playing around with it, i think this will do the job:

SELECT description, group_concat(abbreviation) FROM UKServiceAreas GROUP BY description

this leaves me with the array:

Array ( [0] => Array ( [0] => East Anglia [description] => East Anglia [1] => AL,CB,CM,CO,IG,IP,LU,MK,NR,PE,RM,SG,SS,WD [group_concat(abbreviation)] => AL,CB,CM,CO,IG,IP,LU,MK,NR,PE,RM,SG,SS,WD ) [1] => Array ( [0] => Midlands [description] => Midlands [1] => B,CV,DE,DY,LE,NG,NN,ST,WS,WV [group_concat(abbreviation)] => B,CV,DE,DY,LE,NG,NN,ST,WS,WV ) [2] =>

But I'm struggling to output this with the description column as the header?


<h4>East Anglia</h4>
<input type=checkbox value=AL><label>AL</label>
etc...



Any ideas?