I'm having two tables of data "Item" and "Subsidiary" with the following structure:
ITEM ItmCod ItmName
SUBSIDIARY ItmCodParent ItmCodChild
I need to show a list of Items each with a list of its subsidiaries, like in this json:
{
"ItmCod":1,
"ItmName":"BogusItem1",
"Subsidiaries":
[
{
"ItmCodParent":1,
"ItmCodChild":15
},{
"ItmCodParent":1,
"ItmCodChild":16
}
]
},{
"ItmCod":2,
"ItmName":"BogusItem2",
"Subsidiaries":
[
{
"ItmCodParent":2,
"ItmCodChild":17
},{
"ItmCodParent":2,
"ItmCodChild":18
}
]
}
How can I add the second result set to the first one to have the nested as shown above. I have this code so far:
$sql = "SELECT ItmCod, ItmName FROM item";
$item_rows = array();
while($item_row = $database->fetch_array_assoc($item_result)){
$sub_sql = "SELECT ItmCodParent, ItmCodChild FROM subsidiary WHERE subsidiary.ItmCodParent = " . $item_row["ItmCod"];
$sub_result = $database->query($sub_sql);
$sub_rows = array();
while($sub_row = $database->fetch_array_assoc($sub_result)){
$sub_rows[] = $sub_row;
}
$item_rows[] = $item_row;
}
print json_encode($item_rows);
Thanks.