Tree subtreewalk optimice

[Jonas Fischer]

So I have the following Problem:
I’m building a mysql based file system and I want to make it a fast one - (because it´s my second that’s the reason why I wrote a new one).

What I want to do is I want to know all the folders contained in a folder and for eatch folder found on this way again the folders this one contains and so on.

i have one table gives for the files (and folders), which contains: id, name, type(1=folder, 2=file), path (connection to a id of a folder), ...

Then I have another table witch only contains folders (the id in this table is always equal with the id of the folder in the other table, it´s connected to), here I save additional data (you do not need to know more about that).



So a unperformant solution would be that (PHP):


function getFoldersInFolder ( $folder_id, $results = array() ) {
$q = mysql_query('SELECT if_id FROM wosifile WHERE if_typ=1 AND if_path='.$folder_id);

while($o = mysql_fetch_result($q)) {
array_push($results, $o->if_id);
$results = getFoldersInFolder($o->if_id, $results);
}
mysql_free_result($q);

return $results;
}

// 2 is just a example
print_r(
getFoldersInFolder( 2 ) );



Another unperformant way would be (short form, some things had been removed):

// "/test" is just a example
$folder_path = '/test';

$q = mysql_query('SELECT ii_id FROM wosidir WHERE ii_strpath LIKE "'.addslashes($folder_path).'%"');
$results = array();

while($o = mysql_fetch_result($q)) {
array_push($results, $o->if_id);
}
mysql_free_result($q);

print_r($results);



So my question:
Is there any way to do this in mysql without using strings (second example) AND
without using many queries AND
without changing my databases??

Im looking forward to reading your answer!



Edited 1 time(s). Last edit at 07/14/2011 08:19AM by Jonas Fischer.