title: PHP: Dynamic Drop down with optgroup
body: <p>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:</p>
  
  <pre>
  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)
  </pre>
  
  <p>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. <strong>The following is pretty much I want the output to be (example):</strong></p>
  
  <pre><code>&lt;select name="dropdownmenu"&gt;
      &lt;optgroup label="Group 1"&gt;
          &lt;option name="20"&gt;Employee 2&lt;/option&gt;
          &lt;option name="19"&gt;Employee 1&lt;/option&gt;
      &lt;/optgroup&gt;
      &lt;optgroup label="Group 2"&gt;
          &lt;option name="21"&gt;Employee 3&lt;/option&gt;
      &lt;/optgroup&gt;
  &lt;/select&gt;
  </code></pre>
  
  <p>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".</p>
  
  <p>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:</p>
  
  <pre><code>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-&gt;GetAll($query);
      $groups = array();
  
      while ($qa = $employees-&gt;GetRows()) {
          $groups[$qa['groupname']][$qa['grp_id']] = $qa['empname'];
      }
      foreach ($groups as $label =&gt; $opt) { ?&gt;
          &lt;optgroup label="&lt;?php echo $label; ?&gt;"&gt;
  &lt;?php   }
          foreach ($groups[$label] as $id =&gt; $name) { ?&gt;
              &lt;option value="&lt;?php echo $id; ?&gt;"&gt;&lt;?php echo $name; ?&gt;&lt;/option&gt;
  &lt;?php } ?&gt;
          &lt;/optgroup&gt;
  &lt;?php }
  </code></pre>
  
  <p><strong>getDynGrpList function as of 3:15AM CST (2/27):</strong></p>
  
  <pre><code>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-&gt;GetAll($query);
      $groups = array();
      while ($qa = $employees-&gt;GetRows()) {
          $groups[$qa['groupname']][$qa['emp_id']] = $qa['empname'];
      }
      var_export($groups);
      foreach($groups as $label =&gt; $opt): ?&gt;
          &lt;optgroup label="&lt;?php echo $label; ?&gt;"&gt;
      &lt;?php foreach ($opt as $id =&gt; $name): ?&gt;
          &lt;option value="&lt;?php echo $id; ?&gt;"&gt;&lt;?php echo $name; ?&gt;&lt;/option&gt;
      &lt;?php endforeach; ?&gt;
      &lt;/optgroup&gt;
  &lt;?php endforeach;
  }
  </code></pre>
  
  <p><strong>Final Solution (with the help of Felix Kling)</strong></p>
  
  <pre><code>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-&gt;GetAll($query);
      $groups = array();
      foreach ($employees as $employee) {
          $groups[$employee['groupname']][$employee['emp_id']] = $employee['empname'];
      }
      foreach($groups as $label =&gt; $opt): ?&gt;
          &lt;optgroup label="&lt;?php echo $label; ?&gt;"&gt;
      &lt;?php foreach ($opt as $id =&gt; $name): ?&gt;
          &lt;option value="&lt;?php echo $id; ?&gt;"&gt;&lt;?php echo $name; ?&gt;&lt;/option&gt;
      &lt;?php endforeach; ?&gt;
      &lt;/optgroup&gt;
  &lt;?php endforeach;
  }
  </code></pre>