Getting hierarchy data from self-referencing tables
If the database is SQL 2005 / 2008 then...
The easiest way to get this is using a CTE (Common Table Expression) that is designed to recurse.
WITH myCTE (Item_id, Depth) AS ( Select Item_ID, 0 as Depth From yourTable where Item_Parent=0 Union ALL Select yourTable.Item_ID, Depth + 1 From yourTable inner join myCte on yourTable.item_Parent = myCte.Item_Id ) Select Item_id, Depth from myCTE
The output is as follows:
Item_Id Depth 1 0 2 0 3 1 4 1 5 2
From that you can format it as you wish.
There is a good tech article on the mysql website about hierarchical data in MySql:Managing Hierarchical Data in MySQL - you can find a few detailed solutions with pro and cons there.
Especially the part about "The Nested Set Model" and "Finding the Depth of the Nodes" should be of interest for you.
Oracle has a very convenient syntax for retrieving hierarchical data like this:
select item_id, item_parent, level as depthfrom itemsconnect by prior item_id = item_parentstart with item_parent not in (select item_id from items)
This starts with the root nodes of your trees as those items whose item_parent does not exist in the table as item_id, and selects all children of those nodes, along with their depth in the tree.