Hierarchical Data in a menu tree.

**whisher** · Apr 8, 2009 08:37

Hi.
I followed this excellent tutorial
http://www.sitepoint.com/article/hie...ta-database/2/
now I'd like to set up a menu tree (to use in jquery tree plugin)
http://jquery.bassistance.de/treeview/demo/

just tried to myself but with a very poor result

Can you give me an example or a tutorial on how
I can get it.

Bye.

**Jake Arkinstall** · Apr 8, 2009 09:23

Before we can give you any help, we'll need to see your table structure.

But, in essence, what you have to do is generate a list of master items. The children of these items are in an inner-list, etc.

**whisher** · Apr 8, 2009 10:14

Originally Posted by arkinstall

Before we can give you any help, we'll need to see your table structure.

Code SQL:

CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `parent_id` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  `lft` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  `rgt` INT(11) UNSIGNED NOT NULL DEFAULT '0',
  `ctitle` VARCHAR(255) NOT NULL DEFAULT '0',
  `description` text NOT NULL,
  `published` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=119 ;
 
--
-- Dumping data for table `categories`
--
 
INSERT INTO `categories` (`id`, `parent_id`, `lft`, `rgt`, `ctitle`, `description`, `published`) VALUES
(103, 0, 1, 32, 'Root', 'Root', '2009-04-08 00:00:00'),
(104, 103, 2, 23, 'Item 1', 'Item 1', '2009-04-08 19:32:20'),
(105, 103, 24, 25, 'Item 2', 'Item 2', '2009-04-08 19:32:35'),
(106, 103, 26, 31, 'Item 3', 'Item 3', '2009-04-08 19:32:50'),
(107, 104, 3, 8, 'Item 1.0', 'Item 1.0', '2009-04-08 19:33:29'),
(108, 104, 9, 10, 'Item 1.1', 'Item 1.1', '2009-04-08 19:33:47'),
(109, 104, 11, 22, 'Item 1.2', 'Item 1.2', '2009-04-08 19:34:01'),
(110, 107, 4, 5, 'Item 1.0.0', 'Item 1.0.0', '2009-04-08 19:35:03'),
(111, 107, 6, 7, 'Item 1.0.1', 'Item 1.0.1', '2009-04-08 19:35:21'),
(112, 109, 12, 13, 'Item 1.2.0', 'Item 1.2.0', '2009-04-08 19:36:11'),
(113, 109, 14, 19, 'Item 1.2.1', 'Item 1.2.1', '2009-04-08 19:36:29'),
(114, 109, 20, 21, 'Item 1.2.2', 'Item 1.2.2', '2009-04-08 19:36:48'),
(115, 113, 15, 16, 'Item 1.2.1.0', 'Item 1.2.1.0', '2009-04-08 19:37:10'),
(116, 113, 17, 18, 'Item 1.2.1.1', 'Item 1.2.1.2', '2009-04-08 19:37:39'),
(117, 106, 27, 28, 'Item 3.0', 'Item 3.0', '2009-04-08 19:38:35'),
(118, 106, 29, 30, 'Item 3.1', 'Item 3.1', '2009-04-08 19:38:52');

Originally Posted by arkinstall

But, in essence, what you have to do is generate a list of master items. The children of these items are in an inner-list, etc.

I'm trying to get the menu tree from this query

Code SQL:

SELECT id,ctitle, lft, rgt
FROM categories
WHERE lft
BETWEEN '1'
AND '32'
ORDER BY lft ASC 
LIMIT 0 , 30

