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