[UPHPU] Hierarchical Menu Ideas

Mon Dec 5 17:30:48 MST 2005

Here's a thread recap, with objective, summary, methods, results (so 
far), and conclusion

OBJECTIVE:

I have a static hierarchical menu I wrote in JavaScript.  I borrowed the 
code from here:

  http://javascript.internet.com/navigation/click-to-expand-menu.html

I use it for my main menu on my website:

  http://mscis.org

I want to generate this menu with a MySQL hierarchy scheme described in 
the section entitled "Finding the Depth of the Nodes" here:

  http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

I'm using the nested set model because of the several advantages it has 
over the traditional categorization scheme.

THREAD SUMMARY

Here's my category table:

-- Table structure for table `categories`
-- 

CREATE TABLE `categories` (
  `Created` datetime NOT NULL,
  `Updated` timestamp NULL default CURRENT_TIMESTAMP,
  `CategoryID` smallint(6) NOT NULL auto_increment,
  `CategoryName` varchar(20) NOT NULL,
  `LeftValue` smallint(6) NOT NULL,
  `RightValue` smallint(6) NOT NULL,
  `Description` text NOT NULL,
  PRIMARY KEY  (`CategoryID`),
  UNIQUE KEY `UniqueLeft` (`LeftValue`),
  UNIQUE KEY `UniqueRight` (`RightValue`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Web Page Categories' 
AUTO_INCREMENT=9 ;

Full data dump available on request, the hierarchy looks like this:

TechWriting
    Hardware
    Network
ProjectManagement
    phpMyResume
WebMaster
    FamilyPages

METHOD 1:

My original php code had a syntax error (else does not use parameters):

Here's the revised code:

 $mainMenuQuery="(
 SELECT
  COUNT(`parent`.`CategoryName`) - 1 AS `depth`,
  `node`.`CategoryName` AS `Category`
 FROM `web`.`categories` `node`, `web`.`categories` `parent`
 WHERE
  `node`.`LeftValue` BETWEEN `parent`.`LeftValue` AND `parent`.`RightValue`
  AND `parent`.`LeftValue` > 1
 GROUP BY `node`.`CategoryID`
 ORDER BY `node`.`LeftValue`
 )";

 $mainMenu = mysql_query($mainMenuQuery);

 echo "<ul id=\"containerul\">\r\n";
 while($row = mysql_fetch_row($mainMenu)) {
  for($i=0; $i < mysql_num_fields($mainMenu); $i++) {
   if($row[0] = 0) {
    echo "<li>$row[1]";
   } elseif($row[0] > $row[0]--) {
    echo "</li>\r\n<ul>\r\n <li>$row[1]";
   } else {
    echo "</li>\r\n</ul>\r\n<li>$row[1]";
   }
  }
 }
 echo "</li>\r\n</ul>";

RESULT 1:

This produces doubles of every list item, and problematic html code:

<ul id="containerul">
</li>
</ul>
<li>TechWriting</li>
</ul>
<li>TechWriting</li>
</ul>
<li>Hardware</li>
</ul>
<li>Hardware</li>
</ul>
<li>Network</li>
</ul>
<li>Network</li>
</ul>
<li>ProjectManagement</li>
</ul>
<li>ProjectManagement</li>
</ul>
<li>phpMyResume</li>
</ul>
<li>phpMyResume</li>
</ul>
<li>WebMaster</li>
</ul>
<li>WebMaster</li>
</ul>
<li>FamilyPages</li>
</ul>
<li>FamilyPages</li>
</ul>

METHOD 2:

Ryan Oldham suggested that $row-- wasn't doing what I thought it was 
doing.  He suggested using ($row - 1) instead.

I replaced this line in the above code:

   } elseif($row[0] > $row[0]--) {

with this:

   } elseif($row[0] > ($row[0] -1)) {

RESULT 2:

This also produces doubles of every list item, and problematic html code:

<ul id="containerul">
</li>
<ul>
 <li>TechWriting</li>
<ul>
 <li>TechWriting</li>
<ul>
 <li>Hardware</li>
<ul>
 <li>Hardware</li>
<ul>
 <li>Network</li>
<ul>
 <li>Network</li>
<ul>
 <li>ProjectManagement</li>
<ul>
 <li>ProjectManagement</li>
<ul>
 <li>phpMyResume</li>
<ul>
 <li>phpMyResume</li>
<ul>
 <li>WebMaster</li>
<ul>
 <li>WebMaster</li>
<ul>
 <li>FamilyPages</li>
<ul>
 <li>FamilyPages</li>
</ul>

METHOD 3:

Dave Boucha suggested using a variable to hold the previous row's 
value.  Here's the code I tested:

 $mainMenu = mysql_query($mainMenuQuery);
 $prevRow = '0';

 echo "<ul id=\"containerul\">\r\n";
 while($row = mysql_fetch_row($mainMenu)) {
  for($i=0; $i < mysql_num_fields($mainMenu); $i++) {
   if($row[0] = 0) {
    echo "<li>$row[1]";
   } elseif($row[0] > $prevRow) {
    echo "</li>\r\n<ul>\r\n <li>$row[1]";
   } else {
    echo "</li>\r\n</ul>\r\n<li>$row[1]";
   }
  }
  $prevRow = $row[0];
 }
 echo "</li>\r\n</ul>";

RESULT 3:

This seemed to produce the same output as method 1:

<ul id="containerul">
</li>
</ul>
<li>TechWriting</li>
</ul>
<li>TechWriting</li>
</ul>
<li>Hardware</li>
</ul>
<li>Hardware</li>
</ul>
<li>Network</li>
</ul>
<li>Network</li>
</ul>
<li>ProjectManagement</li>
</ul>
<li>ProjectManagement</li>
</ul>
<li>phpMyResume</li>
</ul>
<li>phpMyResume</li>
</ul>
<li>WebMaster</li>
</ul>
<li>WebMaster</li>
</ul>
<li>FamilyPages</li>
</ul>
<li>FamilyPages</li>
</ul>

METHOD 4:

Fred Larsen suggested a completely different approach, which resulted in 
this code (I fixed one error in the select statement):

$mainMenuQuery="(
 SELECT
  `node`.`RightValue`,
  `node`.`LeftValue`,
  `node`.`CategoryName` AS `Category`
 FROM
  `web`.`categories` `node`,
  `web`.`categories` `parent`
 WHERE
  `node`.`LeftValue` BETWEEN `parent`.`LeftValue` AND `parent`.`RightValue`
  AND `parent`.`LeftValue` > 1
 ORDER BY `node`.`LeftValue`
)";

