title: Populate SELECT with OPTGROUP and OPTION values using AJAX
body: <p>I have got a simple bit of PHP / Ajax / jQuery working so that the contents of a 2nd SELECT are changed when I choose a value in the first SELECT.</p>
  
  <p><strong>HTML</strong></p>
  
  <pre><code>&lt;select class='form-control' id='cat_parent' name='cat_parent'&gt;
      &lt;option value=''&gt;Select Parent&lt;/option&gt;
      &lt;option value='1'&gt;Oracle&lt;/option&gt;
      &lt;option value='6'&gt;SQL&lt;/option&gt;
  &lt;/select&gt;
  
  &lt;select class='form-control' id='cat_id' name='cat_id'&gt;
  &lt;/select&gt;
  </code></pre>
  
  <p><strong>jQuery:</strong></p>
  
  <pre><code>$(document).ready(function() {
      $('#cat_parent').change(function() {
          var currentValue = $(this).val();
          // Populate Categories once select Parent
          $.get("../ajax/ajax-cats-from-parent.php", {'cat_parent': currentValue}, function(data) {
              var cat_id = $.parseJSON(data);
              $('#cat_id').empty();
              for (var i = 0; i &lt; cat_id.length; i++) {
                  var regionOption = '&lt;option value="'+cat_id[i]['id']+'"&gt;';
                  regionOption += cat_id[i]['label'];
                  regionOption += '&lt;/option&gt;';
                  $('#cat_id').append(regionOption);
              }
          });
  
      });
  });
  </code></pre>
  
  <p><strong>PHP (ajax-cats-from-parent.php)</strong></p>
  
  <pre><code> $sql = "SELECT cats.fld_label
                , cats.fld_id
             FROM tbl_b_cats cats
            WHERE cats.fld_parent = ? 
         ORDER BY cats.fld_label";
  
  $stmt = $conn-&gt;stmt_init();
  
  if (!$stmt-&gt;prepare($sql)) {
      throw new Exception("Error preparing statement: $stmt-&gt;error, SQL query: $sql");
  }
  
  if (!$stmt-&gt;bind_param('s', $_GET['cat_parent'])) {
      throw new Exception("Error binding parameter: $stmt-&gt;error");
  }
  
  $stmt-&gt;execute();
  $stmt-&gt;store_result();
  
  if ($stmt-&gt;num_rows == 0) {
      header('HTTP/1.0 404 Not Found');
      exit;
  } else {
  
      $AjaxCatsFromParent = array();
  
      $stmt-&gt;bind_result($fld_label, $fld_id);
  
      while ($stmt-&gt;fetch()) {
          $AjaxCatsFromParent []= [
              'id'    =&gt; $fld_id,
              'label' =&gt; $fld_label
          ];
      }
  
      echo json_encode($AjaxCatsFromParent);
  
  }
  $stmt-&gt;free_result();
  $stmt-&gt;close();
  
  $conn -&gt; close();
  </code></pre>
  
  <p>That all works fine.</p>
  
  <p>I'm trying to populate another SELECT, also based on choosing a value in the first SELECT menu (ID cat_parent).</p>
  
  <p>This is so that for a new post, I can assign a parent post.</p>
  
  <p>The trouble I am having is that the SELECT has an OPTGROUP label for each category, and then within the OPTGROUP, posts are listed for that category.</p>
  
  <p>The SQL to get the posts is:</p>
  
  <pre><code>$query = "SELECT fld_id fld_id2
                 , fld_title fld_title2 
              FROM tbl_c_posts 
             WHERE fld_catid = ? 
          ORDER BY fld_title";
  </code></pre>
  
  <p>Where the ? is the ID of the category that the posts are assigned to.</p>
  
  <p>I would want the SELECT to look like this for example:</p>
  
  <pre><code>&lt;select class='form-control' id='parent_cat_id' name='parent_cat_id'&gt;
      &lt;option value=''&gt;Select Parent Post&lt;/option&gt;
      &lt;optgroup label='Accounts Payable'&gt;
              &lt;option value='88'&gt;a&lt;/option&gt;
              &lt;option value='1'&gt;AP - Invoices&lt;/option&gt;
              &lt;option value='79'&gt;AP - Supplier Reports&lt;/option&gt;
      &lt;/optgroup&gt;
      &lt;optgroup label='Accounts Receivable'&gt;
              &lt;option value='2'&gt;AR - Customers&lt;/option&gt;
              &lt;option value='3'&gt;AR - Interface Lines&lt;/option&gt;
              &lt;option value='4'&gt;AR - Transactions&lt;/option&gt;
      &lt;/optgroup&gt;
      &lt;optgroup label='BLOB'&gt;
              &lt;option value='87'&gt;s&lt;/option&gt;
              &lt;option value='86'&gt;sadsadsad&lt;/option&gt;
      &lt;/optgroup&gt;
  &lt;/select&gt;
  </code></pre>
  
  <p>Therefore the problem I have is that the PHP needs to output:</p>
  
  <ol>
  <li>A loop for the Categories (to output the OPTGROUP labels)</li>
  <li>For each Category, a loop of Posts assigned to that Category (to output the OPTION values)</li>
  </ol>
  
  <p>However, the PHP code can only output one array, as far as I know?</p>
  
  <p>I got this far:</p>
  
  <pre><code>// SQL FOR THE OPTGROUP LABELS ############################################
  
   $sql = "SELECT cats.fld_label
                , cats.fld_id
             FROM tbl_b_cats cats
            WHERE cats.fld_parent = ?
         ORDER BY cats.fld_label";
  
  $stmt = $conn-&gt;stmt_init();
  
  if (!$stmt-&gt;prepare($sql)) {
      throw new Exception("Error preparing statement: $stmt-&gt;error, SQL query: $sql");
  }
  
  if (!$stmt-&gt;bind_param('s', $_GET['cat_parent'])) {
      throw new Exception("Error binding parameter: $stmt-&gt;error");
  }
  
  $stmt-&gt;execute();
  $stmt-&gt;store_result();
  
  if ($stmt-&gt;num_rows == 0) {
      header('HTTP/1.0 404 Not Found');
      exit;
  } else {
  
      // array for OPTGROUPS
      $AjaxOptGpLabel = array();
  
      $stmt-&gt;bind_result($fld_label, $fld_id);
  
      while ($stmt-&gt;fetch()) {
          $AjaxOptGpLabel []= [
              'label' =&gt; $fld_label
          ];
  
          // sub-select for the posts assigned to the category
          $query = "SELECT fld_id fld_id2
                         , fld_title fld_title2 
                      FROM tbl_c_posts 
                     WHERE fld_catid = ? 
                  ORDER BY fld_title";
  
          $stmt2 = $conn-&gt;stmt_init();
  
          if (!$stmt2-&gt;prepare($query)) {
              throw new Exception("Error preparing statement: $stmt2-&gt;error, SQL query: $query");
          }
  
          if (!$stmt2-&gt;bind_param('s', $fld_id)) {
              throw new Exception("Error binding parameter: $stmt2-&gt;error");
          }
  
          $stmt2-&gt;execute();
          $stmt2-&gt;store_result();
  
          if ($stmt2-&gt;num_rows &gt; 0){
  
              // array for the posts
              $AjaxOptGpPosts = array();
  
              /* get results */
              $stmt2-&gt;bind_result($fld_id2, $fld_title2);
  
              while ($stmt2-&gt;fetch()) {
  
                  $AjaxOptGpPosts []= [
                      'id'    =&gt; $fld_id2,
                      'label' =&gt; $fld_title2
                  ];
  
              }
  
          }
  
          /* tidy up */
          $stmt2-&gt;free_result();
          $stmt2-&gt;close();
  
      }
  
      echo json_encode($AjaxOptGpLabel);
  
  }
  /* free and close */
  $stmt-&gt;free_result();
  $stmt-&gt;close();
  
  $conn -&gt; close();
  </code></pre>
  
  <p>So this sort of works in as much as the PHP generates a "AjaxOptGpPosts" array containing the posts for each Category, but then?</p>
  
  <ol>
  <li><p>How do I get that into the output of the PHP, since it currently only returns the array for the OPTGROUPS via:</p>
  
  <p>echo json_encode($AjaxOptGpLabel);</p></li>
  <li><p>How would I revise the jQuery to have a loop within a loop to populate the OPTGROUPS and OPTION elements?</p></li>
  </ol>
  
  <p>Thanks!</p>