This are my attempts (it's just a test)

Code PHP:

function display_memu($root=0) { 
   // retrieve the left and right value of the $root node 
   $result = mysql_query('SELECT lft, rgt FROM categories '. 
                          'WHERE parent_id="'.$root.'";'); 
 
 	$row = mysql_fetch_array($result,MYSQL_ASSOC); 
	// start with an empty $right stack 
   	$right = array(); 
   	$left = array(); 
	// now, retrieve all descendants of the $root node 
   	$result = mysql_query('SELECT ctitle, lft, rgt FROM categories '. 
                          'WHERE lft BETWEEN '.$row['lft'].' AND '. 
                          $row['rgt'].' ORDER BY lft ASC;'); 
 
   	// display each row 
   	while ($row = mysql_fetch_array($result)) { 
		// only check stack if there is one and get rid of root
 		if (count($right)>0){ 
           	// check if we should remove a node from the stack 
   			while ($right[count($right)-1]<$row['rgt']) { 
      			$rows= array_pop($right); 
 
				var_dump($rows);
           	} 
           	$parentRight= $right[count($right)-1];
           	$parentLeft= $left[count($left)-1];
			$change= ($row['lft'] == ($parentRight + 2))?'T':'F';
			$isChild= (($row['lft'] - $parentLeft) == 1)?'T':'F';
 			$sibling= (!($row['rgt'] - $row['lft'] - 1) && (($row['lft'] - $parentLeft) == 2)) ?'T':'F';
	 		$descendants = ($row['rgt'] - $row['lft'] -1) / 2;
	 	/*
		echo $row['ctitle']."<br>";  
		echo 'descendants '.$descendants."<br>";
		echo 'change '.$change."<br>";
		echo 'sibling '.$sibling."<br>";
		echo 'ischild '.$isChild."<br>"; 
		echo "-----------------------------<br>";  
		*/
		//echo "<pre>";
		//var_dump($right);
		// display indented node title 
       	}
		// add this node to the stack 
       	$right[] = $row['rgt'];
  		$left[] = $row['lft'];
   } 
}

I'm trying with and without this

Code PHP:

while ($right[count($right)-1]<$row['rgt']) { 
      			$rows= array_pop($right); 
 
				var_dump($rows);
           	}

May be I'm following the wrong way because
of I don't find the way

Thanks for help.

**risoknop** · Apr 8, 2009 10:33

This is a Zend view helper I have used in one of my past projects where categories where sorted by Modified Preorder Traversal algorithm:

PHP Code:


<?php

class Zend_View_Helper_Categories extends Zend_View_Helper_Abstract

{

    public function categories($categories)

    {

    $tab = '    '; // For proper XHTML indentation

        

    $right = array(); // Stack of right values

    $currentLevel = 1;

    $lastLevel = 1;

    

    echo $tab, $tab, $tab, '<ul class="categories">', "\n";

    

    foreach ($categories as $category) {        

        if (count($right) > 0) {        

            // Check if we should remove a node from the stack

        while ($right[count($right) - 1] < $category->rgt) {

            array_pop($right);

        }            

        $lastLevel = $currentLevel;

        $currentLevel = count($right);            

        //If we have moved to upper level, close opened unordered lists

        if ($currentLevel < $lastLevel) {

            for ($i = 0; $i < $lastLevel - $currentLevel; $i++) {

            echo str_repeat($tab, $lastLevel - $i + 2), '</ul></li>', "\n";

            }

        }

        // Add this node to the unordered list

        echo str_repeat($tab, $currentLevel + 3), '<li><a href="',

        $this->view->url(

            array(

            'id' => $this->view->escape($category->id),

            'slug' => $this->view->escape($category->slug)

            ),

            'category',

            true

        ),

        '">', $this->view->escape($category->title), '</a> (',

        $category->posts_count, ')';            

        if ($category->rgt - $category->lft > 1) {

            echo '<ul>', "\n";

        } else {

            echo '</li>', "\n";

        }        

        }        

        // Add this node to the stack

        $right[] = $category->rgt;        

    }    

    // Close remaining unordered lists

    for ($i = 0; $i < $currentLevel - 1; $i++) {

        echo str_repeat($tab, $currentLevel - $i + 2), '</ul></li>', "\n";

    }

    

    echo $tab, $tab, $tab, '</ul>', "\n";

    }

}

It prints a complete hierarchy in unordered nested list. You just pass object array of categories ordered by "lft" to it and voila.

Ignore the $tab variable... I just wanted to make the list nicely indented.

Maybe you can get something from it...

**whisher** · Apr 8, 2009 11:26

@risoknop
Thanks for help.
I'd like to avoid to use ZF
but I'll take a look

**whisher** · Apr 8, 2009 11:50

It doesn't work yet
but it can be at least a start point

(just a rough past and copy)

Code PHP:

function display_memu($root=0) { 
   // retrieve the left and right value of the $root node 
   $result = mysql_query('SELECT lft, rgt FROM categories '. 
                          'WHERE parent_id="'.$root.'";'); 
   if (!$result){
    	die('Invalid query: ' . mysql_error());
	}  
 	$row = mysql_fetch_array($result,MYSQL_ASSOC); 
	// start with an empty $right stack 
   	$right = array(); 
	// now, retrieve all descendants of the $root node 
   	$result = mysql_query('SELECT id,ctitle, lft, rgt FROM categories '. 
                          'WHERE lft BETWEEN '.$row['lft'].' AND '. 
                          $row['rgt'].' ORDER BY lft ASC;'); 
 	$right = array(); // Stack of right values 
    $currentLevel = 1; 
    $lastLevel = 1;
    $menu= '<ul class="categories-tree">'."\n";
   	// display each row 
   	while ($row = mysql_fetch_array($result)) { 
		// only check stack if there is one 
 		if (count($right)>0){ 
           	// check if we should remove a node from the stack 
   			while ($right[count($right)-1]<$row['rgt']) { 
      			array_pop($right); 
           	} 
           	$lastLevel = $currentLevel; 
        	$currentLevel = count($right); 
           	if ($currentLevel < $lastLevel) { 
            	for ($i = 0; $i < $lastLevel - $currentLevel; $i++) { 
            		$menu .='</ul></li>'."\n"; 
            	} 
        	}
           	$menu .='<li><a href="'.$row['id'].'">'.$row['ctitle'].'</a><li>';  
           	if ($row['rgt'] - $row['lft'] > 1) { 
            	$menu .='<ul>'."\n"; 
        	} else { 
            	$menu .='</li>'."\n"; 
        	}     
 		} 
		$right[] = $row['rgt']; 
   } 
   // Close remaining unordered lists 
   for ($i = 0; $i < $currentLevel - 1; $i++) { 
   		$menu .= '</ul></li>'."\n"; 
   }
   return $menu;
}

**risoknop** · Apr 8, 2009 12:33

Originally Posted by whisher

@risoknop
Thanks for help.
I'd like to avoid to use ZF
but I'll take a look

It doesn't require Zend. Just copy the method from the class and modify it for your script. Try this:

PHP Code:


function your_function($categories)

{

    $tab = '    '; // For proper XHTML indentation 

         

    $right = array(); // Stack of right values 

    $currentLevel = 1; 

    $lastLevel = 1; 

     

    echo $tab, $tab, $tab, '<ul class="categories">', "\n"; 

     

    foreach ($categories as $category) {         

        if (count($right) > 0) {         

            // Check if we should remove a node from the stack 

        while ($right[count($right) - 1] < $category->rgt) { 

            array_pop($right); 

        }             

        $lastLevel = $currentLevel; 

        $currentLevel = count($right);             

        //If we have moved to upper level, close opened unordered lists 

        if ($currentLevel < $lastLevel) { 

            for ($i = 0; $i < $lastLevel - $currentLevel; $i++) { 

            echo str_repeat($tab, $lastLevel - $i + 2), '</ul></li>', "\n"; 

            } 

        } 

        // Add this node to the unordered list 

        echo str_repeat($tab, $currentLevel + 3), '<li>', $category->title;        

        if ($category->rgt - $category->lft > 1) { 

            echo '<ul>', "\n"; 

        } else { 

            echo '</li>', "\n"; 

        }         

        }         

        // Add this node to the stack 

        $right[] = $category->rgt;         

    }     

    // Close remaining unordered lists 

    for ($i = 0; $i < $currentLevel - 1; $i++) { 

        echo str_repeat($tab, $currentLevel - $i + 2), '</ul></li>', "\n"; 

    } 

     

    echo $tab, $tab, $tab, '</ul>', "\n"; 

}

Just remember that $categories must be an object array, so fetch the categories into an object... not array. Categories must be ordered by "lft":

Code:

SELECT * FROM categories ORDER BY lft

**risoknop** · Apr 8, 2009 12:40

Sorry, try it now.

**whisher** · Apr 8, 2009 13:24

Code PHP:

public function fetchMenu($root=0) { 
		if($root==0){
			$root= $this->getRootId();
			if($root===false){
				return null;	
			}
		}
		$categories= $this->getDescendantsRows($root);
		if (count($categories)===0) {
      		return null;
    	}
     $tab = '    '; // For proper XHTML indentation 
 
    $right = array(); // Stack of right values 
    $currentLevel = 1; 
    $lastLevel = 1; 
 
    echo $tab, $tab, $tab, '<ul class="categories">', "\n"; 
 
    foreach ($categories as $category) {          
        if (count($right) > 0) {          
            // Check if we should remove a node from the stack 
        while ($right[count($right) - 1] < $category->rgt) { 
            array_pop($right); 
        }              
        $lastLevel = $currentLevel; 
        $currentLevel = count($right);              
        //If we have moved to upper level, close opened unordered lists 
        if ($currentLevel < $lastLevel) { 
            for ($i = 0; $i < $lastLevel - $currentLevel; $i++) { 
            echo str_repeat($tab, $lastLevel - $i + 2), '</ul></li>', "\n"; 
            } 
        } 
        // Add this node to the unordered list 
        echo str_repeat($tab, $currentLevel + 3), '<li>', $category->ctitle;         
        if ($category->rgt - $category->lft > 1) { 
            echo '<ul>', "\n"; 
        } else { 
            echo '</li>', "\n"; 
        }          
        }          
        // Add this node to the stack 
        $right[] = $category->rgt;          
    }      
    // Close remaining unordered lists 
    for ($i = 0; $i < $currentLevel - 1; $i++) { 
        echo str_repeat($tab, $currentLevel - $i + 2), '</ul></li>', "\n"; 
    } 
 
    echo $tab, $tab, $tab, '</ul>', "\n";
}

It fits very well to my class

Loads of thanks ^_^

**risoknop** · Apr 8, 2009 13:48

You are welcome

User Tag List

Thread: Hierarchical Data in a menu tree.

Thread Tools

Display

Hierarchical Data in a menu tree.

Bookmarks

Bookmarks

Posting Permissions