Build a Tree from a Multi-Dimensional PHP Array using mysql results

Question

I would like to create a function that work recursively to generate tree view from multi dimensional array with unknown levels and unknown count of elements

here is the db file

--
-- Table structure for table `accounts_tree`
--

CREATE TABLE `accounts_tree` (
  `id` bigint(20) NOT NULL,
  `parent_id` bigint(20) DEFAULT '0',
  `final_acc_id` bigint(20) NOT NULL,
  `Code` varchar(255) DEFAULT NULL,
  `name_a` varchar(255) DEFAULT NULL,
  `name_e` varchar(255) DEFAULT NULL,
  `nature` tinyint(1) DEFAULT '0',
  `currency_id` bigint(20) NOT NULL DEFAULT '0',
  `currency_rate` varchar(200) DEFAULT NULL,
  `match_date` date DEFAULT NULL,
  `notes` text,
  `created` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `accounts_tree`
--

INSERT INTO `accounts_tree` (`id`, `parent_id`, `final_acc_id`, `Code`, `name_a`, `name_e`, `nature`, `currency_id`, `currency_rate`, `match_date`, `notes`, `created`) VALUES
(1, 0, 1, '1', 'folder 1', 'budget', 0, 1, '1', NULL, NULL, '2017-12-16 00:00:00'),
(2, 0, 1, '1', 'folder 2', 'budget2', 0, 1, '1', NULL, NULL, '2017-12-16 00:00:00'),
(3, 1, 1, '1', 'sub 1-1', 'balance sheet', 0, 1, '1', NULL, NULL, '2017-12-16 00:00:00'),
(4, 2, 1, '1', 'sub 2-1', 'balance sheet', 0, 1, '1', NULL, NULL, '2017-12-16 00:00:00'),
(5, 3, 1, '1', 'Sub 1-1-1', 'balance sheet', 0, 1, '1', NULL, NULL, '2017-12-16 00:00:00'),
(6, 0, 1, '3', 'folder 3', 'balance sheet', 0, 1, '1', NULL, NULL, '2017-12-16 00:00:00'),
(7, 5, 1, '3', 'sub 1-1-1-1', 'balance sheet', 0, 1, '1', NULL, NULL, '2017-12-16 00:00:00');

PHP work to generate the array

$query = "SELECT * FROM `accounts_tree`";
$result = $db->query($query);
$output = array();
while ($row = $db->fetch_assoc($result)) {
    $sub_data["id"] = $row["id"];
    $sub_data["name"] = $row["name_a"];
    $sub_data["parent_id"] = $row["parent_id"];
    $data[] = $sub_data;
}
foreach ($data as $key => &$value) {
    $output[$value["id"]] = &$value;
}
foreach ($data as $key => &$value) {
    if ($value["parent_id"] && isset($output[$value["parent_id"]])) {
        $output[$value["parent_id"]]["nodes"][] = &$value;
    }
}
foreach ($data as $key => & $value ) {
    if ($value["parent_id"] && isset($output[$value["parent_id"]])) {
        unset($data[$key]);
    }
}

EDITED

The output array will be

Array
(
    [0] => Array
        (
            [id] => 1,
            [name] => "folder 1",
            [parent_id] => 0,
            [nodes] => Array
                (
                    [0] => Array
                        (
                            [id] => 3,
                            [name] => "sub 1-1",
                            [parent_id] => 1,
                            [nodes] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 5,
                                            [name] => "Sub 1-1-1",
                                            [parent_id] => 3,
                                            [nodes] => Array
                                                (
                                                    [0] => Array
                                                        (
                                                            [id] => 7,
                                                            [name] => "sub1-1-1-1",
                                                            [parent_id] => 5
                                                        )

                                                )

                                        )

                                )

                        )

                )

        ),

    [1] => Array
        (
            [id] => 2,
            [name] => "folder 2",
            [parent_id] => 0,
            [nodes] => Array
                (
                    [0] => Array
                        (
                            [id] => 4,
                            [name] => "sub 2-1",
                            [parent_id] => 2
                        )

                )

        ),

    [5] => Array
        (
            [id] => 6,
            [name] => "folder 3",
            [parent_id] => 0
        )
    );

