Introduction

Introduction – Introduction to DB_NestedSet

Overview

With this package, one can easily create trees with infinite depth inside a relational database. The package provides a way to

  • create/update/delete nodes
  • query nodes, trees and subtrees
  • copy (clone) nodes, trees and subtrees
  • move nodes, trees and subtrees
  • etc.

An example

Creates some root and subnodes

In this example, one rootnode and two subnodes are created, saved to the database and displayed.

<?php
require_once 'DB/NestedSet.php';
require_once 
'DB/NestedSet/Output.php';
require_once 
'HTML/Menu.php';
$DatabasePointer mysql_connect("localhost""user""pwd");
mysql_select_db("database"$DatabasePointer);
$dsn 'mysql://user:pwd@localhost/database';
// needed colums in table:
$params = array(
    
'id'        => 'id',
    
'parent_id' => 'rootid',
    
'left_id'   => 'l',
    
'right_id'  => 'r',
    
'order_num' => 'norder',
    
'level'     => 'level',
    
'name'      => 'name',
);
$nestedSet =& DB_NestedSet::factory('DB'$dsn$params);
$nestedSet->setAttr(array(
        
'node_table' => 'nested_set',
        
'lock_table' => 'nested_set_locks',
        
'secondarySort' => 'name',
    )
);
$parent $nestedSet->createRootNode(array('name' =>'root 1'), falsetrue);
$nestedSet->createSubNode($parent, array('name' => 'node 1.1'));
$nestedSet->createSubNode($parent, array('name' =>'node 1.2'));
$data $nestedSet->getAllNodes(true);

foreach (
$data as $id => $node) {
     
$data[$id]['url'] = 'index.php?nodeID=' $node['id'];
}

$params = array(
    
'structure' => $data,
    
'titleField' => 'name',
    
'urlField' => 'url');
$output =& DB_NestedSet_Output::factory($params'Menu');
$structure $output->returnStructure();
$menu = & new HTML_Menu($structure'sitemap');
$menu->forceCurrentUrl($currentUrl);
$menu->show();
?>
DB_NestedSet (Previous) Add an event listener (Next)
Last updated: Thu, 31 Jul 2014 — Download Documentation
Do you think that something on this page is wrong? Please file a bug report or add a note.
View this page in:

User Notes:

Note by: ben@level8ds.com
Here is SQL code to create the required tables. A previous note on this page states that the `lockStamp` column should be of type TIMESTAMP. However, the source code (NestedSet.php, line 2316) seems to indicate that `lockStamp` should be an integer type.

CREATE TABLE IF NOT EXISTS `nested_set` (
`id` int(9) unsigned NOT NULL AUTO_INCREMENT,
`lft` int(9) unsigned DEFAULT NULL,
`rgt` int(9) unsigned DEFAULT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`parent` int(9) unsigned DEFAULT NULL,
`norder` mediumint(4) unsigned DEFAULT NULL,
`level` mediumint(4) unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE IF NOT EXISTS `nested_set_locks` (
`lockId` varchar(32) COLLATE utf8_unicode_ci NOT NULL,
`lockTable` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`lockStamp` bigint(11) DEFAULT NULL,
PRIMARY KEY (`lockId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Note by: ad@addacumen.com
Could someone advise me? If several tables in the database contain nested sets it would appear that one lock table will be sufficient to cover all their needs.

Is that correct?
Note by: wabnitz@platis.de
You need to create two tables. The names of the tables are passed in through the $nestedSet->setAttr() method.

One table for the nodes has columns as described in the params array.

The second table for locking needs following columns:
lockId - VARCHAR(32)
lockTable - VARCHAR(32)
lockStamp - TIMESTAMP