Create a Dynamic Nav on the Fly

Question

I'm looking for the best way to create a complex navigation element on the fly. I have all of the elements in a database (title, id, parentId) and I want to efficiently take them out of the DB and display them correctly. I also want to collapse all of the navigation elements that aren't active. So if I was browsing through "Sofas" I wouldn't see "Chandeliers" or any of the categories under lighting but I would see "Lighting".

This is what I want the final product to look like:

• Furniture
  • Living Room
    • Sofas
    • Chairs
    • Ottomans
  • Bedroom
    • Beds
    • Nightstands
• Lighting
  • Chandeliers
  • Floor Lamps
  • Sconces
• Rugs & Textiles
  • Contemporary
  • Vintage

My current method is

1. write one SQL query that pulls down all of the category names, ids, and parent ids
2. Iterate through the categories and put into a sorted multi-dimensional array with child categories stored under their parents.
3. Iterate through the new array and add another entry to mark the appropriate categories as open (all categories are closed by default)
4. iterate through the array and write HTML

I'm trying to to this with as few interations as possible and I'm sure the code I have right now is inefficient. Especially step 2 I iterate through the array several times. There has to be a general solution to this (common?) problem.

Answer 1

Consider adding a new field to your database table: level.
Main-categories will have level 0.
Sub-categories will have level 1.
Sub-sub-categories will have level 2.
etc.

This trick will help you to know which sub-categories to disable without 2nd iteration of the array.