How can I convert my database to a nested tree structure

I started a tree structure database from scratch, and I thought I had it right, heres what it looks like:

but there musta been a glitch in there somewhere because the categories aren’t organised properly anymore. I don’t know how to go about fixing this now. Also, I have other category tables which I’d like to convert into a heirarchical structure, but they are packed full of entries so building it up from scratch manually isn’t an option. Its the lft and rgt part that would be too difficult for me to enter manually, I can put in parent ids though, and I know that I could make a script to organise the lft, rgt values based on the parent_id but don’t wanna go reinventing the wheel here, is there a tool that’ll do this automatically? How do you all deal with heirarchical databases? Is the left and right value approach the best way to do it?

with the adjacency model, as in Categories and Subcategories – works fine up to 15 levels deep

yes, if you truly have ~unlimited~ levels of depth in your hierarchy

I sorted it out with cakephp. It makes it pretty easy, it does all the reorganising for you when you insert or edit a row.

CakePHP makes it real easy to convert an adjacency model database to a nested one. You just feed the parent_id into cakes Save function and it puts in the right and left values for you. I just converted a huge adjacency model database to nested model with cake.

NMR Spectroscopy on a sitepoint page!

I thought I’d never see that happen.

My life is now complete and has come full circle!