pran. pran. - 2 months ago 14
MySQL Question

How to get nested json object with php mysql

I am trying to fetch parent categories and subcategories from same table. I'm using php rest api for the same.

This is my table.

id parent category
--------------------
1 | 0 | Fruits
2 | 0 | Cars
3 | 1 | Orange
4 | 1 | Apple
5 | 0 | Books
6 | 2 | Benz
7 | 5 | Comics
8 | 2 | Honda
9 | 5 | Fantasy


How could I get the following json with php/mysql query.

{
"categories": [{
"id": "1",
"category": "Fruits",
"subCat": [{
"id": "3",
"category": "Orange"
}, {
"id": "4",
"category": "Apple"
}]
}, {
"id": "2",
"category": "Cars",
"subCat": [{
"id": "6",
"category": "Benz"
}, {
"id": "8",
"category": "Honda"
}]

}, {
"id": "5",
"category": "Books",
"subCat": [{
"id": "7",
"category": "Comics"
}, {
"id": "9",
"category": "Fantasy"
}]

}

]
}


Thanks for any help.

Answer

I think this will solve your issue

    function buildTree(array $elements, $parentId = 0)  {
    $branch = array();
    foreach($elements as $element)
        {
        if ($element['parent'] == $parentId)
            {
            $children = buildTree($elements, $element['id']);
            if ($children)
                {
                $element['subCat'] = $children;
                }

            $branch[] = $element;
            }
        }

    return $branch;
    }

$query1 = mysql_query("select * from table");

while ($rlt2 = mysql_fetch_array($query1, MYSQL_ASSOC))
    {
    $child[] = $rlt2;
    }

$tree['categories'] = buildTree($child);
$this->response($this->json($tree) , 200);