Relationship of records of a table with itself

0

I'm trying to set up a genealogical tree structure, but I still can not relate to it and leave it ready for the while to scroll through the entire table and structure the tree infinitely, since the person can have 'x' children. p>

Then the table is

|ID  | Nome | IdPai |
|1   | Zé   |  NULL |
|2   | Joao | 1     |
|3   | Maria| 1     |

So, I can for a few levels put a while within a while, however, I believe there is an easier way to ensure that while you scroll through the table.

Any Suggestions?

    
asked by anonymous 30.07.2017 / 21:20

1 answer

0

For this you need to do a recursive function. Recursive function is one that can make a call to itself.

As you did not put your code, I'll put an example in a very abstract way:

function getArvore($id, $count, $conn) {
    if ((!isset($count)) || ($count = null)) {
        $count = 1;
    }

    $query = $conn->prepare("SELECT Nome FROM sua_tabela WHERE ID = :id");
    $query->execute(array("id" => $id));
    $rows = $query->fetchAll(PDO::FETCH_ASSOC);
    if (count($rows) > 0) {
        for ($i = 0; $i < $count; $i++) {
            echo "-";
        }
        echo $rows[0]["Nome"]."<br />";
        $query = $conn->prepare("SELECT id FROM sua_tabela WHERE IdPai = :id");
        $query->execute(array("id" => $id));
        $rows = $query->fetchAll(PDO::FETCH_ASSOC);
        foreach($rows as $row) {
            getArvore($row["id"], $count + 1, $conn);
        }
    }
}

$conn = new PDO(
        'mysql:host='.$seu_host.'; dbname='.$seu_banco,
        $seu_usuario,
        $sua_senha,
        array(
            PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
            PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
        )
);

getArvore(1, 1, $conn);

From this example you can do exactly what you need.

    
30.07.2017 / 22:11