Group mysql rows by column value and split with headings

[jayman2]

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 Code:

<?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>

[BubikolRamios]

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.

[jayman2]

thanks for that

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

PHP Code:

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


this leaves me with the array:

PHP Code:

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?

PHP Code:

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


Any ideas?