SitePoint Sponsor

User Tag List

Results 1 to 10 of 10

Hybrid View

  1. #1
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Hierarchical Data in a menu tree.

    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.

  2. #2
    Theoretical Physics Student bronze trophy Jake Arkinstall's Avatar
    Join Date
    May 2006
    Location
    Lancaster University, UK
    Posts
    7,062
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    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.
    Jake Arkinstall
    "Sometimes you don't need to reinvent the wheel;
    Sometimes its enough to make that wheel more rounded"-Molona

  3. #3
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by arkinstall View Post
    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');
    Quote Originally Posted by arkinstall View Post
    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.

  4. #4
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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...

  5. #5
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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;
    }

  6. #6
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @risoknop
    Thanks for help.
    I'd like to avoid to use ZF
    but I'll take a look

  7. #7
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by whisher View Post
    @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

  8. #8
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry, try it now.

  9. #9
    SitePoint Guru whisher's Avatar
    Join Date
    May 2006
    Location
    Kakiland
    Posts
    732
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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 ^_^

  10. #10
    SitePoint Guru risoknop's Avatar
    Join Date
    Feb 2008
    Location
    end($world)
    Posts
    834
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You are welcome


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •