MySQL Forums
Forum List  »  Optimizer & Parser

Tree subtreewalk optimice
Posted by: Jonas Fischer
Date: July 14, 2011 08:16AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Tree subtreewalk optimice
2262
July 14, 2011 08:16AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.