Create Nested Array From Database Self Referencing Table And Show in Nested list In Smarty Template

Question

I am getting the rows from database and i am trying to generate an array of the parent categories and its children categories (children can also have children), so that i can pass that array data to my smarty template view and can render check boxes with each category and sub category.

Table Categories:

id |      name         | parent_id
 1 |  Electronics      |   0
 2 |  Mobile           |   1
 3 |  iPhone           |   2
 4 |  Men              |   0
 5 |  Shirt            |   4

I want the result in such way that those categories with parent_id = 0 becomes parent and show nested children below it: like this below

Array ( 
    [0] => Array (
            [id] => 1 
            [name] => Electronics 
            [children] => Array (
               [0] => Array (
                        [id] => 2
                        [name] => Mobile
                        [children] => Array (
                                      [0] => Array (
                                             [id] => 3
                                             [name] => iPhone
                                             )
                                      )
                       )
             )
    [1] => Array (
            [id] => 4 
            [name] => Men 
            [children] => Array (
               [0] => Array (
                        [id] => 5
                        [name] => Shirt
                       )
             )
 )  

And this is the code i have written so far:

$parentCat = Shopware()->Db()->query("SELECT * FROM `categories`");
    $cats = [];
    foreach ($parentCat->fetchAll() as $key => $value) {
        $prevId = $value['parent_id'];
        if ($prevId == 0) {
            $data = [
                'id' => $value['id'],
                'name' => $value['name']
            ];
            array_push($cats, $data);
        } else {
            foreach ($cats as $cat) {
                if($value['parent_id'] == $cat['id']) {
                    $childData = [
                        'id' => $value['id'],
                        'name' => $value['name'],
                    ];
                    array_push($cats,$childData);

                }
            }
        }
    }
    print_r($cats);
    die();

The Result I am getting is like this:

Array
(
[0] => Array
    (
        [id] => 1
        [name] => Electronics
    )

[1] => Array
    (
        [id] => 2
        [name] => Mobile
    )

[2] => Array
    (
        [id] => 3
        [name] => iPhone
    )

[3] => Array
    (
        [id] => 4
        [name] => Cars
    )

[4] => Array
    (
        [id] => 5
        [name] => Toyota
    )

)

Please help.

Answer 1

You can use recursive function like this:

function addToArr(&$arr, $data) {
    if ($data["parent_id"] == 0)
        return $arr[] =  ["id" => $data["id"], "name" => $data["name"], "children"=> []];
    foreach($arr as &$e) {
        if ($e["id"] == $data["parent_id"]) { // if found add as child
            $e["children"][] = ["id" => $data["id"], "name" => $data["name"], "children"=> []];
            break;
        }
        addToArr($e["children"], $data); // call recursively 
    }
}

Live example: 3v4l

Edit

To have HTML tag:

function getHTMLformat($arr) {
    if (!$arr) return "";
    $str = "<ul>" . PHP_EOL; // starting the list 
    foreach ($arr as $e) {
        $str .= "<li>" . $e["name"] . getHTMLformat($e["children"]) . "</li>" . PHP_EOL;
    }
    return $str . "</ul>" . PHP_EOL; // end list
}

echo getHTMLformat($res);