we need to collect names only and put it in tree view please any one can solve this :S

thanks

A database table returns rows. Rows are not suited to holding multidimensional data. A more logical approach would be to create several tables which you can join together using a common value. — miknik
– miknik, Commented Dec 16, 2017 at 17:25
@miknik I've generated the multidimensional array with PHP not from database — M.M
– M.M, Commented Dec 16, 2017 at 17:57
@mickmackusa the code you wrote is good but you did not understood my question will maybe I did't explain it well but you did it well thanks — M.M
– M.M, Commented Dec 25, 2017 at 15:29
I wanted to deal with out to be sorted in ul its li with the names — M.M
– M.M, Commented Dec 25, 2017 at 15:31

mickmackusa · Accepted Answer · 2017-12-17 13:51:32Z

Explanations can be found as inline comments. This function provides your exact desired output. Also pay attention to the query that I've rewritten to set up $resultset.

Code: (Demo)

function findParent(&$array,$parentid=0,$childarray=[]){  // make $array modifiable
    foreach($array as $i=>&$row){                         // make $row modifiable
        if($parentid){                                    // if not zero
            if($row['id']==$parentid){                    // found parent
                $row['nodes'][]=$childarray;              // append child to parent's nodes subarray
            }elseif(isset($row['nodes'])){                // go down rabbit hole looking for parent
                findParent($row['nodes'],$parentid,$childarray);  // look deeper for parent while preserving the initial parent_id and row
            }                                             // else continue;
        }elseif($row['parent_id']){                       // child requires adoption
            unset($array[$i]);                            // remove child from level because it will be store elsewhere and won't be its own parent (reduce iterations in next loop & avoid infinite recursion)
            findParent($array,$row['parent_id'],$row);    // look for parent using parent_id while carrying the entire row as the childarray
        }                                                 // else continue;
    }
    return $array;                                        // return the modified array
}


// $db->query('SELECT id,name_a AS name,parent_id FROM accounts_tree ORDER BY id');
// for($resultset=[]; $row=$res->fetch_assoc(); $resultset[]=$row);  // inspired by: http://php.net/manual/en/mysqli-result.fetch-assoc.php#112924

$resultset=[
    ['id'=>1,'name'=>'folder 1','parent_id'=>0],
    ['id'=>2,'name'=>'folder 2','parent_id'=>0],
    ['id'=>3,'name'=>'sub 1-1','parent_id'=>1],
    ['id'=>4,'name'=>'sub 2-1','parent_id'=>2],
    ['id'=>5,'name'=>'Sub 1-1-1','parent_id'=>3],
    ['id'=>6,'name'=>'folder 3','parent_id'=>0],
    ['id'=>7,'name'=>'sub 1-1-1-1','parent_id'=>5]
];

print_r(findParent($resultset));

Output:

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => folder 1
            [parent_id] => 0
            [nodes] => Array
                (
                    [0] => Array
                        (
                            [id] => 3
                            [name] => sub 1-1
                            [parent_id] => 1
                            [nodes] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 5
                                            [name] => Sub 1-1-1
                                            [parent_id] => 3
                                            [nodes] => Array
                                                (
                                                    [0] => Array
                                                        (
                                                            [id] => 7
                                                            [name] => sub 1-1-1-1
                                                            [parent_id] => 5
                                                        )

                                                )

                                        )

                                )

                        )

                )

        )

    [1] => Array
        (
            [id] => 2
            [name] => folder 2
            [parent_id] => 0
            [nodes] => Array
                (
                    [0] => Array
                        (
                            [id] => 4
                            [name] => sub 2-1
                            [parent_id] => 2
                        )

                )

        )

    [5] => Array
        (
            [id] => 6
            [name] => folder 3
            [parent_id] => 0
        )

)

Collectives™ on Stack Overflow

Build a Tree from a Multi-Dimensional PHP Array using mysql results

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related