Web Analytics Made Easy -
StatCounter Virtual Folder problem - CodingForum


No announcement yet.

Virtual Folder problem

  • Filter
  • Time
  • Show
Clear All
new posts

  • Virtual Folder problem

    Hi, in my database i have a table which stores a directory structure

    folderstructure table
    id, foldername, parentid

    parentid points to its parents "id" ( )

    i need to retrieve this info so that its sorted by foldername and its children are listed below also sorted by foldername; there can be a infinite amount of nested folders.... im not really sure how to go about this; i was hoping that maybe theres some magical sql statement that can do this for me

    i sort of dont want to put this info in a array then sort it with recursion (which i tried and failed), nor do i want loads of sql queries


  • #2
    I've written code once, that did exactly that + had some userpermission system bined into it.

    You can read more then you'de like here.

    I'll see this evening if i still have the code on my machine at home and send you the relevant pieces. If i remember correctly, it's only a few dozen lines of code.

    I think i did use an additional variable with the tree level in it. (I should have a small script to automatically fill in that variable)

    Or else you can run a search at http://www.phpclasses.org/search.htm...ee&go_search=1

    Let me know if i need to dig up my code.
    Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


    • #3
      heres what ive been trying; $fstruct is the array in which the sorted list should be in; $temp is the array to sort; $fcounter is the size of $temp

      this will probably give anyone reading it a headache, feel free not to read it hehe

      PHP Code:
        // hooray for recursion!
        // sorry for the bad variable names
      function sortfolders($fstructcount$i$j$fstruct$temp$fcounter)
      $j $fcounter)
      $i$i $fcounter$i++)
      $temp[$i][2] == $temp[$j][1]) && ($temp[$i][2] != 0))
      $temp[$i][0]." ".$temp[$j][0]." <br>";
      $fstruct[$fstructcount][0] = $temp[$i][0];
      $fstruct[$fstructcount][1] = $temp[$i][1];
      $fstruct[$fstructcount][2] = $temp[$i][2];
      $fstruct[$fstructcount][0] = $temp[$i][0];
      $fstruct[$fstructcount][1] = $temp[$i][1];
      $fstruct[$fstructcount][2] = $temp[$i][2];
      $j $j 1;
      sortfolders($fstructcount++, 0$j$fstruct$temp$fcounter);
      what i have yet to add are some dashes to make it look good in a drop down box



      thats what i got so far; ill work on it some more and reply again if i need that code of yours... thanks
      Last edited by Roost3r; Feb 23, 2004, 10:15 AM.


      • #4
        Don't find the code anymore must have been deleted when cleaning up.

        But it was a completely diffrent approach anyway. To get the dashes --> don't you have some 'directory-level' inside one of the loops? See $l is that level (1 being the root, 2 the roots subdirs etc), then you could have

        echo str_repeat('-',$l);
        Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


        • #5
          im still having problems with this however i have made some progress... recursion wasnt working out at all so i just tried a few for statements... it got me farther but theres still a problem

          PHP Code:
            function check($temp$count$fcounter)
          $i 0$i $fcounter$i++)
          $temp[$i][1] == 0)
          $line 0;
          $folderlist[$count][0] = $temp[$i][0];
          $folderlist[$count][1] = $temp[$i][1];
          $folderlist[$count][2] = $temp[$i][2];
          $j 0$j $fcounter$j++)
          $temp[$i][2] == $temp[$j][1])
          $line 0)
          $folderlist[$count][0] .= "-";
          $folderlist[$count][0] .= $temp[$j][0];
          $folderlist[$count][1] = $temp[$j][1];
          $folderlist[$count][2] = $temp[$j][2];
          the output is in the format:

          (name) (parentid) (id)

          abc 0 9
          -defg 9 11
          Default 0 1
          FAQ's 0 5
          Old News 0 4
          SS:2845 0 3
          T:V 0 2
          Test 0 6
          -test 6 10
          -testsub1 6 7
          -testsub2 6 8
          -subsubtest 10 12

          it works somewhat however the subsubtest folder at the bottom is a sub folder of "-test"

          im thinking maybes theres a way to join the table on itself to sort it in a sql query maybe

          i remember reading somewher about this problem, wish i knew where

          it needs to

          1. sort folders alphabetically
          2. have sub folders beneath the parent and organized alphabetically
          3. have a sequence of dashes next to the name to show the depth



          i would be willing to make a "donation" to anyone who could help


          • #6
            So why don't you simply add and extra column with the tree-level? Like

            (name) (parentid) (id) (level)
            abc 0 9 1
            defg 9 11 2
            Default 0 1 1
            FAQ's 0 5 1
            Old News 0 4 1
            SS:2845 0 3 1
            T:V 0 2 1
            Test 0 6 1
            test 6 10 2
            testsub1 6 7 2
            testsub2 6 8 2
            subsubtest 10 12 3

            And then you don't need any code at all to build the dashes. Then it's just
            echo str_repeat('-',$row['level']);

            Adding the level is easy.
            You first run a select on all records where parentid=0. You then build a collection with their id.
            then an update for all records in the collection. Then a select for all records where parentID In ($collection). You then build a new collection and do the update etc etc.

            So it's just a for loop like (untested so their could be small bugs)
            PHP Code:
            $coll '0';
            for (
            $i=1$i<20$i ++){
            $select="SELECT id FROM table WHERE parentid In ("$coll ")";
            $result mysql_query($select$link) or die ('Queryproblem in   iteration ' $i ': 'mysql_error());
               if (
            mysql_num_rows($result) >= 1){
            while ($row=mysql_fetch_assoc($result)){
            $coll .= ',' $row['id'] ;
            $upd "UPDATE table SET level = " $i " WHERE id In ("$coll .")";
            $result mysql_query($upd$link) or die ('Queryproblem in   update level ' $i ': 'mysql_error());

            Last edited by raf; Feb 27, 2004, 03:52 AM.
            Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html


            • #7
              ya i could add that extra field that wouldnt help my sorting function

              see the last
              subsubtest 10 12 3

              that should be underneath
              test 6 10 2

              (test's id is 10, subsubtest's parentid is 10)

              thats why the for loop doesnt work for more than 1 depth lists... because its not recursive

              what the for loop does is go through the list from the top to bottom each element at a time putting its subfolders underneath... (however it doesnt check for the subfolder subfolders)

              when it reaches the Test folder which is at the root, it sees that "test", "testsub1" and "testsub2" are children and place them beneath without checking if they themslves have children

              that list should look like this

              Old News

              i know using your method there with the lists would work the same way to retrieve and order but i dont want each page having 20+ queries or more depending on how much folders a person creates

              maybe i can just output the format to a text file each time someone creates a directory
              Last edited by Roost3r; Feb 27, 2004, 10:08 AM.


              • #8
                I understand your problem. The approach i took/followed in the thread i initially refered to, was to show only the root-directorys. And if you clicked on a rootdirectory, then it unfolded, and you got all rootdirectorys, but between the selected one and the next rootdirectory, you got the subdirectorys. Like in windows explorer.

                For your routine if you use a recursive function, or if you work with nested selects (which i think would be the only real alternative) then you will burn a lott of extra resources.

                It depends on how many levels deep your tree goes, but i fear that you will need a lott of selects tobuild up the complete tree at once.

                It would then be better to write such a script + include a 'displayorder' column in yout table and have the script fill in that displayorder.
                You then need to rerun that script each time the tree is changed, but you will save a lott of runtime resources.
                Last edited by raf; Feb 27, 2004, 10:24 AM.
                Posting guidelines I use to see if I will spend time to answer your question : http://www.catb.org/~esr/faqs/smart-questions.html