php 从数据库结果生成多维数组的递归函数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8587341/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Recursive function to generate multidimensional array from database result
提问by David Hemphill
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);
}
回答by deceze
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.
- 获取所有元素的数组和当前父级的 id（最初是
0/nothing/null/whatever）。 - 循环遍历所有元素。
- 如果
parent_id元素的 与您在 1. 中获得的当前父 ID 匹配，则该元素是父元素的子元素。把它放在你当前孩子的列表中（这里：）$branch。 - 使用您刚刚在 3. 中标识的元素的 id 递归调用该函数，即找到该元素的所有子元素，并将它们添加为
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 的元素，它们是根节点。该函数递归调用自身以查找子项的子项。
回答by Aleks G
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_filterat 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_rproduces 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.
这正是我一直在寻找的。
回答by mustafa
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']);
}
