用 php 显示存储在数据库中的家谱树

Question

我的数据库(mysql(中有一个表，其中包含下一个模式：

-----------------------------
- id  name         parent_id
-----------------------------
- 1   grandfather  NULL
- 2   father       1
- 3   uncle        1
- 4   son          2
- 5   brother      2
- 6   sister       2

我想以以下方式在我的页面上显示它：

grandfather 
father
son
brother
sister
Uncle

(预序遍历(

这是我来的最好的解决方案(不起作用(

$sql = "SELECT p1.id, p1.name, p1.parent_id FROM tree p1 ORDER BY p1.id";
$result = $conn->query($sql);
while($row = mysqli_fetch_assoc($result)) {
arbol($row, $result);
};
function arbol($fila, $result) {
echo $fila["name"] . "<br>";
$flag = false;
while($busqueda = mysqli_fetch_row($result)) {
if($busqueda[2]==$fila["id"]){
$flag = true;
break;
}
};
if ($flag){
foreach ($result as $ruta) {
if($ruta["parent_id"]==$fila["id"]){
arbol($ruta, $result);
};
};

} else {
return;
}
};
};

因此，我得到了第一个遍历到底部，但从来没有遍历树的其余部分：

Grandfather
parent
son

我做错了什么？或者你对我有什么建议？

注意：当这完成后，我会有很多"祖父"，这就是为什么有 while(我会在那里添加一个"parent_id=NULL"条件(。

编辑： 执行此操作后行的结构：

while( $row = mysqli_fetch_assoc( $result)){
$resguard[] = $row;
}
print_r($resguard);

是这个(为清楚起见，格式化(：

Array ( 
[0] => Array ( [id] => 1 [name] => Grandfather [parent_id] => ) 
[1] => Array ( [id] => 2 [name] => Parent [parent_id] => 1 ) 
[2] => Array ( [id] => 3 [name] => Uncle [parent_id] => 1 ) 
[3] => Array ( [id] => 4 [name] => Son [parent_id] => 2 ) 
[4] => Array ( [id] => 5 [name] => Brother [parent_id] => 2 ) 
[5] => Array ( [id] => 6 [name] => Sister [parent_id] => 2 ) 
)

Answer 1

下面是一个适用于数据集的示例，并假定原始数据按 id 排序(就像查询一样(。我相信要走的方法是编写小(呃(，简单的(r(函数，将数据转换为树并逐步遍历它。也许有可能从数组中遍历并像您所做的那样一次性打印它，但我的大脑目前无法想到一个好方法。

function make_tree($data) {
$tree = [];
foreach ($data as $node) {
insert($tree, $node);
}
return $tree;
}
function insert(&$root, &$node) {
if (!$root) {
$root = $node;
}
else if ($root["id"] === $node["parent_id"]) {
$root["children"][] = $node;
}
else if (array_key_exists("children", $root)) {
foreach ($root["children"] as &$c) {
if (insert($c, $node)) {
break;
}
}
}
}
function preorder(&$root) {
if ($root) {
yield $root;
if (array_key_exists("children", $root)) {
foreach ($root["children"] as $c) {
yield from preorder($c);
}
}
}
}
$data = [
["id" => 1, "name" => "Grandfather", "parent_id" => null],
["id" => 2, "name" => "Father", "parent_id" => 1],
["id" => 3, "name" => "Uncle", "parent_id" => 1],
["id" => 4, "name" => "Son", "parent_id" => 2],
["id" => 5, "name" => "Brother", "parent_id" => 2],
["id" => 6, "name" => "Sister", "parent_id" => 2]
];
$tree = make_tree($data);
foreach (preorder($tree) as $node) {
echo $node["name"]."n";
}

输出：

Grandfather
Father
Son
Brother
Sister
Uncle