Virtual Folder problem

[Roost3r]

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

tia

[raf]

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.

http://www.codingforum.net/showthread.php?s=&threadid=24780&highlight=tree

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.

[Roost3r]

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

  // hooray for recursion!
  // sorry for the bad variable names
  function sortfolders($fstructcount, $i, $j, $fstruct, $temp, $fcounter)
  {
    while($j < $fcounter)
    {
       for($i; $i < $fcounter; $i++)
       {
           if(($temp[$i][2] == $temp[$j][1]) && ($temp[$i][2] != 0))
           {
              echo $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];
              $fstructcount++;
           }
       }
       $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

IE:

Home
-Articles
-Headlines
--Headline1
--Headline2
-Zips
Links

thats what i got so far; ill work on it some more and reply again if i need that code of yours... thanks

[raf]

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);

[Roost3r]

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

  function check($temp, $count, $fcounter)
  {
        for($i = 0; $i < $fcounter; $i++)
        {
            if($temp[$i][1] == 0)
            {
               $line = 0;
               $folderlist[$count][0] = $temp[$i][0];
               $folderlist[$count][1] = $temp[$i][1];
               $folderlist[$count][2] = $temp[$i][2];
               $count++;
            }
            for($j = 0; $j < $fcounter; $j++)
            {
                if($temp[$i][2] == $temp[$j][1])
                {
                        $line++;
                        while($line > 0)
                        {
                          $folderlist[$count][0] .= "-";
                          $line--;
                        }
                        $folderlist[$count][0] .= $temp[$j][0];
                        $folderlist[$count][1] = $temp[$j][1];
                        $folderlist[$count][2] = $temp[$j][2];
                        $count++;
                }
            }
        }
 } 

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

IE

Home
-subHome
--subsubHome
-zHome
Home2

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

[raf]

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)

$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){
      $coll='99999999999999'
      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());
   }else{
      break;
   }
} 

[Roost3r]

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

abc
-defg
Default
FAQ's
Old News
SS:2845
T:V
Test
-test
--subsubtest
-testsub1
-testsub2

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

[raf]

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.