填充OPTGROUP和嵌套的OPTION值

Question

I have a PHP file which takes an ID as an input and outputs an array.

The idea is that the first SQL will generate a list of category values to use as labels for an OPTGROUP.

The 2nd SQL finds posts belonging to the parent category, and adds them into the array.

PHP

 $sql = "SELECT cats.fld_label
              , cats.fld_id
           FROM tbl_b_cats cats
          WHERE cats.fld_parent = ?
       ORDER BY cats.fld_label";

$stmt = $conn->stmt_init();
if (!$stmt->prepare($sql)) { throw new Exception("Error preparing statement: $stmt->error, SQL query: $sql"); }
if (!$stmt->bind_param('s', $_GET['cat_parent'])) { throw new Exception("Error binding parameter: $stmt->error"); }
$stmt->execute();
$stmt->store_result();
if ($stmt->num_rows == 0) {
    header('HTTP/1.0 404 Not Found');
    exit;
} else {
    $AjaxOptGpLabel = array();
    $stmt->bind_result($fld_label, $fld_id);
    while ($stmt->fetch()) {
        $query = "SELECT fld_id fld_id2, fld_title fld_title2 FROM tbl_c_posts WHERE fld_catid = ? ORDER BY fld_title";
        $stmt2 = $conn->stmt_init();
        if (!$stmt2->prepare($query)) { throw new Exception("Error preparing statement: $stmt2->error, SQL query: $query");}
        if (!$stmt2->bind_param('s', $fld_id)) {throw new Exception("Error binding parameter: $stmt2->error");}
        $stmt2->execute();
        $stmt2->store_result();
        if ($stmt2->num_rows > 0){
            $AjaxOptGpPosts = array();
            $stmt2->bind_result($fld_id2, $fld_title2);
            while ($stmt2->fetch()) {
                $AjaxOptGpPosts []= [
                    'id'    => $fld_id2,
                    'label' => $fld_title2
                ];
            }
        }

        $stmt2->free_result();
        $stmt2->close();

        $AjaxOptGpLabel [] = [
            $fld_label,
            $AjaxOptGpPosts
        ];

    }

    echo json_encode($AjaxOptGpLabel);
}
/* free and close */
$stmt->free_result();
$stmt->close();

$conn -> close();

JSON Output

[
    ["AP", [{
        "id": 88,
        "label": "a"
    }, {
        "id": 1,
        "label": "AP - Invoices"
    }, {
        "id": 79,
        "label": "AP - Supplier Reports"
    }]],
    ["AR", [{
        "id": 2,
        "label": "AR - Customers"
    }, {
        "id": 3,
        "label": "AR - Interface Lines"
    }, {
        "id": 4,
        "label": "AR - Transactions"
    }]]
]

jQuery

I am a bit familiar with using jQuery to loop through JSON to append values to a SELECT menu - e.g.

$(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 < cat_id.length; i++) {
                var regionOption = '<option value="'+cat_id[i]['id']+'">';
                regionOption += cat_id[i]['label'];
                regionOption += '</option>';
                $('#cat_id').append(regionOption);
            }
        });

    });
});

But I am very stuck trying to adapt the methods used above to generate this type of HTML from the JSON output above:

<optgroup label="AP">
    <option value="88">a</option>
    <option value="1">AP - Invoices</option>
    <option value="798">AP - Supplier Reports</option>
</optgroup>
<optgroup label="AR">
    <option value="2">AR - Customers</option>
    <option value="3">AR - Interface Lines</option>
    <option value="4">AR - Transactions</option>
</optgroup>

Any advice much appreciated. Thanks

Answer 1

you can do like this FIDDLE:

var output = '[["AP",[{"id":88,"label":"a"},{"id":1,"label":"AP - Invoices"},{"id":79,"label":"AP - Supplier Reports"}]],["AR",[{"id":2,"label":"AR - Customers"},{"id":3,"label":"AR - Interface Lines"},{"id":4,"label":"AR - Transactions"}]]]';

$(document).ready(function() {
  var cat_id = $.parseJSON(output);
  console.debug(cat_id);
  $('#cat_id').empty();
  $.each(cat_id, function(index, data) {
    console.debug(data);
    var grpData = "<optgroup label=" + data[0] + ">";
    $.each(data[1], function(index2, subData) {
      grpData += "<option value="+subData.id+">"+data[0] +" - "+subData.label+"</option>";
    });
    grpData += "</optgroup>";
    $('#cat_id').append(grpData);
  });
});