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

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.

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

In MySQL my table appears like this:

在MySQL中，我的表格如下所示：

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.

有了这些结果，我想用id过滤掉它。

Say for example I wanted an associative array of every node under Portable Electronics with the id of 3.(Use id for code)

比方说，我想要一个ID为3的便携式电子设备下每个节点的关联数组。（代码使用id）

It would return an array with rows with ids:

它将返回一个包含id的行的数组：

• 3 Portable Electronics (Selected parent has to be included)
• 3便携式电子产品（必须包括选定的父母）
• 7 Mp3 Players (Child)
• 7个Mp3玩家（儿童）
• 8 CD Players (Child)
• 8名CD播放器（儿童）
• 9 2 way Radios (Child)
• 9 2路无线电（儿童）
• 10 Flash (Grand Child)
• 10 Flash（大孩子）

if Flash had children it would return those as well.

如果Flash有孩子，它也会返回。

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.

请注意：我不是在创建树结构的多维数组的函数之后（已经有了解决方案）。我想构建一个函数：fetch_recursive（$ id），它接收一个ID并返回所有项目那个级别和等级以下等等。

Hope this helps

希望这可以帮助

Thanks in advance

提前致谢

2 个解决方案

#1

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:

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

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:

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

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

哪个应该为id 3提供正确的数组结构（如本答案的最后一个代码框中的示例所示）。

---

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.

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

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:

要将特定id的所有子节点都放到一维数组上，可以使用以下函数：

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:

基于上面的buildtree（）函数，假设我们想要获取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
        )

)

#2

Here is a way to get you further, and you can decide how you build your result array and what fields you choose to include. This is not tested, but you should see the logic.

这是一种让您更进一步的方法，您可以决定如何构建结果数组以及选择包含哪些字段。这没有经过测试，但您应该看到逻辑。

// connect to db

// set id counter
$ids = 0;

// declare array
$categories = new Array();

// determine max ids
$query = mysql_query("SELECT COUNT(1) AS ids FROM test");
$result = mysql_fetch_array(query); // get result
$count = $result['ids'];

// loop through ids for parents
for($ids = 0; $ids <= $count; $ids++) {
  $query1 = mysql_query("SELECT * FROM test WHERE id = '" . $ids . "'");
  $query2 = mysql_query("SELECT id, name, parent_id FROM test WHERE parent_id = '" . $ids . "'");
  // check if has children
  if(mysql_num_rows($query2) > 0) {
    // iterate through children and add to array
    while (mysql_fetch_array($query2) as $row) {
      $categories[$ids]['child'][$row['id']] = $row['name'];
    }
  }
  // check if has siblings
  if(mysql_num_rows($query1) > 0) {
    // iterate through children and add to array
    while (mysql_fetch_array($query2) as $row) {
      $categories[$ids]['sibling'][$row['id']] = $row['name'];
    }
  }
}