How to generate dynamic selectbox with optgroup

Question

Hi I am having this mysql table.

CREATE TABLE IF NOT EXISTS `selling_counties` (
  `county_id` int(11) NOT NULL auto_increment,
  `country` varchar(20) collate utf8_unicode_ci NOT NULL,
  `county_name` varchar(25) collate utf8_unicode_ci NOT NULL,
  `datecreated` datetime NOT NULL,
  `datemodified` datetime NOT NULL,
  PRIMARY KEY  (`county_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=97 ;

and I need have a selectbox with optgroup where country will be and county_name will the s such as

<select name ="county">
    <optgroup label="England">
        <option>South Yorkshire</option>
        <option>Staffordshire</option>
        <option>Suffolk</option>
        <option>Surrey</option>
        <option>Tyne and Wear</option>
        <option>Warwickshire</option>
        <option>West Midlands</option>
        <option>West Sussex</option>
        <option>West Yorkshire</option>
        <option>Wiltshire</option>
        <option>Worcestershire</option>
    </optgroup>
    <optgroup label="Wales">
        <option>Clwyd</option>
        <option>Dyfed</option>
        <option>Gwent</option>
        <option>Gwynedd</option>
        <option>Mid Glamorgan</option>
        <option>Powys</option>
        <option>South Glamorgan</option>
        <option>West Glamorgan</option>
    </optgroup>
    <optgroup label="Scotland">
        <option>Aberdeenshire</option>
        <option>Angus</option>
        <option>Argyll</option>
        <option>Selkirkshire</option>
        <option>Shetland</option>
        <option>Stirlingshire</option>
        <option>Sutherland</option>
        <option>West Lothian</option>
        <option>Wigtownshire</option>
    </optgroup>
    <optgroup label="Northern Ireland">
        <option>Antrim</option>
        <option>Armagh</option>
        <option>Down</option>
        <option>Fermanagh</option>
        <option>Londonderry</option>
        <option>Tyrone</option>
    </optgroup>
</select>

I have already tried this PHP: Dynamic Drop down with optgroup but it is bit complex for me as I am newbie with php, moreover it is coming from 2 seperate tables and mine is coming from same table.

Any help will be highly appreciable.

MrCode · Answer 1 · 2012-11-21T10:16:38.800

2

Create a new array like this, with countries as the keys, and the array of counties as each value. Assuming $rows is the array of raw table rows.

<?php

$countries = array();

foreach($rows as $row)
{
    if(isset($countries[$row['country']])) // <-- edit, was missing a ]
    {
        $contries[$row['country']][] = $row['county_name'];
    }
    else
    {
        $countries[$row['country']] = array($row['county_name']);
    }
}

?>

<select name ="county">
    <?php foreach($countries as $country => $counties): ?>

        <optgroup label="<?php echo htmlentities($country); ?>">

            <?php foreach($counties as $county): ?>

                <option><?php echo htmlentities($county); ?></option>

            <?php endforeach; ?>

        </optgroup>

    <?php endforeach; ?>
</select>

edited Nov 21 '12 at 10:16

answered Nov 21 '12 at 09:03

MrCode

63,975
10
90
112

there seems to be something wrong around this line $contries[$row['country']][] = $row['county_name']; – Asnexplore Nov 21 '12 at 10:11
@user1530513 I was missing a `]` on the `isset()` line. Try again. – MrCode Nov 21 '12 at 10:16
Thanks for your kind help buddy. I used some part of it to make it work. Thanks a lot. – Asnexplore Nov 23 '12 at 07:12

Olaf Dietsche · Accepted Answer · 2012-11-23T11:45:19.487

0

You need to select the values from your table

$mysqli = new mysqli($host, $user, $passwd, $database);
$result = $mysqli->query('select county_id, country, county_name from selling_counties');
$countries = array();
while ($row = $result->fetch_assoc()) {
    $country = $row['country'];
    if (!array_key_exists($country, $countries))
        $countries[$country] = array();

    $countries[$country][] = array($row['county_id'], $row['county']);
}

and put these in your html select.

Update to add county_id in <option>:

<option value="<?php echo htmlentities($county[0]); ?>"><?php echo htmlentities($county[1]); ?></option>

edited Nov 23 '12 at 11:45

answered Nov 21 '12 at 09:19

Olaf Dietsche

72,253
8
102
198

Can you plz paste the code for html so that i can try it out. Thanks – Asnexplore Nov 21 '12 at 10:10
@user1530513 You can use MrCode's html part. This should work fine. – Olaf Dietsche Nov 21 '12 at 10:48
One thing you missed out, how can I have the id in the – Asnexplore Nov 23 '12 at 11:30
1

@user1530513 To add county_id, you must select it in the query and update option accordingly. See modified answer. – Olaf Dietsche Nov 23 '12 at 11:44
Thanks buddy, it was awesome. Really got it right in first go. Thank you very much mate. – Asnexplore Nov 23 '12 at 11:58

How to generate dynamic selectbox with optgroup

2 Answers2

Linked