MySQL & PHP get depth of item in hierarchy with parent_id

I have a开发者_JAVA百科 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!

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

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