How to create JSON nested child parent tree with PHP and MySQL(PDO method)

Question

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!

chris85 · Accepted Answer · 2015-10-25 05:03:42Z

1

$a is undefined. You also aren't using any placeholders in your query so I think that would fail out.

Try:

function hasChild($id){
    $sql = "SELECT count(*) as da_count FROM `myTable` WHERE parent_id = ?";
    $stmt = $this->db->prepare($sql);
    $stmt->execute(array($id));
    $row = $stmt->fetch(PDO::FETCH_ASSOC);
    return $row['da_count'] > 0 ? true : false;
}//function hasChild($id)

A longer write up on prepared statements: http://php.net/manual/en/pdo.prepared-statements.php.

edited Oct 25, 2015 at 5:03

answered Oct 25, 2015 at 2:48

chris85

23.9k7 gold badges36 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Joomler Over a year ago

thanks for your response mate, but i wasnt using prepared statement so i just defined $a = array(); and i changed my code according to your suggestion but still its not working :(

chris85 Over a year ago

You tried my code or you tried with an empty array? You are using prepared statements just not parameterized queries.

Joomler Over a year ago

sorry i forgot to update you. yes your code worked, and the issue was occurring due to slim framework. thank you very much.

bhavin · Accepted Answer · 2022-02-09 15:47:29Z

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);

Collectives™ on Stack Overflow

How to create JSON nested child parent tree with PHP and MySQL(PDO method)

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related