使用php和mysql查询结果获取父节点下的所有子节点、孙节点等节点 [英] Get all child, grandchild etc nodes under parent using php with mysql query results

查看：43
本文介绍了使用php和mysql查询结果获取父节点下的所有子节点、孙节点等节点的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我一直在想办法解决这个问题，但一直没有结果.希望有人能来救我.

我的问题是我使用邻接列表数据模型在 mysql 中生成我的层次结构数据.我可以将表(见下文)检索到一个多维数组中，每个项目都有关联数组.我想要做的是一旦我得到这个数组，我想得到另一个数组，其中包含父 ID(包括父项)下的所有节点(子节点、孙子节点等).我只是无法锻炼如何在其中编写代码php.ini

在 MySQL 中，我的表如下所示:

id 名称 parent_id1 电子产品 02 电视 13 便携式电子产品 14 管 25 液晶显示器 26 等离子 27 Mp3 播放器 38 CD 播放器 39 2路收音机 310 闪光 7

我可以用这个代码将所有行检索到一个关联数组中.

$r = mysql_query("SELECT * FROM test");$data = 数组();while($row = mysql_fetch_assoc($r)) {$data[] = $row;}

获取结果:

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

有了这些结果，我想用一个 id 过滤它.

比如说我想要一个 id 为 3 的 Portable Electronics 下每个节点的关联数组.(使用 id 表示代码)

它会返回一个包含 id 行的数组:

  • 3 便携式电子产品(必须包括选定的父级)
  • 7 Mp3 播放器(儿童)
  • 8 个 CD 播放器(儿童)
  • 9 2 路收音机(儿童)
  • 10 Flash(孙子)

如果 Flash 有孩子，它也会返回那些孩子.

因此，最终结果将返回一个类似于上面的数组，但仅包含这些项目.

请注意:我不是在追求创建树结构的多维数组的函数(已经得到了解决方案).我想构建一个函数:fetch_recursive($id)，它接收一个 ID 并返回该级别中的所有项目和在以下级别等中

希望能帮到你

提前致谢

解决方案

我之前发布了一个解决方案，用于根据您提供的输出构建一个多维数组，以及一种从该特定数组中获取特定 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:
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屋！
查看全文
相关文章
PHP最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