Recursing and Building a Tree from database Results

Using a database I store product departments. There are 7 department levels. For the root departments "0" I return a distinct array containing the main departments.

This array will result in 0=>'apparel', 1=>'pet supplies' and so on.
I can therefore get the sub departments of 'apparel' by returning distinct values for level[1] that have level[0] = to 'apparel' and so on. The sub can contain 'accessories' etc. which will have 'jewelry' etc. as its sub down to 7 levels.

Think of it as a google adwords mapping.

What I am trying to do is write a recursive function to iterate through all the root departments on down through the subs and any subs that those subs contain.

To be specific I am trying to generate a store department map for the site dynamically from the department database.

I have not found any solution anywhere and am at a total loss. Anything I have found only takes it down 1 level which isn't very hard to do. I need something that will be recursive through heavily nested results.


For Instance.
the root departments would be similar to:

Array ( [0] => Animals & Pet Supplies [1] => Apparel & Accessories [2] => Arts & Entertainment [3] => Cameras & Optics [4] => Electronics [5] => Furniture [6] => Health & Beauty [7] => Home & Garden [8] => Mature [9] => Media [10] => Office Supplies [11] => Software [12] => Sporting Goods [13] => Toys & Games )

each 1 of those will return an array of there own subs and each one of those another array and so on up to 7 levels of nesting.

A typical sub for array[0] would be..

Array ( [0] => Bird Supplies [1] => Cat Supplies [2] => Dog Supplies [3] => Fish Supplies [4] => Pet Bells & Charms [5] => Pet Bowls, Feeders & Waterers [6] => Pet Carriers & Crates [7] => Pet Collars & Harnesses [8] => Pet Containment Systems [9] => Pet Flea & Tick Control [10] => Pet Food Containers [11] => Pet Grooming Supplies [12] => Pet Leash Extensions [13] => Pet Leashes [14] => Pet Medical Tape & Bandages [15] => Pet Playpens [16] => Pet Steps & Ramps [17] => Pet Strollers [18] => Pet Training Aids [19] => Reptile & Amphibian Supplies [20] => Small Animal Supplies )

and so on.

It is a somewhat dated article as you can see from the examples with the old MySQL extension, but it can still apply: Sitepoint: Storing Hierarchical Data in a Database.

Didn't quite meet my needs. The first method was more along the lines of what I originally wanted to implement but wasn't quite optimal. The second method did however gave me an idea to use a lookup table. Since I manage the databases with a C++ program it was just a matter of adding another table adding a few lines of code and recompiling. The lookup will automatically have the trees formatted in the way I need when a user requests the full directory. Less strain on the DB with only 1 call. I greatly appreciate the link though. Even though I didn't go his route (exactly) it did help me formulate the solution. I originally sought help on Stack Overflow and they kept down voting because I had no output to show... If I had output to show obviously I already had a working solution. Down voting someone who legitimately needs help is not very mature at all. I will come here from now on. Seems all they care about there is reputation and not helping others. Thank you again.