Storing and retrieving directory structures in MYSQL
Has anyone here created some sort of directory structure before, with categories & subcategories, that they stored in a mysql database?
This first time I tried this, I set up each record with a category name, category id and parent id (The parent id for root-level categories was 0). I figured there was some sort of recursive function that would retrieve the tree structure from the database. However, I just couldn't figure it out. In the end I settled on a different solution.
I stored my category records like this:
The fields were printed out by their order number ascending, and indented based on their level number like this:
Category Name Order Level
Category A 1 0
Category AB 2 1
Category B 3 0
Category BA 4 1
Category BAA 5 2
From this could be inferred the parent-child relationships between categories. But now the order numbers have to be refreshed every time a category is added or deleted, which is more error-prone than just storing the parent-child relationships.
Can anyone else share how they store and retrieve directory structures?