递归函数从数据库结果生成多维数组

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.

我正在寻找一个函数,它接受一个页面/类别数组(来自平面数据库结果),并根据父ID生成一个嵌套页面/类别项的数组。我想以递归方式执行此操作,以便可以完成任何级别的嵌套。

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.

我已经查看并尝试了几乎我遇到过的所有解决方案(Stack Overflow上有很多这样的解决方案,但是没有运气得到足够通用的东西,可以用于页面和类别。

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

3 个解决方案

#1

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

获取所有元素的数组和当前父元素的id(最初为0 / nothing / null / whatever)。

Loop through all elements.

遍历所有元素。

If the parent_id of 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).

如果元素的parent_id与您在1中获得的当前父ID匹配,则该元素是父元素的子元素。把它放在当前孩子的列表中(这里:$ 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 children element.

使用您刚刚在3中标识的元素的id递归调用该函数,即查找该元素的所有子元素,并将它们添加为子元素。

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.

换句话说,此函数的一次执行返回一个元素列表,这些元素是给定父ID的子元素。使用buildTree($ myArray,1)调用它,它将返回父ID为1的元素列表。最初,在父id为0的情况下调用此函数,因此返回没有父id的元素,即根节点。该函数以递归方式调用自身以查找子项的子项。

#2

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:

我知道这个问题已经过时了,但我遇到了一个非常类似的问题 - 除非有大量的数据。经过一番努力,我设法在结果集的一次传递中构建树 - 使用引用。这段代码并不漂亮,但它的工作原理非常快。它是非递归的 - 也就是说,结果集上只有一个传递,最后是一个array_filter:

$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:

最后一个print_r生成此输出:

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.

这正是我想要的。

#3

It is possible to use php to get the mysql result into array and then use it.

可以使用php将mysql结果导入数组然后使用它。

$categoryArr = Array();while($categoryRow = mysql_fetch_array($category_query_result)){    $categoryArr[] = array('parentid'=>$categoryRow['parent_id'],            'id'=>$categoryRow['id']);   }

#1