[thelist] [DB Design] Recursive Directory Structure

[Hans Fraser]

(sorry this is a long e-mail)

On Saturday, February 1, 2003, at 08:53  PM, Ken Kogler wrote:

>> You can do this quite easliy if you approach it as a recursive data
>> structure. You need to have a way to point one row at another in the
>> same table (child to parent) and to determine when you've reached
>> home.
>
> Yeah, but the performace hit scares me -- this table would be pretty
> big, and the server isn't the most robust piece of hardware I own. :)
>
> Is there a way to do this with JOINing the table on itself?

I just did a similar project ! but not with picture but with url!
or if you prefer a favorite system on an lms it is in php
and i am ready to share the code if you are interested

the approach is pretty simple

first you need to decide if this directory structure is the base of
your system.
do you go to a certain topic and then give the ability to surf
directory like?
i personally think that the 2 column approach is a little bit over
simplified.

i will go with the favorites example!
2 tables 1 for your links and one for your folders

the folders table something like:

id , 	name , 	position , 	user_id
1	folder 1	0		1
2	folder 2	1		1
3	folder 3	2		1
4	folder 4	1		1


with a link table :

id, 	name, 	position, 	url, 	user_id
1	fileA		0		url	1
2	fileB		0		url	1
3	fileC		1		url	1
4	fileD		1		url	1
5	fileE		3		url	1
6	fileF		4		url	1

then in 2 different queries you select all folders for user 1
and all files for user 1 (i convert them to associative arrays for
simplicity)

to display well a simple recursive function
and upload the whole thing to the user with a simple layer display
function (that way you leave your server alone at least for a while!)
you can show only the first level and when the user click on the + sign
or arrow ... whatever .. you display the layer that contains what they
do!

ex of folder recursive functions:
// to display all folder from the root call displayFolders with
$myLevel = 0

function displayFolders($myLevel, $myFolderArray, $myLinkArray){
	// loop through the folder array and find folder at $myLevel
	foreach($myFolderArray as $key => $foldLocalArray){

		if($foldLocalArray['folderPos'] == $myLevel){
			// if a folder is found print the header for the folder
			print "<span id=\"folder_".$foldLocalArray['foldId']."_name\"
style=\"position:relative; display:block\">\r";
			print "<span class=\"favLinkStyle\">\r";
			print "<img src=\"../../../common/img/tools/arrow_LbluU.gif\"
onClick=\"dispLayer('folder_".$foldLocalArray['foldId']."');\"
name=\"arro_folder_".$foldLocalArray['foldId']."\" border=\"0\"
width=\"14\" height=\"14\">";
			print
"<strong>".$foldLocalArray['foldName']."</strong></span></span>";//
			print "<span id=\"folder_".$foldLocalArray['foldId']."\"
style=\"position:relative; left:15px; display:none;\">\r";

			// find and display all the links for the folder
			displayLinks($foldLocalArray['foldId'], $myLinkArray);
			// display folders for the level (recursive function on self will do
every folders down every level)
			displayFolders($foldLocalArray['foldId'], $myFolderArray,
$myLinkArray);

			// close the folder
			print"</span>\r";
		}
	}
}


// display the links depending on the type of link print the associated
url!

function displayLinks($myLevel, $myLinkArray){
	// loop through the link array and display the link at $myLevel
	foreach($myLinkArray as $key => $linkLocalArray){

		// if the link is part of $myLevel
		if($linkLocalArray['linkPos'] == $myLevel){
			print "<a
href=\"javascript:top.popContent('common/pop/
content_a.php?ID=".$linkLocalArray['linkUrl']."&".SID."');\"
class=\"favLinkStyle\">\r";
			print "<span
id=\"my_link_".$linkLocalArray['linkId']."\">".$linkLocalArray['linkName
']."</span></a>\r";
		}
	}
}