More SQL guys...
Basically I'm trying to create a drop-down very similar to the "Forum Jump" at the bottom of this page. That is, displaying a hierarchy. Im writing in PHP.
The table is laid out such that every item has its own ID, and a parent ID. Items at the top of the hierarchy have a parent ID of zero. So I want to be able to output the data like:
Item 1
Item 2
SubItem1
Item 3
SubItem2
SubItem3
Etc, etc, you get the idea.
Now, I can think of a way to do this in two queries - get the Top-level items first, and then get the items with a parent value != 0. For each top-level item, check to see if there are any children, and if so display them.
But when you look at the boards Forum Jump, there are multiple levels. Thus, with my method, for n levels, you would have to perform n queries.
I'm looking for a query to organise data like this:
+-------+--------+-------+
| id | text | parent|
+-------+--------+-------+
| 1 | Item1 | 0 |
| 2 | Item2 | 0 |
| 3 | Item3 | 0 |
| 4 |Subitem1| 1 |
| 5 |Subitem2| 2 |
| 6 |Subitem3| 2 |
+-------+--------+-------+
Into data like this:
+-------+--------+-------+
| id | text | parent|
+-------+--------+-------+
| 1 | Item1 | 0 |
| 4 |Subitem1| 1 |
| 2 | Item2 | 0 |
| 5 |Subitem2| 2 |
| 6 |Subitem3| 2 |
| 3 | Item3 | 0 |
+-------+--------+-------+
Anyone have any ideas? I'm only at the design point of the tables, so if you have a better design idea, then fire it at me...
