swa66 - 10:26 am on Oct 8, 2012 (gmt 0)
I'm not sure I fully got how your tree is modeled in your database (all I'd expect is a parent id).
Maybe the first part is to explain just what lft and rgt mean in your table.
Anyway what I would do:
- just keep the parent id for every category
- load a tree in memory that can be walked efficiently (catid -> parentcatid) is all you need ...
- And then you can walk up to the root from any catid, in the inmemory array, which gives you a list of cats to get the details like url, what to show etc from, you even get the order (to show them in - well reverse, but that's easy enough).
[Although, no doubt somebody with more SQL skills than myself could walk the tree in SQL just as well]