Option group select

[eluser]doubleplusgood[/eluser]
Hi there,

I'm trying to do a region select drop down. But I'd like the regions group in the same select for England, Scotland and Wales.

Here's a sample of the database table as it stands at the moment with some regions and countries;

+----+---------------------+----------+
| id | name | country |
+----+---------------------+----------+
| 1 | East Midlands | england |
| 2 | East of England | england |
| 3 | North East Scotland | scotland |
| 4 | Highlands | scotland |
| 5 | Mid and West Wales | wales |
| 6 | North Wales | wales |
+----|---------------------+----------+

So far, I just have a select showing the England regions as follows;

Code:

<select name="region">
    <option value="-">-</option>
    &lt;?php foreach($regions as $item) : ?&gt;
    <option value="&lt;?php echo $item['id']?&gt;" &lt;?php if($this->input->get_post('region') == $item['id']) echo 'selected' ?&gt;>&lt;?php echo $item['name']?&gt;</option>
    &lt;?php endforeach; ?&gt;
</select>

And my Model;

Code:

public function getRegions(){
    $this->db->order_by("name", "asc");
    $query=$this->db->get('regions');
    if($query->num_rows()>0){
        // return result set as an associative array
        return $query->result_array();
    }
}

So basically, i'm wondering if anyone could point me in the right direction on how to group the regions in the select so that it would follow this sort of format?

Code:

<select name="regions">
    <optgroup label="england">
        <option value=""></option>
        <option value=""></option>
        <option value=""></option>
    </optgroup>
    <optgroup label="scotland">
        <option value=""></option>
        <option value=""></option>
        <option value=""></option>
    </optgroup>
    <optgroup label="wales">
        <option value=""></option>
        <option value=""></option>
        <option value=""></option>
    </optgroup>
</select>

Thank you.

[eluser]smilie[/eluser]
Hm, there would be probably an MySQL only solution for this, but only thing I can think of is:

Do 2 query's:
First query will get only unique countries.
Then, in second query build up array of all 'name' belonging to each country.

So, you will end up with array like this:

Code:

$select = array('england' => array('name1','name2','name3'), 'wales' => array('name1','name2'));

Then, you can simply do 2 foreaches to create select (HTML) as you want it.

Good luck

[eluser]Dennis Rasmussen[/eluser]
@Hanabi Do it the way you did, however sort by country first then name.
Do a loop and everytime the current country is different than the previous country you create a label with that country.

Code:

<select name="region">
    <option value="-">-</option>
    &lt;?php
    $currCountry = "";
    foreach($regions as $item) :
        if ($item['country'] != $currCountry)
        {
            // echo label...
            $currCountry = $item['country'];
            $endLabel = true;
        }
    ?&gt;
    <option value="&lt;?php echo $item['id']?&gt;" &lt;?php if($this->input->get_post('region') == $item['id']) echo 'selected' ?&gt;>&lt;?php echo $item['name']?&gt;</option>
    &lt;?php
    if ($endLabel)
    {
        // echo ending label here
    }
    endforeach;
    ?&gt;
</select>

Something like that I'd guess.

[eluser]doubleplusgood[/eluser]
Thanks Dennis. Do you know how i'd get the option group in there, so that people could see in the select the group name for each set of regions?

[eluser]Dennis Rasmussen[/eluser]
You create the optgroup element by the 2 comments I wrote in the script above.
I just called them labels in my comments.

Code:

<select name="region">
    <option value="-">-</option>
    &lt;?php
    $currCountry = "";
    foreach($regions as $item) :
        if ($item['country'] != $currCountry)
        {
            // echo label...
            echo '<optgroup label="' . $item['country'] . '">';
            $currCountry = $item['country'];
            $endLabel = true;
        }
    ?&gt;
    <option value="&lt;?php echo $item['id']?&gt;" &lt;?php if($this->input->get_post('region') == $item['id']) echo 'selected' ?&gt;>&lt;?php echo $item['name']?&gt;</option>
    &lt;?php
    if ($endLabel)
    {
        // echo ending label here
        echo '</optgroup>';
    }
    endforeach;
    ?&gt;
</select>

[eluser]Dennis Rasmussen[/eluser]
Oh wait I made a mistake.
Here's the correct code:

Code:

<select name="region">
    <option value="-">-</option>
    &lt;?php
    $currCountry = "";
    foreach($regions as $item) :
        if ($item['country'] != $currCountry)
        {
            if ($endLabel)
            {
                // echo ending label here
                echo '</optgroup>';
            }
            // echo label...
            echo '<optgroup label="' . $item['country'] . '">';
            $currCountry = $item['country'];
            $endLabel = true;
        }
    ?&gt;
    <option value="&lt;?php echo $item['id']?&gt;" &lt;?php if($this->input->get_post('region') == $item['id']) echo 'selected' ?&gt;>&lt;?php echo $item['name']?&gt;</option>
    &lt;?php
    if ($endLabel)
    {
        // echo ending label here
        echo '</optgroup>';
    }
    endforeach;
    ?&gt;
</select>

[eluser]doubleplusgood[/eluser]
Hey Dennis,

Thank you very much. I had to remove the 'echo' out of the last if statement, as it was adding a closing optgroup after each option.

Cheers,
Neil

[eluser]Dennis Rasmussen[/eluser]
Oh that is another mistake of mine haha.
You have to echo AFTER the endforeach for a last optgroup

Code:

<option value="&lt;?php echo $item['id']?&gt;" &lt;?php if($this->input->get_post('region') == $item['id']) echo 'selected' ?&gt;>&lt;?php echo $item['name']?&gt;</option>
    &lt;?php
    endforeach;
    if ($endLabel)
    {
        // echo ending label here
        echo '</optgroup>';
    }
    ?&gt;
</select>

[eluser]doubleplusgood[/eluser]
Hehe, you're a star.

Thanks mate.