Relationship of records of a table with itself

Question

I’m trying to put together a family tree structure, but I still can’t relate and leave it ready for while to go through the whole table and structure the tree infinitely, since one can have 'x' children.

So the table is

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

So, I can get a few levels to put a while inside a while, but I think there’s an easier way to make sure that the while goes through the entire table.

Some Suggestion?

Answer 1

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

Since you didn’t 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.