Database-driven site with 6-level tree structure

[sarah]

I am working on a site with at least 1,000 pages that are in a tree structure with at least 6 levels (MySQL database). Some of the pages have content and some of them are just directories of the pages beneath it.

Apes
-- Grandpa
----- Mom
-------- Jimmy
----------- Doll
----------- Teddy Bear
-------------- Button
-------- Lucy
-------- Bobby
-- Grandma
----- Mom
-------- Bobby
----------- Crafts
----- Dad
-------- Billy
----------- Trucks

Starting off, I structured the database so that each entry had the following:

pageID - Unique ID for the page
pageTitle - Name of the page
pageLevel - The level of the page (1-6)
prevLevelID - The page ID of the level above it.
pageInclude - The file to be included.

I was a little tentative about this structure, but it worked ok until I needed to print out a site map in a tree format. The number of queries is out of this world...

If anyone has any suggestions for printing out the site map, I would appreciate it. Or...better yet...if anyone has come up with a good solution for a similar situation, I would love some alternative ideas.

[sadrik]

Hi,

I don't know, how your information about the tree is arranged.
If you are able to arrange it some like that:

$aTreeInfo = array(1 => array ( "TITLE" => "MyPage1", "LEVEL" => 1,"URL" => "secret1.html", "FATHER" => 0),2 => array ( "TITLE" => "MyPage2", "LEVEL" ) );

Then you can use a recursive function call:
print "<pre>";
buildTree();
print "</pre>";

function buildTree()
{
global $aTreeInfo;
foreach($aTreeInfo as $iID => $aInfo) {
if($aInfo[FATHER] == 0)
prtSons($iID);
}
} // buildTree
function prtSons($iID)
{
global $aTreeInfo;
global $iElements;
for($i = 0;$i < $aTreeInfo[$iID][LEVEL];$i++)
print "-";
print $aTreeInfo[$iID][TITLE]."LINK: ".$aTreeInfo[$iID][URL]."</br>\n";
for($i = 0; $i < $iElements;$i++){
if($aTreeInfo[$i][FATHER] === $iID)
prtSons($i);
} // foreach
} // prtSons
But the problem is, that you have to search through the hole Array "aTreeInfo" for every element is contains.

[sarah]

Thank you for your suggestion. Due to some other new requirements, I ended up just changing the prevLevelID column to a subPages column. Instead of noting the ID of the page above it, it records the ID's of all of the pages directly below it.

I am sure it is not the absolute best way, but if anyone runs into this situation in the future, this solution works. (As a bonus, it is an easy way to determine the order of the sub-pages too.)

[trooper]

Sara

I have done something very similar. I simply had a "parent" field in each record that pointed to the id of the parent section the sub section belonged to.

The top level sections had a parent of 0.

As for the site map you could write a recursive function that looks for the section's id as their parent (essentially top -> down or go the other way bottom ->up).

It does take a bit of getting right but when you do its well worth it

If you need further assistance post your requrest here

HTH

GM