Generate nested the same structure table in Json script by using php and mysql

Question

My problem with json and php: How to generate something like below part of json script table contain another Table with the same structure nested in it? I want to generate that from php code using mysql. I have searched too in web, but I didn't find any solution. I just found like this in postgre database by using "WITH" clause that doesn't support in mysql yet in this link

{
  "categories": [
    {
      "id": 1,
      "name": "Electronic",
      "children": [
        {
          "id": 11,
          "name": "Computer",
          "children": [
            {
              "id": 111,
              "name": "Desktop",
              "children": []
            },
            {
              "id": 112,
              "name": "Labtop",
              "children": []
            }
          ]
        },
        {
          "id": 12,
          "name": "Mobile",
          "children": [
            {
              "id": 121,
              "name": "Phone",
              "children": []
            },
            {
              "id": 122,
              "name": "Tablet",
              "children": []
            }
          ]
        }
      ]
    }
  ]
}

Answer 1

If I understood you correctly, what you want to do is convert array result from SQL to JSON form of tree-like structure

Assuming that your target object structure looks like this:

[
   "id"=>10,
   "name"=>"Computer",
   "children"=[]
]

And what your result array from SQL should look like in php is this:

[
   "id"=>12,
   "name"=>"Laptop",
   "parentId"=>10
]

You could then try to write some php functions (or repository methods) doing the job for you.

<?php
function arrayRemove(&$array,&$object){
    if (($key = array_search($object, $array)) !== false) {
    unset($array[$key]);
    }
}
function findRoots(&$array,$baseRoot){
    $roots=[];
    foreach($array as &$element)
    {
        $cpy=null;
        if(
            ($baseRoot==null&&$element['parentId']==null)||
            ($baseRoot!==null&&$element['parentId']==$baseRoot['id'])
        ){
            $cpy=$element;
            arrayRemove($array,$element);
            array_push($roots,[
                'id'=>$cpy["id"],
                'name'=>$cpy["name"],
                'children'=>findRoots($array,$cpy)
                ]);
        }
    }
    return $roots;
}
function foo($array){
    $object=["categories"=>[]];
    $roots=findRoots($array,null);
    $object["categories"]=$roots;
    return json_encode($object);
}
echo foo([
    ['id'=>1,'name'=>'A','parentId'=>null],
    ['id'=>2,'name'=>'bb','parentId'=>1],
    ['id'=>3,'name'=>'ccc','parentId'=>2],
    ['id'=>4,'name'=>'DDDD','parentId'=>1]
    ]);
?>

The result of the above code should be this:

{
    "categories":[
        {
            "id":1,
            "name":"A",
            "children":[
                {
                    "id":2,
                    "name":"bb",
                    "children":[
                        {
                            "id":3,
                            "name":"ccc",
                            "children":[]
                        }
                    ]
                },
                {
                     "id":4,
                     "name":"DDDD",
                     "children":[]
                }
            ]
        }
    ]
}

As Diogo Sgrillo mentioned, you could then create a table in your DB containing id, name, and parentId fields. Let's assume id and parentId are int(11) and name is varchar(255), and parentId is foreign key to categories:id. phpMyAdmin generated following SQL for table:

CREATE TABLE `categories` (
  `id` int(11) NOT NULL,
  `name` varchar(255) COLLATE utf8_bin NOT NULL,
  `parentId` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
ALTER TABLE `categories`
  ADD PRIMARY KEY (`id`),
  ADD KEY `parentId` (`parentId`);
ALTER TABLE `categories`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
ALTER TABLE `categories`
  ADD CONSTRAINT `parent_fk` FOREIGN KEY (`parentId`) REFERENCES `categories` (`id`) ON DELETE SET NULL ON UPDATE CASCADE;

If you want to reverse the process, simply write a function taking parentId and array of parent's children:

function reversedProcess(&$children,$parentId)
{
    $array=[];
    foreach($children as $child)
    {
        array_push($array,['id'=>$child['id'],'name'=>$child['name'],'parentId'=>$parentId]);
        $array=array_merge($array,reversedProcess($child['children'],$child['id']));
    }
    return $array;
}

Answer 2

Thanks my friend Szymon D , Your answer help me very much and work fine with me, This is simple adding to your code to work mysql:

$db = new PDO('mysql:host=localhost;dbname=tree;charset=utf8mb4', 'root', '');
echo foo($db->query('SELECT * FROM category')->fetchAll(PDO::FETCH_ASSOC));