make a file directory in php & mySQL

Question

I am currently making a file manager system I store all the folders names and files in the database (MySQL)I am trying to add folders in the database and then add sub folders to them,I wanted it to show all the folders to show in there correct position, Here is a eg:

  Folder1
    Folder1-Sub1
       Folder1-Sub1-Sub1
       Folder1-Sub1-Sub2
       Folder1-Sub1-ect...
    Folder1-Sub2
    Folder1-Sub3
    Folder1-ect...

  Folder2
    Folder2-Sub1
    Folder2-sub2

  ect...

I currently have the mysql table layed out like this

 id   folder_name           sub_folder_id    file_name    file_folder_id
  1     Folder1                    -1             -1              -1
  2     Folder1-Sub1                1             -1              -1
  3     Folder1-Sub1-Sub1           2             -1              -1
  4     Folder1-Sub1-Sub2           2             -1              -1
  5     Folder1-Sub2                1             -1              -1
  6     Folder1-Sub3                1             -1              -1
  7     Folder2                    -1             -1              -1
  8     Folder2-Sub1                7             -1              -1
  9     Folder2-Sub2                7             -1              -1

Here is the following code that I have so far

    $GetFolders = mysql_query("SELECT * FROM user_filesfolders");
                    $file_tree = "";    
                    while($ShowFolders = mysql_fetch_array($GetFolders))
                    {
                        if($ShowFolders['folder_name']==-1){
                            //Dont Add Becuse it not a folder
                        }else{
                                    $file_tree .=  '  
                                     <tr>
                                     <td height="30" colspan="4"><strong>
                                     <input type="checkbox" name="checkbox[]" class="folder_checkbox" id="-1" />
                                    <span class="tree_drop" id="-1">
                                    <img src="images/Folder.png" width="15" height="21"  /> <span id="status">+</span> '.$ShowFolders['folder_name'].'</strong>
                                    </span>
                                    </td>
                                    </tr>     ';


            //I need to keep adding floders to folder from mysql

                                    $file_tree .=  '  
                                     <tr>
                                     <td height="30" colspan="4"><strong>
                                     <input type="checkbox" name="checkbox[]" class="folder_checkbox" id="-1" />
                                    <span class="tree_drop" id="-1">
                                    <img src="images/Folder.png" width="15" height="21"  /> <span id="status">+</span> '.$ShowFolders['folder_name'].'</strong>
                                    </span>
                                    </td>
                                    </tr>     ';
                                        }


                    }
                    }

Can someone help me out please or lead me down the right path

What doesn't work? Is this just a question about formatting the HTML, or are you having other problems? — kijin, Oct 30 '10 at 06:45
at the moment i am getting the folders what is not a sub folders and then i want to add the sub folders from mysql the same table to the right folder and i want to do it for all the sub folders in the table so put sub folders in sub folders — Rickstar, Oct 30 '10 at 06:51
In mysql schema sub_folder_id column actually contains reference to PARENT folder. Column name confuses a little :) — Kel, Oct 30 '10 at 06:53
Your code is not very well formatted, which makes it hard to read and therefore error prown. Also you're not consistent with your variable naming. Don't use capitals in the beginning, if you use capitals, don't use underscores for other vars. $fileTree, not $FileTree, not $file_tree. — markus, Oct 30 '10 at 07:18

score 2 · Answer 1 · answered Oct 30 '10 at 06:53

Some suggestions:

"sub_folder_id" should be "parent_id", because it's really the ID of the parent folder that you're storing there.
Use null instead of -1 to imply "none" or "not applicable".
"Folder1-Sub1" is actually the full name of the subfolder, right? (No problem there.)
What are the "file_name" and "file_folder_id" columns doing there? Files don't need a separate treatment, they have a name and a parent folder (ie. the folder where they belong) just like subfolders. Treat files like folders, and just add a separate column to indicate the type of the entry: 'file', 'dir', 'link', etc.

score 1 · Answer 2 · answered Oct 30 '10 at 06:50

does your structure have more than one nesting level? your approach is not quite correct. for representing tree structure like your as html you must at least use recursive algorithm. as for me, it's better to map returned dataset to php array and implement deep-search function.

score 0 · Answer 3 · edited Oct 30 '10 at 07:34

Here is a recursive solution by which you can make a neat an clean solution which will allow you to print unlimited depth of tree. I am writing the algorithm, you can easily put the code:

function GetTree($parentId)
{
   $html=''; 
   $childHtml='';
   1. Get all the children of the parent ID got
   2. foreach child, 
      $childHtml.=GetTree($ChildId);

   //so you now have all the child html. you need to wrap it into this parent html now.

   3. if($parentId==-1)
      $html=childHtml;
   4. else
      $html=$some_html_code_for_this_folder.$html.$some_other_html_code_for_this_folder

   return $html;

}


//use
echo GetTree(-1);

make a file directory in php & mySQL

3 Answers3