Show with php a Genealogy tree stored in Database

Question

I have a table in my database(mysql) with the next schema:

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

And I want to show it on my page the following way:

grandfather 
father
son
brother
sister
Uncle

(a preorder traversal)

This is the best solution I had come to(doesn't work)

$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;
            }
        };
    };

As a result of this I am getting the first traversal to the bottom but never the rest of the tree:

Grandfather
parent
son

What I am doing wrong? Or what do you suggest me?

Note: when this is finished I would have many "grandfathers" that's why there is the while (I would add a "parent_id=NULL" condition there).

Edit: the estructure of the rows after doing this:

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

is this(formated for clarity):

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

Here's an example that works on your dataset and assumes the raw data is sorted by id (as your query does). I believe the way to go is to write small(er), simple(r) functions that transform the data into a tree and to traverse it step by step. It's probably possible make a traversal happen from the array and print it in one leap as you're doing, but my brain isn't able to think of a good way at the moment.

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";
}

Output:

Grandfather
Father
Son
Brother
Sister
Uncle