带有MPTTA层次结构的简单PHP菜单

Question

I am attempting to create a simple unordered list menu using PHP. The list is populated from MySQL. I am having a lot of trouble finding instructions that match my specific needs.

Requirements

1. I am trying to avoid self-referencing functions.
2. I am trying to do this with one query for performance efficiency.
3. I am using a modified preordered tree traversal algorithm for displaying the menu.
4. Each row must be surrounded by a list item tag.
5. Lists must be opened and closed where nodes begin and end

Code:

CREATE TABLE IF NOT EXISTS `maj_topmenu` (
`menu_id` int(11) NOT NULL auto_increment,
`menu_title` varchar(100) NOT NULL default '',
`menu_url` varchar(200) NOT NULL default '',
`menu_level` int(10) NOT NULL default '0',
`parent_id` int(11) NOT NULL default '0',
`lft` int(10) NOT NULL,
`rgt` int(10) NOT NULL,
`displayorder` int(11) default NULL,
PRIMARY KEY  (`menu_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

INSERT INTO `maj_topmenu` (`menu_id`, `menu_title`, `menu_url`, `menu_level`, `parent_id`, `lft`, `rgt`, `displayorder`) VALUES
(1, 'Home', '/home', 0, 0, 1, 2, 0),
(2, 'About Us', '/about', 0, 0, 5, 10, 10),
(3, 'News', '/news', 0, 0, 3, 4, 5),
(4, 'Blogs', '/viewblog', 0, 0, 11, 12, 15),
(5, 'Contact Us', '/contact', 0, 0, 13, 14, 20),
(6, 'Sub Menu 1', '/link1', 1, 2, 6, 9, 0),
(7, 'Sub Menu 2', '/link2', 2, 6, 7, 8, 0);

PHP Part

$topmenu = '';
$nodes=array();
$nlbr = "
";

// Now, retrieve all descendants of the $root node
$sql="SELECT n.menu_title, n.menu_title, n.menu_url, n.parent_id, n.lft, n.rgt FROM maj_topmenu AS n, maj_topmenu AS p WHERE n.lft BETWEEN p.lft AND p.rgt AND p.parent_id = '0' ORDER BY n.lft";
$result = mysql_query($sql) or die(mysql_error());

$topmenu .= '<ul>' . $nlbr;
while ($row = mysql_fetch_array($result))
{
    if ( ($row['rgt'] - $row['lft']) == 1 )
    {
        // No child elements
        if (sizeof($nodes) == 0)
        {
            // We're at the top with no children
            $topmenu .= '<li>'.$row['lft'].' <a href="'.$row['menu_url'].'">'.$row['menu_title'].'</a> '.$row['rgt'].'</li>' . $nlbr;
            continue;
        }
        else
        {
            // We're in the middle with no children; We'll need to end at least one branch
            $topmenu .= '<li>'.$row['lft'].' <a href="'.$row['menu_url'].'">'.$row['menu_title'].'</a> '.$row['rgt'].'</li>' . $nlbr;
            while (  ($row['rgt'] + 1) - end($nodes) == 0  )
            {
                $topmenu .= '</ul>' . $nlbr;
                array_pop($nodes);
            }

            continue;
        }
    }
    else
    {
        // Start a new branch
        $topmenu .= '<li>'.$row['lft'].' <a href="'.$row['menu_url'].'">'.$row['menu_title'].'</a> '.$row['rgt'].'</li>' . $nlbr;
        $topmenu .= '<ul>' . $nlbr;

        // End leaf at this point later
        $nodes[] = $row['rgt'];
    }
}
$topmenu .= '</ul>';

The issue I am having is that the loop doesn't end the nodes in the proper places. If I'm on a 3rd level branch, it may end either too many or too few lists.

This script may be used later for extensive hierarchy data which is why I went with the MPTTA for this and I'm trying to avoid having to do queries at numerous levels.

Answer 1

I've edited this answer to improve it.

1. It uses one sql query instead of three

2. It uses "order by" so you are sure the preorder travel is respected, this means you can define the traversal path only by editing level and order, consider level as moving top-down and order as moving left to right.

3. ..list item tag.. Do you mean something like 1.1, 1.2 etc... if this is the case then it's done.

4. ..where nodes begin and end... Do you mean that the level on reference is fully shown, and other level are not (they are close), if this is the case it's ok.

5. ..self-reference... If you mean the function calling it self, I really cannot see where this happens (including the code before edit)??! The links here can be formated and used as you wish!

Every time you load the page the level function is executed, if you don't want this happen at all or that often then you can go with http request - ajax!

Or you can use a custom cache, for example store menu content in session, or transfer from page to page (a communication cost you pay anyway, loaded with a bit more text makes no difference) the menu content, then if the current link is on the same level then you use the cached menu, if not you run a fresh query. This way you can reduce db communication.

  <?php


// Make a MySQL Connection
mysql_connect("localhost", "root", "") or die(mysql_error());

//select database
mysql_query("drop database if exists `my_test`;")or die(mysql_error());  
mysql_query("create database `my_test`;")or die(mysql_error());  
mysql_select_db("my_test") or die(mysql_error());

//create table 
mysql_query(" drop table if exists `menu_item`;")or die(mysql_error());  
mysql_query("   
CREATE TABLE `menu_item` (
    `item_id` MEDIUMINT(8) UNSIGNED NOT NULL  ,
    `item_title` VARCHAR(100) NOT NULL ,
    `item_url` VARCHAR(200)   NULL  ,
    `item_level` TINYINT UNSIGNED NOT NULL   ,
    `order` TINYINT UNSIGNED NOT NULL   , 

    PRIMARY KEY (`item_id`) 
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;") or die(mysql_error());  





 mysql_query("INSERT INTO `menu_item`  
VALUES (0, 'titile_000', 'http://www', 0, 0  ) ") or die(mysql_error());   
 mysql_query("INSERT INTO `menu_item`  
VALUES (1, 'titile_111', 'http://www', 0, 1  ) ") or die(mysql_error());   
 mysql_query("INSERT INTO `menu_item`  
VALUES (2, 'titile_222', 'http://www', 0, 2  ) ") or die(mysql_error());  
 mysql_query("INSERT INTO `menu_item`  
VALUES (3, 'titile_333', 'http://www', 0, 3  ) ") or die(mysql_error());  

 mysql_query("INSERT INTO `menu_item`  
VALUES (4, 'titile_444', 'http://www', 1, 0  ) ") or die(mysql_error());  
 mysql_query("INSERT INTO `menu_item`  
VALUES (5, 'titile_555', 'http://www', 1, 1 ) ") or die(mysql_error());  
 mysql_query("INSERT INTO `menu_item`  
VALUES (6, 'titile_666', 'http://www', 1, 2 ) ") or die(mysql_error());  
 mysql_query("INSERT INTO `menu_item`  
VALUES (7, 'titile_777', 'http://www', 1, 3  ) ") or die(mysql_error());  

 mysql_query("INSERT INTO `menu_item`  
VALUES (8, 'titile_888', 'http://www', 2, 0  ) ")  or die(mysql_error());  
 mysql_query("INSERT INTO `menu_item`  
VALUES (9, 'titile_999', 'http://www', 2, 1  ) ") or die(mysql_error());    
 mysql_query("INSERT INTO `menu_item`  
VALUES (10, 'titile_010', 'http://www', 2, 2  ) ") or die(mysql_error());    





function  get_level($level )
{ 

    //get level     
    $result = mysql_query("SELECT `item_title`, `item_url`, `item_level`, `order` FROM `menu_item`  
       where `order` = '0' or `item_level` ='{$level}'  order by  `item_level` , `order` ;")
       or die(mysql_error());   

    $output='<b>_____MENU_____</b><br>';

    while($row = mysql_fetch_row($result)) 
    {
        $prefix=($row[3]==0)?$row[2].'.&nbsp;':'&nbsp;&nbsp;&nbsp;'.$row[2].'.'.$row[3].'&nbsp;'; 
        $output.=  $prefix.$row[0].' | '.$row[1].'<br>'; 
    }
    echo $output;   
}


get_level(0);



?>