How to get nested json object with php mysql

Question

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 1

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

Answer 2

Approximately like this - but it's not quite right (I'm hopelessly bad at transforming arrays, so I'll leave it for someone else to tweak that bit)...

<?php

require('path/to/connection/stateme.nts');

$query = "
SELECT x.id
     , x.category
     , y.id sub_id
     , y.category sub_category
  FROM my_table x
  LEFT
  JOIN my_table y
    ON y.parent = x.id
 WHERE x.parent IS NULL
 ORDER
    BY id,sub_id;
";

$result = mysqli_query($db,$query);

$my_array = array();

while($row = mysqli_fetch_assoc($result)){
$my_array[] = $row;
}

$new_array=array();

foreach($my_array as $row){
    $new_array[$row['id']][$row['category']][] = $row['id']['category'];
    };  //this bit needs fixing

$json = json_encode($new_array);
?>