Always with the SQL...

seamus

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...

seamus

Or if anyone fancies taking a look at some vB source code, and letting me know how they do it....

Seth Brundle

As you may add more items and subitems over time then I guess that #1 is the better way to go. the parent column will be a foreign key on the id column.
You then run "select * from table order by parent, title"

As you loop through the returned records, use an if statement to tell if they are a parent item or a child subitem (parent item will have a parent record of 0) and like this forum just throw in a few   (or whatever) to pad out the subitems.

Simple!

jimmycrackcorm

http://www.nigelrivett.net/SQLTsql/RetrieveTreeHierarchy.html