Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    Regular Coder
    Join Date
    May 2008
    Thanked 1 Time in 1 Post

    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.

  • #2
    Senior Coder
    Join Date
    Jun 2008
    New Jersey
    Thanked 259 Times in 256 Posts
    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:


    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.


    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts