Using one DB query per node in a tree is expensive, and if your tree is large, will horribly slow the site. The answer is to take a little more time on inserts and reap the efficiency rewards in selects. I’ve been using this technique for a while but I just mislaid my “rebuild tree” method so I was digging around for an example online. This article in Sitepoint by Gijs Van Tulder spells out the techniques and pros & cons nice and clearly, with good solid code examples. They call it the “ modified preorder tree traversal algorithm” but whatever. I’ve always heard it called the Visitation Method. Same thing different name. Cool. Cheers Gijs & Sitepoint.
PS this bit of PEAR code is handy too