3

I have a table in my database that host many genealogy trees.

-----------------------------
- id  name              parent_id
-----------------------------
- 1   grandfather       NULL
- 2   father            1
- 3   uncle             1
- 4   son               2
- 5   brother           2
- 6   cousin's dauther  7
- 7   cousin            8
- 8   auntie            1

The problem is I can't get to show all the names because of an edge case:

-When I have a person with a parent_id that is bigger than it's parent's parent_id (see cousin's daugter)

I use this queries to get the table:

    $sql = "SELECT p1.id, p1.name, p1.parent_id FROM pariente p1 
    ORDER BY p1.parent_id";
    $result = $conn->query($sql);

The problem is that if I use "ORDER BY parent_id" "cousin's dauther" won't show and if I use "ORDER BY id" "cousin" won't show.

I use this functions to make a the tree into an array and draw it:

        function make_tree($data, $root) {
            $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 preorder2(&$root) {
            if ($root) {
                echo "<li>";
                echo $root["name"];

                if (array_key_exists("children", $root)) {
                    echo "<ul>";
                    foreach ($root["children"] as $c) {
                        preorder2($c);
                    }
                    echo "</ul>";
                }
                echo "</li>";
            }
        }
    ?>

And after I use this to call the functions:

<div>

<?php
while( $row = mysqli_fetch_assoc( $result)){
    $resguard[] = $row;
}
    $tree = make_tree($resguard);
    preorder2($tree);
?>
</div>
MatAle Albiach
  • 95
  • 1
  • 9

2 Answers2

1

I had a similar problem once, and here's how I fixed it.

  1. Iterate over the dataset, putting each node in your array, and keep track of what you want to be your root node.

  2. Iterate over the array. For each node where the parent_id is not null, lookup the parent node by id, and add the current node as a child. There's no need to use recursion when building the tree.

toastifer
  • 478
  • 3
  • 8
  • In the actual version I have more than 1400 people in the table. If I understand your solution I have to populate the array first with all the roots (parent_id= null), and after go trough the table adding the children to every node already in the table. It's a nice way, but I have trouble imagining how to add all the nodes, how many times should I go trougth the table checking the children, once for every person in the table? Or what condition should I use? in the first iteration it will already add most of the people. – MatAle Albiach Aug 20 '18 at 23:35
  • You go through the list twice. The first puts the node into an array with the ID as the key, so you can look it up instantly. The second time you connect each child to the parent. Don't add the children until every node can be looked up by id. That way you get them all. As a benefit, you now also have access to every node by id without having to search the results or the tree. – toastifer Aug 21 '18 at 00:03
  • This sounds very good, but is a preorder traversal possible for this structure? – ggorlen Aug 21 '18 at 01:18
  • @ggorlen Certainly! Just keep a reference/pointer to the root node, and then visit the current node then the children in order. – toastifer Aug 21 '18 at 01:28
0

At the end I believe I didn't get toastifier answer, but it get me thinking and I ended up solving the problem (still using the way ggorlen shows the tree).

First this are the queries:

$sql = "SELECT p1.id, p2.name, p2.id as minor, p2.name FROM pariente p1 INNER JOIN pariente p2 ON p1.id = p2.parent_id ORDER BY p1.id";
$result = $conn->query($sql);

$sql2 = "SELECT p1.id, p1.nombre, p1.padre_id FROM pariente p1 WHERE p1.padre_id IS NULL ORDER BY p1.id";
$raices = $conn->query($sql2);

The functions:

        function make_tree($resguardo, $root){
            $tree = [];
            $tree = $root;
            foreach ($resguardo[$root["id"]] as $node) {
                add($tree, $node, $resguardo);
            }
            return $tree;
        }

        function add(&$root, &$children, $resguardo){
            $root["children"][$children["minor"]] = $children;

            $flag= false;
            if (isset($resguardo[$children["minor"]])) {
                $flag = true;
            }

            if ($flag == false){
                return;
            } else {
                foreach ($resguardo[$children["minor"]] as $child) {
                    agregar($root["children"][$children["minor"]], $child, $resguardo);
                }
            }
        }

        function preorder2(&$root) {
            if ($root) {
                echo "<li>";
                echo '<a href="">';
                echo $root["name"];
                echo "</a>";

                if (array_key_exists("children", $root)) {
                    echo "<ul>";
                    foreach ($root["children"] as $c) {
                        preorder2($c);
                    }
                    echo "</ul>";
                }
                echo "</li>";
            }
        }

And I call them here:

while( $row = mysqli_fetch_assoc($result)){
    $resguardo[$row["id"]][] = $row;
}
    while( $root = mysqli_fetch_assoc( $roots)){
    echo '<ul>';
    $tree = make_tree($resguardo, $root);
    preorder2($tree);
    echo "</ul>";
}

Thanks alot to both of you, I would never solve it without your help.

MatAle Albiach
  • 95
  • 1
  • 9