A few days ago I was working on a navigation tree. The basic idea is simple: every navigation item is part of another navigation item or the root item.
Seems like a simple parent-child relation, so I made a MySQL table to store everything in. Every record has a parent_id and, ofcourse, an unique id. I wanted to load the data in a multi dimensional associated array in PHP, so that I could easily include them in my HTML templates (using recursive functions).
The general aproach for these kinds of data is recursive functions, but this leads to a huge amount of overhead and a lot of useless loops. That’s why I decided to take another approach, using references.
The basic idea is simple: for every parent you create, you write a reference to this parents’ children array in a seperate array.
Take in mind that, in order to use this approach, you need to sort your data on the depth level! That means you will have to store the level of a child (= count the parent, grandparent, …) in your table. Order your data ascending on depth level, and you should be alright. If you dont do this, the script will try to add records to unexisting parents.
You can find my example script here.