ordering select list

[68thorby68]

I understand a select list will display the options in which they are received, however I am compiling my select list and options from a number of PHP/mysql queries and am at a lost as to how I could achive the ordering via the php/mysql route.

$all_zones=('a,b'); //
foreach($all_zones as $zone_no)
{
    //get the zone name for each value of the $all_zones array
    $zone_name_get=mysql_query("SELECT zone_name FROM zones WHERE zone_no='$zone_no'");
    $zone_name_result=mysql_fetch_array($zone_name_get);
    $zone_name=$zone_name_result['zone_name'];

    //construct a DIV and select list using the zone name as the div ID (required for js hide/display)
    echo "<div id='".$zone_name."' class='quote_collect' style='display:none;'>\n";
    echo "<label class='pblack'>Deliver Country </label>\n";
    echo "<select class='zone_show country_width' name='d_country'>\n";

    //get the allowed zones (one by one) for each of the all zone_array

    $allowed_zones_get=mysql_query("SELECT d_zone FROM zones_allowed WHERE c_zone='$zone_no' AND allowed='1'");

    while($allowed_zones_result=mysql_fetch_array($allowed_zones_get))
    {
        $allowed_zones=$allowed_zones_result['d_zone']; 

        //get the country ID and the country name for each country in the allowed zone ---this is where I think I should order the results, but I could have upto 10 results to order

        $allowed_country_get=mysql_query("SELECT id, country FROM country_zones WHERE zone='$allowed_zones' ORDER BY country");

        while($allowed_country_result=mysql_fetch_array($allowed_country_get))
        {
            $country_id=$allowed_country_result['id'];
            $country_name=$allowed_country_result['country'];

            //construct option element of the select list
            echo "<option value='".$country_id."'>".$country_name."</option>\n";
        }

    }
echo "</select>";
echo "</div>\n";

}

This will give me similar to this (I've put a line break in each result set to show each loop of the $allowed_country_get query)

<div id='UK' class='quote_collect' style='display:none;'>
<label class='pblack'>Deliver Country </label>
<select class='zone_show country_width' name='d_country'>

<option value='243'>England</option>
<option value='10'Ireland></option>
<option value='18'>Scotland</option>
<option value='21'>Wales</option>

<option value='96'>Canada</option>
<option value='119'>USA</option>

<option value='196'>Andorra</option>
<option value='197'>France</option>
<option value='244'>Germany</option>

<option value='27'>Hong Kong</option>
<option value='35'>Singapore</option>
<option value='43'>Thailand</option>

</select></div>


<div id='America' class='quote_collect' style='display:none;'>
<label class='pblack'>Deliver Country </label>
<select class='zone_show country_width' name='d_country'>

<option value='243'>England</option>
<option value='10'Ireland></option>
<option value='18'>Scotland</option>
<option value='21'>Wales</option>

<option value='96'>Canada</option>
<option value='119'>USA</option>

<option value='55'>Iceland</option>
<option value='57'>Norway</option>
<option value='54'>Sweden</option>

<option value='427'>Australia</option>
<option value='435'>New Zealand</option>
<option value='443'>Tonga</option>

</select></div>

Of course for each select, inside each div, I would sort all of the options into an order, not just each iteration of the while loop.

Any help would be greatly appreciated

[Biiim]

Some left join magic

SELECT * FROM `zones`
LEFT JOIN `zones_allowed` ON `zones`.`zone_no` = `zones_allowed`.`c_zone`
LEFT JOIN `country_zones` ON `zones_allowed`.`d_zone` = `country_zones`.`zone`
WHERE 
    `zones_allowed`.`allowed` = 1
ORDER BY `zones`.`zone_no` ASC;


<?php

$Q = "SELECT `zones`.`zone_name`,`zones_allowed`.`d_zone`,`country_zones`.`id`,`country_zones`.`country` FROM `zones`"
    ." LEFT JOIN `zones_allowed` ON `zones`.`zone_no` = `zones_allowed`.`c_zone`"
    ." LEFT JOIN `country_zones` ON `zones_allowed`.`d_zone` = `country_zones`.`zone`"
    ." WHERE `zones_allowed`.`allowed` = 1"
    ." ORDER BY `zones`.`zone_no` ASC";
$R = mysql_query($Q);
if($R !== false){
    $data = array();
    while($row = mysql_fetch_Assoc($R)){
        $data[$row['zone_name']][$row['country']] = $row['id'];
    }
    foreach($data as $k=>$v){//$k == zone_name
        asort($v);//sort array key in ascending order(the country)
        echo "<div id='{$k}' class='quote_collect' style='display:none;'>\n";
        echo "<label class='pblack'>Deliver Country </label>\n";
        echo "<select class='zone_show country_width' name='d_country'>\n";
        foreach($v as $ke=>$va){//loop through this zone_name's allowed countries
            echo "<option value='{$v}'>{$k}</option>\r\n";
        }
        echo "</select>\r\n";
        echo "</div>\r\n";
    }
}else{
    echo "select error";
}

Not tested so hope theres no errors, be much faster too as there's only 1 mysql query - make sure you got indexs on these fields:
zones.zone_no
zones_allowed.c_zone
zones_allowed.d_zone
country_zones.zone
zones_allowed.allowed

as that will make the join go like lightning

[68thorby68]

Wow. Many thanks. Looks like I've got a few more hours with the "wonderful" PHP manual.

As I'm not familiar with left joins, et el, and I'll have to provide support going forward, I'll step through the code line by line so I get an understanding of what going on. When finished, i'll post everything I have for this function in order it may benefit others

Once again, WOW, and many thanks.