使用php和mysql查询结果获取父节点下的所有子节点、孙节点等节点

更新时间：2022-10-18 11:42:12

我之前发布了一个解决方案，用于根据您提供的输出构建一个多维数组，以及一种从该特定数组中获取特定 id 的所有子元素的方法.我现在已经想出了如何直接从您的输出中检索子元素(无需先通过 buildtree() 函数:

function fetch_recursive($src_arr, $currentid, $parentfound = false, $cats = array()){foreach($src_arr 作为 $row){if((!$parentfound && $row['id'] == $currentid) || $row['parent_id'] == $currentid){$rowdata = 数组();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));}}返回 $cats;}

要使用上面的函数，只需将输出数组 $data 传递给第一个参数和要从第二个参数中检索子元素的 id:

例如:

$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;未设置($src_arr[$idx])；$tree[$row['id']]['children'] = buildtree($src_arr, $row['id']);}}ksort($tree);返回 $tree;}

此函数将递归地从邻接列表中构建一棵树，并按升序保持 id 的顺序.这也使得每个父/子的 id 成为每个信息数组的键.

此代码:

$r = mysql_query("SELECT * FROM test");$data = 数组();while($row = mysql_fetch_assoc($r)) {$data[] = $row;}echo '';print_r(buildtree($data));echo '</pre>';

将输出如下内容:

数组([1] =>大批([id] =>1[名称] =>电子产品[parent_id] =>0[儿童] =>大批([2] =>大批([id] =>2[名称] =>电视机[parent_id] =>1[儿童] =>大批([4] =>大批([id] =>4[名称] =>管子[parent_id] =>2[儿童] =>大批())[5] =>大批([id] =>5[名称] =>液晶显示器[parent_id] =>2[儿童] =>大批())[6] =>大批([id] =>6[名称] =>等离子体[parent_id] =>2[儿童] =>大批())))[3] =>大批([id] =>3[名称] =>便携式电子产品[parent_id] =>1[儿童] =>大批([7] =>大批([id] =>7[名称] =>Mp3 播放器[parent_id] =>3[儿童] =>大批([10] =>大批([id] =>10[名称] =>闪光[parent_id] =>7[儿童] =>大批())))[8] =>大批([id] =>8[名称] =>CD 播放器[parent_id] =>3[儿童] =>大批())[9] =>大批([id] =>9[名称] =>2路收音机[parent_id] =>3[儿童] =>大批()))))))

要将特定 id 的所有子节点放到一维数组中，可以使用此函数:

function fetch_recursive($tree, $parent_id, $parentfound = false, $list = array()){foreach($tree as $k => $v){if($parentfound || $k == $parent_id){$rowdata = 数组();foreach($v as $field => $value)if($field != '儿童')$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));}返回 $list;}

基于上面的 buildtree() 函数，假设我们想要获取 id 3 的所有子节点:

echo '';print_r(fetch_recursive(buildtree($a), 3));echo '</pre>';

这将输出:

数组([0] =>大批([id] =>3[名称] =>便携式电子产品[parent_id] =>1)[1] =>大批([id] =>7[名称] =>Mp3 播放器[parent_id] =>3)[2] =>大批([id] =>10[名称] =>闪光[parent_id] =>7)[3] =>大批([id] =>8[名称] =>CD 播放器[parent_id] =>3)[4] =>大批([id] =>9[名称] =>2路收音机[parent_id] =>3))

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:

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
        )

)

上一篇 : ：获取父节点及其子节点，其中的子节点具有特定的文本下一篇 : SPARQL查询以获取节点的所有父节点

使用php和mysql查询结果获取父节点下的所有子节点、孙节点等节点

Edit:

相关阅读

技术问答最新文章