January 31st, 2002, 07:01 PM
It sounds like you have a solution. However if you do not I can offer some code I wrote in perl which displays the Org chart of a large company in just the way you describe in html. It has a lot of extra code that doesn't apply but it may work for you.
It uses the id,parent method as I have seen in this thread and basically uses two queries and a recursive routine.
The first one builds a string I call the $parentList. Based on the id of the person in the org chart the user clicks on it builds a space delimeted list of parents back to the root node.
That string is needed for the recursive routine which issues on query for everytime it is called (the number of levels deep into the tree). We query the database for nodes with a null parent and display the information. If the id of this node is in the $parentList string we call ourself recursively so the next time we get nodes with this id as a parent.
Man, I hope that makes sense. Anyway, I will post the code with extra stuff scraped out if this is of any interest to you. It is a very interesting topic.
Another problem we had to solve is similar. It involves a hierarchy again like that of a product list. How do you know if a product exists as a child of any particular nodes above it. Given a hierarchy like "Products/TV's,Sony,Remote" how can you, with one query, determine if "Remote" belongs to "Sony"? Or belongs to "Products", or of course "Tv's"? Our solution is not pretty but works and could be a whole other thread topic.
Code on, brothers!!