The problem with storing data such as Imar shows is that retrieving the data in tree-order via a SQL query is nearly impossible if the "depth" of the tree is unknown and/or large.
You have to JOIN the table to itself as many times are there are levels in the tree. One way to do this is via a UNION:
Code:
SELECT id AS level1, 0 AS level2, 0 AS level3, name
FROM table WHERE parentID = 0
UNION
SELECT T1.id AS level1, T2.id AS level2, 0 AS level3, T2.name
FROM table AS T1, table AS T2
WHERE T1.parentID = 0 AND T2.parentID = T1.id
UNION
SELECT T1.id AS level1, T2.id AS level2, T3.id AS level3, T3.name
FROM table AS T1, table AS T2, table AS T3
WHERE T1.parentID = 0 AND T2.parentID = T1.id AND T3.parentID = T2.id
ORDER BY level1, level2, level3
And that works. But what about if there might be 17 levels in the tree?
Another way is to better organize the DB.
Something like this:
Code:
Id Name ParentId Path
1 Root 0 0001-
2 Child 1 1 0001-0002-
3 Child 2 1 0001-0003-
4 GC 1 of C2 3 0001-0003-0004-
5 GC 1 of C1 2 0001-0002-0005-
6 Child 3 1 0001-0006-
7 GC 2 of C1 2 0001-0002-0007-
8 GC 1 Of C3 6 0001-0006-0008-
9 GGC1/GC1/C1 5 0001-0002-0005-0009
And now, if you simply do
Code:
SELECT * FROM table ORDER BY path
it all comes out in ready-to-use-in-your-tree order, thus:
Code:
Id Name ParentId Path
1 Root 0 0001-
2 Child 1 1 0001-0002-
5 GC 1 of C1 2 0001-0002-0005-
9 GGC1/GC1/C1 5 0001-0002-0005-0009
7 GC 2 of C1 2 0001-0002-0007-
3 Child 2 1 0001-0003-
4 GC 1 of C2 3 0001-0003-0004-
6 Child 3 1 0001-0006-
8 GC 1 Of C3 6 0001-0006-0008-
It's a bit more work to put the PATH value into the records as you build the table (you need to convert the id's to strings and pad them out to all the same length, as I show there) but it's well worth the trouble for large trees or trees with a lot of depth.