MySQL & PHP get depth of item in hierarchy with parent_id

Question

I have a common database structure using parent_id for a hierarchy.

I'm trying to find out the depth of an item.

So take for example this:

ID  Name    Parent_id
1   Games   0
2   Nintendo    1
3   DS  2
4   3D  3

If I wanted to find out the depth of ID 4 (3D), the answer is 3. Any idea how I would query this or a combination of sql and php?

Thank you!

Not sure what you mean by "depth"? Are you wanting to get the Parent_id or is that just coincidentally the same value? — Alan Moore
– Alan Moore, Commented Sep 25, 2011 at 15:08

Code Lღver · Accepted Answer · 2013-02-12 10:22:03Z

3

Do you have a naive implementation? are you looking for the best way? Trees are recursive, so I think you will query the db as much as the tree height.

this pseudo-php

function getHeigth($item_name) {
    $res = 0;
    $current_parent_id = executeSql("SELECT parent_id FROM games g WHERE g.name= ? " , $item_name);
    while ($current_parent_id != 0) {
        $current_parent_id = executeSql("SELECT parent_id FROM games g WHERE g.id = ? " , $current_parent_id);
        $res = $res + 1;
    }
    return $res;
}

This algorithm will perform bad if your three is not balanced.

Storing depth would improve performance, but will impact in UPDATE and INSERT queries.

Also if your tree is broken, this Psuedo-PHP could loop forever

edited Feb 12, 2013 at 10:22

Code Lღver

15.6k16 gold badges61 silver badges75 bronze badges

answered Sep 25, 2011 at 15:17

llazzaro

4,2184 gold badges35 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

llazzaro Over a year ago

this is a naive way to solve the problem with his table. add the height to the schema is better I think. do you know a better way to solved the problem without changing the schema?. Usually trees are balaced also, it doesnt matters if the table is big.

Your Common Sense Over a year ago

sure, I know. not to run into circumstances where such a problem exists.

dzm Over a year ago

Thank you, this should work fine, but I think we'll change our db to include depth or go with a nested model

Your Common Sense · Accepted Answer · 2011-09-25 15:15:11Z

1

The better way to get depth is to store it along with data in a separate field.

An even better way is to use a little more intelligent way of storing hierarchal data, such as Nested Sets or Materialized Path.

Although you can get your depth from your current table setup, it would be most ugly way, recursion, of course. However, if your table is relatively small, it won't be a big deal though

answered Sep 25, 2011 at 15:15

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Collectives™ on Stack Overflow

MySQL & PHP get depth of item in hierarchy with parent_id

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related