获取所有子，孙子等节点在父下使用php与mysql查询结果 [英] Get all child, grandchild etc nodes under parent using php with mysql query results
问题描述
我的问题是我正在使用邻接列表数据模型以在mysql.I中生成我的层次结构数据。可以将表（见下文）检索到具有每个项目的关联数组的多维数组中。我想要做的是一旦我得到这个数组，我想获得一个父节点（包括父项）的所有节点（子，孙子等）的另一个数组。我只是不能锻炼如何编码tihs php。
在MySQL中，我的表格如下所示：
1电子产品0
2电视1
3便携式电子产品1
4管2
5 LCD 2
6等离子2
7 Mp3播放器3
8 CD播放器3
9 2路无线电3
10闪光7
$ b b
我可以使用这个代码将所有行记录到一个关联数组中。
$ r = mysql_query * FROM test）;
$ data = array（）;
while（$ row = mysql_fetch_assoc（$ r））{
$ data [] = $ row;
}
获取结果：
Array
（
[0] => Array
（
[id] => 1
[name ] =>电子邮件
[parent_id] => 0
）
[1] => Array
（
[id] => 2
[name] =>电视
[parent_id] => 1
）
[2] => Array
（
[id] => ; 3
[name] =>便携式电子设备
[parent_id] => 1
）
[3] => Array
[id] => 4
[name] => Tube
[parent_id] => 2
）
[4] =&
[id] => 5
[name] => LCD
[parent_id] => 2
）
[5] =& b $ b（
[id] => 6
[name] => Plasma
[parent_id] => 2
）
[6] > array
（
[id] => 7
[name] => Mp3玩家
[parent_id] => 3
）
[ 7] => Array
（
[id] => 8
[name] => CD播放器
[parent_id] => 3
）
[ 8] =>数组
（
[id] => 9
[name] => 2路无线电
[parent_id] => 3
）
[9] => Array
（
[id] => 10
[name] => Flash
[parent_id] => 7
）
）
有了这些结果，我想用ID过滤它。
例如，我想要一个关联数组的可移植电子的ID为3的每个节点。（使用id代码）
一个包含ids行的数组：
- 3便携式电子产品（必须包含所选父级）
- 7 Mp3玩家（儿童）
- 8张CD播放器（儿童）
- 9双向收音机（儿童）
- 10 Flash（Grand Child）
如果Flash有儿童，也会返回这些。
因此，最终结果将返回一个类似上面的数组。
请注意：
我不是在一个函数创建一个多维数组的树结构（已经有一个解决方案）。我想构建一个函数：fetch_recursive（$ id），它接收一个ID，并返回该级别的所有项目
希望这有助于
p>
编辑：
从你所给出的输出中获取一个三维数组，以及一种从特定数组中获取特定 id 的所有子元素的方法。我现在想出了如何直接从你的输出中检索子元素（而不必先通过 buildtree（）函数：
function fetch_recursive（$ src_arr，$ currentid，$ parentfound = false，$ cats = array（））
{
foreach（$ src_arr as $ row）
{
if（（！$ parentfound& $ amp;& $ row ['id'] == $ currentid）|| $ row ['parent_id'] == $ currentid）
{
$ rowdata = array（）;
foreach（$ row as $ k => $ v）
$ rowdata [$ k] = $ v;
cats [] = $ rowdata;
if（$ row ['parent_id'] == $ currentid）
$ cats = array_merge（$ cats，fetch_recursive（$ src_arr，$ row ['id' true））;
}
}
return $ cats;
}
b $ b
要使用上面的函数，只需将输出数组 $ data 传递给第一个参数和 id 您要从第二个参数中检索子元素：
ex。：
$ list = fetch_recursive（$ data，3）;
这将给你 id 3 （如此答案的最后一个代码框中的示例所示）。
原始答案：
我从来没有写过递归函数构建嵌套树从这个设计到现在。我确定有很多其他人写了类似的功能，但这一个应该肯定为你工作：
function buildtree （$ src_arr，$ parent_id = 0，$ tree = array（））
{
foreach（$ src_arr as $ idx => $ row）
{
if row ['parent_id'] == $ parent_id）
{
foreach（$ row as $ k => $ v）
$ tree [$ row ['id']] [$ k] = $ v;
unset（$ src_arr [$ idx]）;
$ tree [$ row ['id']] ['children'] = buildtree（$ src_arr，$ row ['id']）;
}
}
ksort（$ tree）;
return $ tree;
}
此函数将递归地从邻接表中构建一棵树，按升序排列。这也使得每个父/子的 id 是每个信息数组的键。
：
$ r = mysql_query（SELECT * FROM test）;
$ data = array（）;
while（$ row = mysql_fetch_assoc（$ r））{
$ data [] = $ row;
}
echo'< pre>';
print_r（buildtree（$ data））;
echo'< / pre>';
将输出如下：
Array
（
[1] => Array
（
[id] => 1
[name] >电子邮件
[parent_id] => 0
[children] => Array
（
[2] =& [id] => 2
[name] =>电视
[parent_id] => 1
[children] =& 4] => Array
（
[id] => 4
[name] => Tube
[parent_id] => 2
] => Array（）
）
[5] => Array
（
[id] => 5
[name] =>
[parent_id] => 2
[children] => Array（）
）
[6] => Array
（
[id] => 6
[name] => Plasma
[parent_id] => 2
[children] => Array （）
）
）
）
[3] => Array
（
[id] => 3
[name] =>便携式电子产品
[parent_id] => 1
[children] => array
（
[7] => Array
（
[id] => 7
[name] => Mp3玩家
[ parent_id] => 3
[children] => Array
（
[10] => Array
（
[id] => 10
[name] => Flash
[parent_id] => 7
[children] => Array（）
）
）
）
[8] => Array
（
[id] => 8
[name] => CD播放器
[parent_id] => 3
[children] => Array（）
）
[9] => Array
（
[id] => 9
[name] => 2路无线电
[parent_id] => 3
[children] => ; Array（）
）
）
）
）
）
）
要将特定 id 的所有子节点放到一维数组上，可以使用以下函数： / p>
function fetch_recursive（$ tree，$ parent_id，$ parentfound = false，$ list = array（））
{
foreach（$ tree as $ k => $ v）
{
if（$ parentfound || $ k == $ parent_id）
{
$ rowdata = array（）;
foreach（$ v as $ field => $ value）
if（$ field！='children'）
$ rowdata [$ field] = $ value;
$ list [] = $ rowdata;
if（$ v ['children']）
$ list = array_merge（$ list，fetch_recursive（$ v ['children']，$ parent_id，true））;
}
elseif（$ v ['children']）
$ list = array_merge（$ list，fetch_recursive（$ v ['children']，$ parent_id）
}
return $ list;
}
基于 buildtree（）函数上面，假设我们想得到 id 3的所有子节点：
echo'< pre>';
print_r（fetch_recursive（buildtree（$ a），3））;
echo'< / pre>';
这将输出：
Array
（
[0] => Array
（
[id] => 3
[name] =&便携式电子产品
[parent_id] => 1
）
[1] => Array
（
[id] => 7
[name] => Mp3玩家
[parent_id] => 3
）
[2] =& b [id] => 10
[name] => Flash
[parent_id] => 7
）
[3] =&
（
[id] => 8
[name] => CD播放器
[parent_id] => 3
）
[4] => Array
（
[id] => 9
[name] => 2 Way Radios
[parent_id] =& b $ b）
）
I've been trying to figure this out but I haven't gotten anywhere.Hopefully someone can come to my rescue.
My problem is I'm using adjacency list data model to produce my hierarchy data in mysql.I can retrieve the table (see below) into a multidimension array with associative array for each item. What I want to do is once I get this array , I want to get another array with all the nodes (child, grandchild etc) under a parent id (including the parent item).I just can't workout how to code tihs in php.
In MySQL my table appears like this:
id name parent_id
1 Electronics 0
2 Televisions 1
3 Portable Electronics 1
4 Tube 2
5 LCD 2
6 Plasma 2
7 Mp3 Players 3
8 CD Players 3
9 2 Way Radios 3
10 Flash 7
I can retrive all rows with this code into an associative array with this.
$r = mysql_query("SELECT * FROM test ");
$data = array();
while($row = mysql_fetch_assoc($r)) {
$data[] = $row;
}
Gets Results:
Array
(
[0] => Array
(
[id] => 1
[name] => Electronics
[parent_id] => 0
)
[1] => Array
(
[id] => 2
[name] => Televisions
[parent_id] => 1
)
[2] => Array
(
[id] => 3
[name] => Portable Electronics
[parent_id] => 1
)
[3] => Array
(
[id] => 4
[name] => Tube
[parent_id] => 2
)
[4] => Array
(
[id] => 5
[name] => LCD
[parent_id] => 2
)
[5] => Array
(
[id] => 6
[name] => Plasma
[parent_id] => 2
)
[6] => Array
(
[id] => 7
[name] => Mp3 Players
[parent_id] => 3
)
[7] => Array
(
[id] => 8
[name] => CD Players
[parent_id] => 3
)
[8] => Array
(
[id] => 9
[name] => 2 Way Radios
[parent_id] => 3
)
[9] => Array
(
[id] => 10
[name] => Flash
[parent_id] => 7
)
)
With those result I want to filter it down with an id.
Say for example I wanted an associative array of every node under Portable Electronics with the id of 3.(Use id for code)
It would return an array with rows with ids:
- 3 Portable Electronics (Selected parent has to be included)
- 7 Mp3 Players (Child)
- 8 CD Players (Child)
- 9 2 way Radios (Child)
- 10 Flash (Grand Child)
if Flash had children it would return those as well.
So the end result would return an array like the one above however only with those items.
Please note: I'm not after a function that creates a multidimension array of the tree structure (Already got a solution for that) .I want to build a function: fetch_recursive($id) which receives an ID and returns all the items in that level and in the levels below etc etc.
Hope this helps
Thanks in advance
Edit:
I had previously posted a solution to build a multi-dimensional array out of the output you gave as well as a way to get all child elements of a particular id out of that particular array. I have now figured out how to retrieve the child elements straight from your output (without having to first go through a buildtree() function:
function fetch_recursive($src_arr, $currentid, $parentfound = false, $cats = array())
{
foreach($src_arr as $row)
{
if((!$parentfound && $row['id'] == $currentid) || $row['parent_id'] == $currentid)
{
$rowdata = array();
foreach($row as $k => $v)
$rowdata[$k] = $v;
$cats[] = $rowdata;
if($row['parent_id'] == $currentid)
$cats = array_merge($cats, fetch_recursive($src_arr, $row['id'], true));
}
}
return $cats;
}
To use the function above, simply pass in the output array $data to the first argument and the id you want to retrieve the child elements from in the second argument:
ex.:
$list = fetch_recursive($data, 3);
Which should give you the correct array structure for id 3 (as seen in the example in the last codebox to this answer).
Original Answer:
I had never got around to writing a recursive function to build nested trees out of this design until now. I'm sure there are plenty of others who have written similar functions, but this one should definitely work for you:
function buildtree($src_arr, $parent_id = 0, $tree = array())
{
foreach($src_arr as $idx => $row)
{
if($row['parent_id'] == $parent_id)
{
foreach($row as $k => $v)
$tree[$row['id']][$k] = $v;
unset($src_arr[$idx]);
$tree[$row['id']]['children'] = buildtree($src_arr, $row['id']);
}
}
ksort($tree);
return $tree;
}
This function will recursively build a tree out of an adjacency list and keep the id's ordered in ascending order. This also makes the id's of each parent/child the key of each array of information.
This code:
$r = mysql_query("SELECT * FROM test ");
$data = array();
while($row = mysql_fetch_assoc($r)) {
$data[] = $row;
}
echo '<pre>';
print_r(buildtree($data));
echo '</pre>';
Will output something like this:
Array
(
[1] => Array
(
[id] => 1
[name] => Electronics
[parent_id] => 0
[children] => Array
(
[2] => Array
(
[id] => 2
[name] => Televisions
[parent_id] => 1
[children] => Array
(
[4] => Array
(
[id] => 4
[name] => Tube
[parent_id] => 2
[children] => Array()
)
[5] => Array
(
[id] => 5
[name] => LCD
[parent_id] => 2
[children] => Array()
)
[6] => Array
(
[id] => 6
[name] => Plasma
[parent_id] => 2
[children] => Array()
)
)
)
[3] => Array
(
[id] => 3
[name] => Portable Electronics
[parent_id] => 1
[children] => Array
(
[7] => Array
(
[id] => 7
[name] => Mp3 Players
[parent_id] => 3
[children] => Array
(
[10] => Array
(
[id] => 10
[name] => Flash
[parent_id] => 7
[children] => Array()
)
)
)
[8] => Array
(
[id] => 8
[name] => CD Players
[parent_id] => 3
[children] => Array()
)
[9] => Array
(
[id] => 9
[name] => 2 Way Radios
[parent_id] => 3
[children] => Array()
)
)
)
)
)
)
To get all child-nodes of a particular id onto a one-dimensional array, you can use this function:
function fetch_recursive($tree, $parent_id, $parentfound = false, $list = array())
{
foreach($tree as $k => $v)
{
if($parentfound || $k == $parent_id)
{
$rowdata = array();
foreach($v as $field => $value)
if($field != 'children')
$rowdata[$field] = $value;
$list[] = $rowdata;
if($v['children'])
$list = array_merge($list, fetch_recursive($v['children'], $parent_id, true));
}
elseif($v['children'])
$list = array_merge($list, fetch_recursive($v['children'], $parent_id));
}
return $list;
}
Based on the buildtree() function above, let's say we wanted to get all child nodes of id 3:
echo '<pre>';
print_r(fetch_recursive(buildtree($a), 3));
echo '</pre>';
This will output:
Array
(
[0] => Array
(
[id] => 3
[name] => Portable Electronics
[parent_id] => 1
)
[1] => Array
(
[id] => 7
[name] => Mp3 Players
[parent_id] => 3
)
[2] => Array
(
[id] => 10
[name] => Flash
[parent_id] => 7
)
[3] => Array
(
[id] => 8
[name] => CD Players
[parent_id] => 3
)
[4] => Array
(
[id] => 9
[name] => 2 Way Radios
[parent_id] => 3
)
)
这篇关于获取所有子，孙子等节点在父下使用php与mysql查询结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
