Creating folders within folders within folders etc

Not sure if this is more of a PHP or MySQL issue, but I'm leaning towards MySQL. I am trying to formulate the best and most efficient way to do this...

I have a user login system where users can upload files and assign those files to specific folders. Administrators can create folders and sub-folders. Let's say though the administrator decides to create a sub-folder five folders deep. I'd like the list the folders out in an unordered list, and the sub-folders would obviously be an indented to the parent list item.

My database table is set-up as so...

+---------+------------+-----------------+
| folder_id | folder_name | parent_folder_id |
+---------+------------+-----------------+
| 1 _____ | Animals ____| 0 _____________|
| 2 _____ | Cats ______ | 1 ____________ |
| 3 _____ | Tigers _____ | 2 ____________ |
| 4 _____ | Lions ______ | 2 ____________ |
+--------+--------------+----------------+

I need the unordered list to display:

- Animals
- Cats
- Tigers
- Lions

I cannot formulate the code so that additional indented unordered lists will appear if the admin were to add subfolders under the Tigers folder.

Does anyone have any ideas? I would really appreciate any and all help.

I've been working at this kind of issue at quite a while, so I'll be curious to see if someone does have a mySQL solution using the same sort of DB setup you've presented, but...

The only mySQL method I've found of doing this is a technique (whose name I cannot remember) where you provide left and right bounds, basically creating a bubble setup. So you make two columns, left and right, and for your situation:

1 ---------- Animals ----------- 8
2 ----------- Cats -------------7
3 --- Lions --- 4 5 --- Tigers --- 6

So if you wanted the children of Cats, you'd select where left > 2 and right < 7. And if you sort by left, you get the order of the categories in the order they're presented, so you can just check left/right values to figure out if the element is a child, sibling, etc.

The issue I found with this technique is when you change an elements location (say you make a new folder, endangered cats and move Tigers there), you have to change a lot of numbers, basically update a lot of rows. Or worse, if you move cats out of animals for whatever reason, a lot of recalcuating and updating.


Right now, I do this kind of stuff in PHP. I have a column called heritage, where I store the entire heritage of a folder/forum so I can do quick references and get parent/child data easily. In your case, I would have this kind of data:

001
001-002
001-002-003
001-002-004

So if I wanted the children of cats, I could do a heritage LIKE '%002-%'. And if I order on heritage, I get all the first level folders first, the second level folders next, etc. So by looping in PHP, I can store all the parents into an array, then store the children into a subarray on the parents, then just a recursive loop to pull all the data.