$mainMenu = mysql_query($mainMenuQuery);
$right = array();

echo "<ul id=\"containerul\">\r\n";

while($row = mysql_fetch_row($mainMenu)) {
 // only check stack if there is one
 if (count($right) > 0) {
 // check if we should remove a node from the stack
  while (count($right) && $right[count($right)-1] < $row[0]) {
   echo "</ul></li>\r\n";
   array_pop($right);
  }
 }
 echo '<li>' . $row[2];
 if (($row[0] - $row[1]) > 1) {
  echo '<ul>';              // new branch
 } else {
  echo "</li>\r\n";       // leaf node close
 }
 $right[] = $row[0];
}
  // finish the tree
if (count($right) > 0) {
 while (count($right)) {
  echo "</ul></li>\r\n";
  array_pop($right);
 }
}
  // complete the opening ul
echo "\r\n</ul>";

RESULT 4:

The result was again doubles of every list item, and problematic html code:

<ul id="containerul">
<li>TechWriting<ul><li>Hardware</li>
<li>Hardware</li>
</ul></li>
</ul></li>
<li>Network</li>
<li>Network</li>
</ul></li>
</ul></li>
</ul></li>
<li>ProjectManagement<ul><li>phpMyResume</li>
<li>phpMyResume</li>
</ul></li>
</ul></li>
</ul></li>
<li>WebMaster<ul><li>FamilyPages</li>
<li>FamilyPages</li>
</ul></li>
</ul></li>
</ul></li>
</ul>

CONCLUSION:

So far, each method produces results, but none of them achieve the 
objective yet.  Now that I'm getting results, I can see the how the code 
produces what it does, and I'm working on some revisions to make it 
work, but I have to run off now, and can't properly summarize what's 
going on.  In the mean time, I welcome any new ideas.

Thank you,

Brandon Stout
http://mscis.org