Creating hierarchial view with MySQL - need help speeding up process

[epp_b]

OK, first let me describe what exactly I am doing...

I have three tables in a MySQL database (for which I will use hypothetical names for simplicity):

Folders (
 FolderID (primary key)
 FolderName
 ....
);

ChildTable (
 FileID (primary key)
 FileName
 ....
);

FolderAndFileLinks (
 ParentFolderID
 FolderID
 FileID
);

I am linking the files and the folders much like any file system for an operating system, except that folders and files can be inside multiple folders. Note that, in the links table, both FolderID and FileID cannot be assigned at the same time in one row (one is always NULL while the other refers to a file or folder, respectively)

I have a function that I use to return a hierarchial view of this structure:

function createHierarchy($ID) {
	$query = mysql_query("SELECT FileID, FolderID FROM FolderAndFileLinks WHERE ParentFolderID = $ID");
	if (mysql_num_rows($query) {

	$Level++;

	if ($ID = 0)
		$Level = 0;

	while ($row = mysql_fetch_assoc($query)) {
		if ($row["FolderID"]) {
			$Folder_array[] = $FolderID;
			createHierarchy($FolderID)
		}

		else if ($row["FileID"]) {
			$File_array[] = $FileID;
		}
	}
}
else
	$Level--;
}

...anyway, that's my pseudo code, and it works perfect....except that it's pretty slow when there are a lot of files and folders. Obviously, this is because it makes a query for every file and folder.

A few notes: I do not want to combine the folder and file tables (not yet anyway), and I cannot remove the links table. I have too many scripts using this function all over the place that I can't afford to change the structure right now.

So, is there any better way I can be doing this, perhaps using only a small number of queries and assigning things to arrays, or maybe using OOP, or basically anything else, as long as it's faster? 😉

Thanks!

[epp_b]

Nobody? 🙁

[konsu]

a single database query that would return the whole hierarchy will speed up your code significantly. http://willets.org/sqlgraphs.html might be helpful.

[epp_b]

Thanks, I'll take a look at it.

[leatherback]

Hi Epp,

THis is ABSOLUTELY NOT what you are doing, but it create a hierarchical branching menu, using one query. they rest tables. If you wrote the coed you posted above, you will figure this one out too, I am sure:

<?php

$menuquery = "select * from $menu_table order by M_sortorder";
   $result=mysql_query($menuquery) or die('An error occured retrieving data');
   $listing = Array();
   while($row = mysql_fetch_array($result))
     { 
     $listing[] = array("This_id" =>  $row[M_page_nr], "parent_id" => $row[M_p_nr],    "category_title" =>  $row[M_label], "link" => $row['M_link'], "path" => $row[M_path]);
     }


function display_tree($parent, $level, $array, $menu_parent, $current_viewing_page)
  {
  foreach ($array AS $node)
    {

if(($node[parent_id] == $parent) and ($node[This_id] <> $current_viewing_page))// If the parent matches the pageid in the array
  {                              // the current page is a child of this parent

  if($node[This_id] == $menu_parent) // If the current page ID matches the parent_id, this child is a direct child
    {
    $selected = "selected";
    }
  else
    {
    $selected = "";
    }

  $indent = "";
  for($i = 1; $i <= $level; $i++) 
    {
    $indent .= "-";
    }

  $html .= "
  <option $selected value=\"$node[This_id]\">$indent $node[category_title]</option>
  ";
  $html .= display_tree($node[This_id], ($level + 1), $array, $menu_parent, $current_viewing_page);
  }
}

  return $html;

  } // end function display list


// Now use it

$pageslist = display_tree(0, 0, $listing, $menu_parent, $pageid);  


  ?>