PHP: Dynamic Drop down with optgroup

Question

I am developing a drop down menu that uses HTML optgroups for group names that employees are a part of. Here is the MySQL query and output:

mysql> SELECT employees.emp_id,employees.empname,employees.grp_id,groups.groupname FROM employees left join groups on employees.grp_id = groups.grp_id order by groupname asc;
+--------+------------+--------+-----------+
| emp_id | empname    | grp_id | groupname |
+--------+------------+--------+-----------+
|     20 | Employee 2 |     13 | Group 1   |
|     19 | Employee 1 |     13 | Group 1   |
|     21 | Employee 3 |     14 | Group 2   |
+--------+------------+--------+-----------+
3 rows in set (0.00 sec)

The only issue is, I'm having the hardest time figuring out how to get the optgroup to work correctly. I've tried countless times, and it's really starting to frustrate me. The following is pretty much I want the output to be (example):

<select name="dropdownmenu">
    <optgroup label="Group 1">
        <option name="20">Employee 2</option>
        <option name="19">Employee 1</option>
    </optgroup>
    <optgroup label="Group 2">
        <option name="21">Employee 3</option>
    </optgroup>
</select>

Basically, the optgroup needs to be the "groupname", the option "name" should be the "emp_id", and the action "option" (drop down item) is the "empname".

I hope this is something that can be done, but really not sure. Here's the function I have, but it doesn't exactly work well:

function getDynGrpList() {
    global $db;

    // $query = "SELECT * FROM employees ORDER BY grp_id desc;";
    $query = "SELECT employees.emp_id,employees.empname,employees.grp_id,groups.groupname FROM employees left join groups on employees.grp_id = groups.grp_id order by groupname asc;";
    $employees = $db->GetAll($query);
    $groups = array();

    while ($qa = $employees->GetRows()) {
        $groups[$qa['groupname']][$qa['grp_id']] = $qa['empname'];
    }
    foreach ($groups as $label => $opt) { ?>
        <optgroup label="<?php echo $label; ?>">
<?php   }
        foreach ($groups[$label] as $id => $name) { ?>
            <option value="<?php echo $id; ?>"><?php echo $name; ?></option>
<?php } ?>
        </optgroup>
<?php }

getDynGrpList function as of 3:15AM CST (2/27):

function getDynGrpList() {
    global $db;

    // $query = "SELECT * FROM employees ORDER BY grp_id desc;";
    $query = "SELECT employees.emp_id,employees.empname,employees.grp_id,groups.groupname FROM employees left join groups on employees.grp_id = groups.grp_id order by groupname asc;";
    $employees = $db->GetAll($query);
    $groups = array();
    while ($qa = $employees->GetRows()) {
        $groups[$qa['groupname']][$qa['emp_id']] = $qa['empname'];
    }
    var_export($groups);
    foreach($groups as $label => $opt): ?>
        <optgroup label="<?php echo $label; ?>">
    <?php foreach ($opt as $id => $name): ?>
        <option value="<?php echo $id; ?>"><?php echo $name; ?></option>
    <?php endforeach; ?>
    </optgroup>
<?php endforeach;
}

Final Solution (with the help of Felix Kling)

function getDynGrpList() {
    global $db;

    $query = "SELECT employees.emp_id,employees.empname,employees.grp_id,groups.groupname FROM employees left join groups on employees.grp_id = groups.grp_id order by groupname asc;";
    $employees = $db->GetAll($query);
    $groups = array();
    foreach ($employees as $employee) {
        $groups[$employee['groupname']][$employee['emp_id']] = $employee['empname'];
    }
    foreach($groups as $label => $opt): ?>
        <optgroup label="<?php echo $label; ?>">
    <?php foreach ($opt as $id => $name): ?>
        <option value="<?php echo $id; ?>"><?php echo $name; ?></option>
    <?php endforeach; ?>
    </optgroup>
<?php endforeach;
}

Felix Kling · Accepted Answer · 2011-02-27T08:29:57.250

5

The two for loops are not nested in your code:

foreach ($groups as $label => $opt) { ?>
    <optgroup label="<?php echo $label; ?>">
<?php   } <-- wrong here
    foreach ($groups[$label] as $id => $name) { ?>
        <option value="<?php echo $id; ?>"><?php echo $name; ?></option>
<?php } ?>

The result is that first all opt groups are created and then the employees for the last group are added (because $label and $opt are also available after the loop finished).

You have to nest the loops (using alternative syntax for control structures):

<?php foreach($groups as $label => $opt): ?>
    <optgroup label="<?php echo $label; ?>">
    <?php foreach ($opt as $id => $name): ?>
        <option value="<?php echo $id; ?>"><?php echo $name; ?></option>
    <?php endforeach; ?>
    </optgroup>
<?php endforeach; ?>

Furthermore, I think you have to use the emp_id, not the grp_id when creating the array:

while ($qa = $employees->GetRows()) {
    $groups[$qa['groupname']][$qa['emp_id']] = $qa['empname'];
}

edited Feb 27 '11 at 08:29

answered Feb 27 '11 at 08:23

Felix Kling

795,719
175
1,089
1,143

I'm actually getting a blank drop down with the code. Your code and my code doesn't actually show any drop down menus. Somewhere in the code, there's an issue, just don't know where. – drewrockshard Feb 27 '11 at 09:03
@drewrockshard: Make a `var_export($groups)` before the loop and see if the array contains data and what structure it has. – Felix Kling Feb 27 '11 at 09:07
I updated my initial post. After adding the var_export($groups), there was no output on that either. Maybe if you could look at my entire function, maybe you could spot something wrong. – drewrockshard Feb 27 '11 at 09:15
I pulled the function out of the select form and let it print to the screen. Here's the error I get: `Fatal error: Call to a member function GetRows() on a non-object` – drewrockshard Feb 27 '11 at 09:22
@drewrockshard: Mmh. What is `$db`? – Felix Kling Feb 27 '11 at 09:23
I'm using adodb5 as a MySQL wrapper. It works for all my other functions (there are about 6 other functions that are using it). Here's the actual defining of `$db`: `$db = NewADOConnection("mysql");` From here, it uses the adodb5 libraries. – drewrockshard Feb 27 '11 at 09:28
Ahh, damn. Figured it out. adodb5 does the `while` loops in this way a different way. They go about it in a `foreach` approach. I'll post my solution. – drewrockshard Feb 27 '11 at 09:50

PHP: Dynamic Drop down with optgroup

1 Answers1

Linked

Related