Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Jun 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

  • #2
    Senior Coder
    Join Date
    Dec 2005
    Location
    Slovenia
    Posts
    1,979
    Thanks
    120
    Thanked 76 Times in 76 Posts
    AL | St Albans | East Anglia
    IP | Ipswich | East Anglia

    Code:
    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.
    Found a flower or bug and don't know what it is ?
    agrozoo.net galery
    if you don't spot search button at once, there is search form:
    agrozoo.net galery search

  • #3
    New to the CF scene
    Join Date
    Jun 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    thanks for that

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

    PHP Code:
    SELECT descriptiongroup_concat(abbreviationFROM 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?


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •