Recursive function to generate multidimensional array from database result
PhpArraysFunctionRecursionPhp Problem Overview
I'm looking to write a function that takes an array of pages/categories (from a flat database result) and generates an array of nested page/category items based on the parent ids. I would like to do this recursively, so that any level of nesting can be done.
For example: I'm fetching all the pages in one query, and this is the what the database table looks like
+-------+---------------+---------------------------+
| id | parent_id | title |
+-------+---------------+---------------------------+
| 1 | 0 | Parent Page |
| 2 | 1 | Sub Page |
| 3 | 2 | Sub Sub Page |
| 4 | 0 | Another Parent Page |
+-------+---------------+---------------------------+
And this is the array I would like to end up with to process in my view files:
Array
(
[0] => Array
(
[id] => 1
[parent_id] => 0
[title] => Parent Page
[children] => Array
(
[0] => Array
(
[id] => 2
[parent_id] => 1
[title] => Sub Page
[children] => Array
(
[0] => Array
(
[id] => 3
[parent_id] => 1
[title] => Sub Sub Page
)
)
)
)
)
[1] => Array
(
[id] => 4
[parent_id] => 0
[title] => Another Parent Page
)
)
I've looked and tried nearly every solution I've come across (there's a lot of them here on Stack Overflow, but have had no luck getting something generic enough that will work for both pages and categories.
Here's the closest I've gotten, but it doesn't work because I'm assigning the children to the first level parent.
function page_walk($array, $parent_id = FALSE)
{
$organized_pages = array();
$children = array();
foreach($array as $index => $page)
{
if ( $page['parent_id'] == 0) // No, just spit it out and you're done
{
$organized_pages[$index] = $page;
}
else // If it does,
{
$organized_pages[$parent_id]['children'][$page['id']] = $this->page_walk($page, $parent_id);
}
}
return $organized_pages;
}
function page_list($array)
{
$fakepages = array();
$fakepages[0] = array('id' => 1, 'parent_id' => 0, 'title' => 'Parent Page');
$fakepages[1] = array('id' => 2, 'parent_id' => 1, 'title' => 'Sub Page');
$fakepages[2] = array('id' => 3, 'parent_id' => 2, 'title' => 'Sub Sub Page');
$fakepages[3] = array('id' => 4, 'parent_id' => 3, 'title' => 'Another Parent Page');
$pages = $this->page_walk($fakepages, 0);
print_r($pages);
}
Php Solutions
Solution 1 - Php
Some very simple, generic tree building:
function buildTree(array $elements, $parentId = 0) {
$branch = array();
foreach ($elements as $element) {
if ($element['parent_id'] == $parentId) {
$children = buildTree($elements, $element['id']);
if ($children) {
$element['children'] = $children;
}
$branch[] = $element;
}
}
return $branch;
}
$tree = buildTree($rows);
The algorithm is pretty simple:
- Take the array of all elements and the id of the current parent (initially
0/nothing/null/whatever). - Loop through all elements.
- If the
parent_idof an element matches the current parent id you got in 1., the element is a child of the parent. Put it in your list of current children (here:$branch). - Call the function recursively with the id of the element you have just identified in 3., i.e. find all children of that element, and add them as
childrenelement. - Return your list of found children.
In other words, one execution of this function returns a list of elements which are children of the given parent id. Call it with buildTree($myArray, 1), it will return a list of elements which have the parent id 1. Initially this function is called with the parent id being 0, so elements without parent id are returned, which are root nodes. The function calls itself recursively to find children of children.
Solution 2 - Php
I know this question is old, but I Was facing a very similar problem - except with a very large amount of data. After some struggle, I managed to build the tree in one pass of the resultset - using references. This code is not pretty, but it works and it works quite fast. It's non-recursive - that is, there's only one pass over the resultset and then one array_filter at the end:
$dbh = new PDO(CONNECT_STRING, USERNAME, PASSWORD);
$dbs = $dbh->query("SELECT n_id, n_parent_id from test_table order by n_parent_id, n_id");
$elems = array();
while(($row = $dbs->fetch(PDO::FETCH_ASSOC)) !== FALSE) {
$row['children'] = array();
$vn = "row" . $row['n_id'];
${$vn} = $row;
if(!is_null($row['n_parent_id'])) {
$vp = "parent" . $row['n_parent_id'];
if(isset($data[$row['n_parent_id']])) {
${$vp} = $data[$row['n_parent_id']];
}
else {
${$vp} = array('n_id' => $row['n_parent_id'], 'n_parent_id' => null, 'children' => array());
$data[$row['n_parent_id']] = &${$vp};
}
${$vp}['children'][] = &${$vn};
$data[$row['n_parent_id']] = ${$vp};
}
$data[$row['n_id']] = &${$vn};
}
$dbs->closeCursor();
$result = array_filter($data, function($elem) { return is_null($elem['n_parent_id']); });
print_r($result);
When executed on this data:
mysql> select * from test_table;
+------+-------------+
| n_id | n_parent_id |
+------+-------------+
| 1 | NULL |
| 2 | NULL |
| 3 | 1 |
| 4 | 1 |
| 5 | 2 |
| 6 | 2 |
| 7 | 5 |
| 8 | 5 |
+------+-------------+
The last print_r produces this output:
Array
(
[1] => Array
(
[n_id] => 1
[n_parent_id] =>
[children] => Array
(
[3] => Array
(
[n_id] => 3
[n_parent_id] => 1
[children] => Array
(
)
)
[4] => Array
(
[n_id] => 4
[n_parent_id] => 1
[children] => Array
(
)
)
)
)
[2] => Array
(
[n_id] => 2
[n_parent_id] =>
[children] => Array
(
[5] => Array
(
[n_id] => 5
[n_parent_id] => 2
[children] => Array
(
[7] => Array
(
[n_id] => 7
[n_parent_id] => 5
[children] => Array
(
)
)
[8] => Array
(
[n_id] => 8
[n_parent_id] => 5
[children] => Array
(
)
)
)
)
[6] => Array
(
[n_id] => 6
[n_parent_id] => 2
[children] => Array
(
)
)
)
)
)
Which is exactly what I was looking for.
Solution 3 - Php
public function testTree(){
$array = [
['id'=>7,'parent_id'=>3],
['id'=>1,'parent_id'=>0],
['id'=>2,'parent_id'=>0],
['id'=>3,'parent_id'=>1],
['id'=>4,'parent_id'=>1],
['id'=>5,'parent_id'=>2],
['id'=>6,'parent_id'=>1],
['id'=>8,'parent_id'=>4],
['id'=>9,'parent_id'=>4],
['id'=>10,'parent_id'=>0]
];
$res = $this->buildTree($array);
print_r($res);
}
public function buildTree($array,$id_key = 'id',$parent_key = 'parent_id'){
$res = [];
foreach($array as $y){
$array_with_id[$y[$id_key]] = $y;
}
foreach($array_with_id as $key => $element){
if($element[$parent_key]){
$array_with_id[$element[$parent_key]]['childrens'][$key] = &$array_with_id[$key];
}else{
$res[$element[$id_key]] = &$array_with_id[$key];
}
}
return $res;
}
Too many operations are provided with recursive, I think it is a best way.
Solution 4 - Php
Taking inspiration from other answers here, I came up with my own version for grouping an array of assoc arrays recursively (to any arbitrary depth), by using list of custom functions to obtain grouping keys at each level.
Here's a simplified version of the original more complex variant (with more params for tweaking knobs). Note that it employs a simple iterative function groupByFn as a subroutine for performing grouping at individual levels.
/**
* - Groups a (non-associative) array items recursively, essentially converting it into a nested
* tree or JSON like structure. Inspiration taken from: https://stackoverflow.com/a/8587437/3679900
* OR
* - Converts an (non-associative) array of items into a multi-dimensional array by using series
* of callables $key_retrievers and recursion
*
* - This function is an extension to above 'groupByFn', which also groups array but only till 1 (depth) level
* (whereas this one does it till any number of depth levels by using recursion)
* - Check unit-tests to understand further
* @param array $data Array[mixed] (non-associative) array of items that has to be grouped / converted to
* multi-dimensional array
* @param array $key_retrievers Array[Callable[[mixed], int|string]]
* - A list of functions applied to item one-by-one, to determine which
* (key) bucket an item goes into at different levels
* OR
* - A list of callables each of which takes an item or input array as input and returns an int
* or string which is to be used as a (grouping) key for generating multi-dimensional array.
* @return array A nested assoc-array / multi-dimensional array generated by 'grouping' items of
* input $data array at different levels by application of $key_retrievers on them (one-by-one)
*/
public static function groupByFnRecursive(
array $data,
array $key_retrievers
): array {
// in following expression we are checking for array-length = 0 (and not nullability)
// why empty is better than count($arr) == 0 https://stackoverflow.com/a/2216159/3679900
if (empty($data)) {
// edge-case: if the input $data array is empty, return it unmodified (no need to check for other args)
return $data;
// in following expression we are checking for array-length = 0 (and not nullability)
// why empty is better than count($arr) == 0 https://stackoverflow.com/a/2216159/3679900
} elseif (empty($key_retrievers)) {
// base-case of recursion: when all 'grouping' / 'nesting' into multi-dimensional array has been done,
return $data;
} else {
// group the array by 1st key_retriever
$grouped_data = self::groupByFn($data, $key_retrievers[0]);
// remove 1st key_retriever from list
array_shift($key_retrievers);
// and then recurse into further levels
// note that here we are able to use array_map (and need not use array_walk) because array_map can preserve
// keys as told here:
// https://www.php.net/manual/en/function.array-map.php#refsect1-function.array-map-returnvalues
return array_map(
static function (array $item) use ($key_retrievers): array {
return self::groupByFnRecursive($item, $key_retrievers);
},
$grouped_data
);
}
}
Do checkout the gist for bigger collection of array utility functions along with unit-tests
Solution 5 - Php
It is possible to use php to get the mysql result into array and then use it.
$categoryArr = Array();
while($categoryRow = mysql_fetch_array($category_query_result)){
$categoryArr[] = array('parentid'=>$categoryRow['parent_id'],
'id'=>$categoryRow['id']);
}