'How to create JSON nested child parent tree with PHP and MySQL(PDO method)
I am trying to build nested parent child JSON tree using PHP and MySQL.
My aim is to create a JSON tree from my MySQL DB and display a tree in front-end using AngularJS. Creating tree is important.
My DB Structure is :
╔═══════╦═══════════════════╦═════════╗ ║ id ║ name ║parent_id║ ╠═══════╬═══════════════════╬═════════╣ ║ 1 ║ Parent ║ 0 ║ ║ 2 ║ Child-1 ║ 1 ║ ║ 3 ║ Child-2 ║ 1 ║ ║ 4 ║ Grand Child-1 ║ 2 ║ ║ 5 ║ Grand Child-2 ║ 2 ║ ║ 6 ║ Grand Child-3 ║ 3 ║ ║ 7 ║ Grand Child-4 ║ 3 ║ ╚═══════╩═══════════════════╩═════════╝
I need the tree to look like:
Parent
|--Child-1
| |--Grand Child-1
| |_ Grand Child-2
|--Child-2
| |--Grand Child-3
| |_ Grand Child-4
And i have made something like this :
function hasChild($id){
$sql = "SELECT count(*) FROM `myTable` WHERE parent_id=".$id;
$stmt = $this->db->prepare($sql);
$stmt->execute($a);
$row = $stmt->fetchAll(PDO::FETCH_ASSOC);
return $row[0] > 0 ? true : false;
}//function hasChild($id)
// create an index on id
$index = array();
foreach($rows as $i =>$row)
{
if (hasChild($i)) {
$index[$row['id']] = $row;
}
}
// build the tree
foreach($index as $id => $indexRow)
{
if ($id === 1) continue;
$parent = $indexRow['parent_id'];
$index[$parent]['children'][] = $indexRow;
}
unset($indexRow);
echo json_encode($index);
But it's clearly not giving me correct json tree :(
I've looked at nesting json and array solutions and something is just not clicking for me, so I was hoping someone could help me out with this. I'm fine with using another way, as long as i can have the same/similar functionality.
Hopefully I was able to portray the situation well enough, but let me know if you need more data.
Thanks in advance!
Solution 1:[1]
I had the same problem since few days so therefore found another way to solve this
POSTMAN RESPONSE EARLIER
{
"Status": 200,
"Data": {
"MyAssetsList": [
{
"AssetsId": 15,
"UserCredentialId": 7,
"NickName": "jals",
"EquipmentId": "BO_15",
"EquipmentTypeId": 4,
"EquipmentTypeValue": "Backhoe",
"MakeId": 1,
"OEMName": "JCB",
"ModelId": 1,
"ModelName": "EX1235",
"YearOfPurchase": 2022,
"OtherOEMName": "rw",
"OtherModelName": 0,
"MetricId": 1,
"Capacity": "1.000 Kg",
"EquipmentAge": "2 Months",
"ProcessCompletionPercentage": 100
},
{
"AssetsId": 14,
"UserCredentialId": 7,
"NickName": "jal",
"EquipmentId": "",
"EquipmentTypeId": 4,
"EquipmentTypeValue": "Backhoe",
"MakeId": 1,
"OEMName": "JCB",
"ModelId": 1,
"ModelName": "EX1235",
"YearOfPurchase": 2022,
"OtherOEMName": "rw",
"OtherModelName": 0,
"MetricId": 1,
"Capacity": "1.000 Kg",
"EquipmentAge": "2 Months",
"ProcessCompletionPercentage": 33.33
},
{
"AssetsId": 13,
"UserCredentialId": 7,
"NickName": "rest",
"EquipmentId": "",
"EquipmentTypeId": 4,
"EquipmentTypeValue": "Backhoe",
"MakeId": 1,
"OEMName": "JCB",
"ModelId": 1,
"ModelName": "EX1235",
"YearOfPurchase": 2022,
"OtherOEMName": "rw",
"OtherModelName": 0,
"MetricId": 1,
"Capacity": "1.000 Kg",
"EquipmentAge": "2 Months",
"ProcessCompletionPercentage": 33.33
},
{
"AssetsId": 11,
"UserCredentialId": 7,
"NickName": "test",
"EquipmentId": "",
"EquipmentTypeId": 1,
"EquipmentTypeValue": "Excavator",
"MakeId": 1,
"OEMName": "JCB",
"ModelId": 1,
"ModelName": "EX1235",
"YearOfPurchase": 2022,
"OtherOEMName": 1,
"OtherModelName": 1,
"MetricId": 1,
"Capacity": "1.000 Kg",
"EquipmentAge": "2 Months",
"ProcessCompletionPercentage": 33.33
},
]
},
"Message": {
"SuccessMessage": "My Asset Found Successfully"
}
POSTMAN RESPONSE PRETTY AFTER SOLVING TREE STRUCTURE ISSUE
{
"Status": 200,
"Data": {
"MyAssets": {
"AssetsId: 15": {
"AssetId": 15,
"EquipmentType": {
"EquipmentTypeId": 4,
"EquipmentTypeValue": "Backhoe",
"EquipmentMake": {
"EquipmentMakeId": 1,
"EquipmentMakeValue": "JCB"
},
"EquipmentModel": {
"EquipmentModelId": 1,
"EquipmentModelValue": "EX1235"
},
"EquipmentYearOfPurchase": 2022,
"EquipmentAge": "2 Months",
"OtherOEMName": "rw",
"OtherModelName": "rw",
"MetricType": {
"MetricId": 1,
"Capacity": "1.000 Kg"
},
"ProcessCompletionPercentage": 100
}
},
"AssetsId: 14": {
"AssetId": 14,
"EquipmentType": {
"EquipmentTypeId": 4,
"EquipmentTypeValue": "Backhoe",
"EquipmentMake": {
"EquipmentMakeId": 1,
"EquipmentMakeValue": "JCB"
},
"EquipmentModel": {
"EquipmentModelId": 1,
"EquipmentModelValue": "EX1235"
},
"EquipmentYearOfPurchase": 2022,
"EquipmentAge": "2 Months",
"OtherOEMName": "rw",
"OtherModelName": "rw",
"MetricType": {
"MetricId": 1,
"Capacity": "1.000 Kg"
},
"ProcessCompletionPercentage": 33.33
}
},
"AssetsId: 13": {
"AssetId": 13,
"EquipmentType": {
"EquipmentTypeId": 4,
"EquipmentTypeValue": "Backhoe",
"EquipmentMake": {
"EquipmentMakeId": 1,
"EquipmentMakeValue": "JCB"
},
"EquipmentModel": {
"EquipmentModelId": 1,
"EquipmentModelValue": "EX1235"
},
"EquipmentYearOfPurchase": 2022,
"EquipmentAge": "2 Months",
"OtherOEMName": "rw",
"OtherModelName": "rw",
"MetricType": {
"MetricId": 1,
"Capacity": "1.000 Kg"
},
"ProcessCompletionPercentage": 33.33
}
},
"AssetsId: 11": {
"AssetId": 11,
"EquipmentType": {
"EquipmentTypeId": 1,
"EquipmentTypeValue": "Excavator",
"EquipmentMake": {
"EquipmentMakeId": 1,
"EquipmentMakeValue": "JCB"
},
"EquipmentModel": {
"EquipmentModelId": 1,
"EquipmentModelValue": "EX1235"
},
"EquipmentYearOfPurchase": 2022,
"EquipmentAge": "2 Months",
"OtherOEMName": 1,
"OtherModelName": 1,
"MetricType": {
"MetricId": 1,
"Capacity": "1.000 Kg"
},
"ProcessCompletionPercentage": 33.33
}
},
},
"AssetImageFileReference": "https://localhost/e9/FileServer/AssetsImage/",
"AssetDocumentFileReference": "https://localhost/e9/FileServer/AssetsDocument/"
},
"Message": {
"SuccessMessage": "My Asset Found Successfully"
}
PHP CODE TO CREATE TREE STRUCTURE LIKE ABOVE IS
$A = array();
$Data->MyAssetsList = $ResultModel; // ResultModel Has query result of mysql
foreach($ResultModel as $R)
{
$A['MyAssets']["AssetsId: ".$R->AssetsId]['AssetId']= $R->AssetsId;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["EquipmentTypeId"]= $R->EquipmentTypeId;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["EquipmentTypeValue"]= $R->EquipmentTypeValue;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["EquipmentMake"]['EquipmentMakeId']= $R->MakeId;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["EquipmentMake"]['EquipmentMakeValue']= $R->OEMName;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["EquipmentModel"]['EquipmentModelId']= $R->ModelId;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["EquipmentModel"]['EquipmentModelValue']= $R->ModelName;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["EquipmentYearOfPurchase"]= $R->YearOfPurchase;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["EquipmentAge"]= $R->EquipmentAge;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["OtherOEMName"]= $R->OtherOEMName;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["OtherModelName"]= $R->OtherOEMName;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["MetricType"]['MetricId']= $R->MetricId;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["MetricType"]['Capacity']= $R->Capacity;
$A['MyAssets']["AssetsId: ".$R->AssetsId]['EquipmentType']["ProcessCompletionPercentage"]= $R->ProcessCompletionPercentage;
}
$A['AssetImageFileReference'] = base_url(PATH_TO_FILESERVER_ASSETSIMAGE);
$A['AssetDocumentFileReference'] = base_url(PATH_TO_FILESERVER_ASSETSDOCUMENT);
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | sudo-bhavin |