Endless nested tree of organizations in database

Question

I have a table named as Organizations

 id  |  organization |  parent_id
-----+---------------+-----------
 51  |  Organ1       |    0
 71  |  Organ2       |    0
 83  |  Organ2.1     |   71
 89  |  Organ1.1     |   51
 104 |  Organ1.1.1   |   89
...

The organizations that have parent_id = 0 are the root organizations. Now I see I am wrong. parent_id defines the organization's parent organization.

It looked a brilliant idea to me first. Thus, I would have created endless nested child organizations in a single table.

Now I am stuck how to determine how deep a organization is.

My current attempt is for now like the following:

<?php
$queryRootOrg = $db->prepare("SELECT * FROM Organizations WHERE parent_id = 0" );

$queryRootOrg ->execute();
$menu = "<ul id='menu'>";

if($queryRootOrg ->rowCount() > 0)
{
    while($rowRoot = $queryRootOrg ->fetch(PDO::FETCH_ASSOC))
    {
        $menu .= "<li><a href='?org=".$rowRoot['id']."'>".$rowRoot['birim']."</a>";

        $queryChildren= $db->prepare("SELECT * FROM Organizations WHERE parent_id = :p1");
        $queryChildren->bindValue(":p1", $rowRoot['id'], PDO::PARAM_INT);

            $queryChildren->execute();

        if ($queryChildren->rowCount() > 0)
        {
            $menu .= "<ul>";
            while($rowSub = $queryChildren->fetch(PDO::FETCH_ASSOC))
            {
                $menu .= "<li><a href='?org=" . $rowSub['id'] . "'>" . $rowSub['birim'] . "</a></li>";
            }
            $menu .= "</ul>";
        }
        $menu .= "</li>";
    }
}

$menu .= "</ul>";
?>

This code brings me only two levels. I think I cannot see the third level organization in the list. My question is how come I know the depth of an organization in this circumstance? Or is there another approach?

possible duplicate of [MySQL - Adjacency List Model - Get Depth](http://stackoverflow.com/questions/10999888/mysql-adjacency-list-model-get-depth) — Marc B, Jun 14 '13 at 14:39
So basically.. if you had `depth`, your problems would be gone.. which implies something :) — N.B., Jun 14 '13 at 14:42
@N.B. You are right. I have a constant depth for my organization. But I am trying to be universal. I will share my code on github. So no one can know the exact count of the depth. — zkanoca, Jun 14 '13 at 14:44

score 1 · Accepted Answer · answered Jun 14 '13 at 14:51

1

Please refer below link as it provide all method for routing through tree like structure ,

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

answered Jun 14 '13 at 14:51

Anil Gupta

632
4
16

score 1 · Answer 2 · answered Jun 14 '13 at 15:01

You could use the mysql nested set model (this link should also answer all your related questinos)

However the approach with parent id, like you did, is a good one since it is simple and robust.

It only lacks performance in some cases.

Basically there is no other way than always select the parent by iterating and count the iterations.

You can do that in php with separate queries as well as with internal mysql functions (procedures). If you are on postgres you can also make recursive queries.

In your case altough i think it would be best to just store the depth along with the parent, just keep in mind that you have to recalculate it when something changes.

score 1 · Answer 3 · answered Jun 14 '13 at 15:22

1

You could try to just read all data from MySQL and construct a tree with PHP manually, if your database is not big it will be the easiest way to do.

answered Jun 14 '13 at 15:22

artahian

2,093
14
17

I was planning just as you say. (: +1 – zkanoca Jun 14 '13 at 19:53

Endless nested tree of organizations in database

3 Answers3