Display Render Nested Set MPPT As HTML List
Contents
Abstract
Modified Preorder Tree Traversal or MPTT is a system of Hierarchical data storage covered quite extensively at http://www.phpro.org/tutorials/Managing-Hierarchical-Data-with-PHP-and-MySQL.html.
This tutorial looks at MPTT nested sets and builds upon the previous tutorial by providing a method to display the nested data using an HTML unordered list.
Database
In this tutorial, PostGreSQL will be used, however, any relational database can be used in the same manner.
Create a database called animals, and in the database, create a single table called categories using the following SQL.
CREATE TABLE categories( id SERIAL PRIMARY KEY NOT NULL, name VARCHAR(45) NOT NULL, left_node INTEGER NOT NULL, right_node INTEGER NOT NULL );
Because you have read the previous tutorial, the concept of left and right nodes is quite clear. So, lets populate the categories table.
INSERT INTO categories (name, left_node, right_node) VALUES ('Ozzies',1 , 16);
INSERT INTO categories (name, left_node, right_node) VALUES ('Snakes', 2, 3);
INSERT INTO categories (name, left_node, right_node) VALUES ('Marsupials', 4, 7);
INSERT INTO categories (name, left_node, right_node) VALUES ('Monotremes', 5, 6);
INSERT INTO categories (name, left_node, right_node) VALUES ('Deadly Things', 8, 13);
INSERT INTO categories (name, left_node, right_node) VALUES ('Crocodiles', 9, 12);
INSERT INTO categories (name, left_node, right_node) VALUES ('Crocodiles with friggen laser beams', 10, 11);
INSERT INTO categories (name, left_node, right_node) VALUES ('Sharks', 14, 15);
Retrieving Data
To fetch the data using PHP, a simple connection needs to established using PDO, and an SQL query formulated to preorder the tree.
The following query needs to return the nested set ordered, starting with the root node and continuing to the end in preorder. The query will also add the depth of each node as an integer, indicating how many levels the node is below the root Ozzies node.
The query to preorder will look like this.
SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM categories AS node
CROSS JOIN categories AS parent
WHERE node.left_node BETWEEN parent.left_node AND parent.right_node
GROUP BY node.name, node.left_node
ORDER BY node.left_node;
In PHP code, using PDO this will look like this:
<?php
// database parameters
$dsn = 'pgsql:dbname=animals;host=127.0.0.1';
$user = 'kevin';
$password = 'secret_password';
try
{
// connect to database
$dbh = new PDO($dsn, $user, $password);
// the query
$sql = "SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM categories AS node
CROSS JOIN categories AS parent
WHERE node.left_node BETWEEN parent.left_node AND parent.right_node
GROUP BY node.name, node.left_node
ORDER BY node.left_node";
// always use prepared statements
$stmt = $dbh->prepare( $sql );
// run the query
$stmt->execute();
// fetch the results
$result = $stmt->fetchAll( PDO::FETCH_ASSOC );
// show the results
print_r( $result );
}
catch (PDOException $e)
{
echo 'Error: ' . $e->getMessage();
}
?>
The above code will render an array of results from the database
Array
(
[0] => Array
(
[name] => Ozzies
[depth] => 0
)
[1] => Array
(
[name] => Snakes
[depth] => 1
)
[2] => Array
(
[name] => Marsupials
[depth] => 1
)
[3] => Array
(
[name] => Monotremes
[depth] => 2
)
[4] => Array
(
[name] => Deadly Things
[depth] => 1
)
[5] => Array
(
[name] => Crocodiles
[depth] => 2
)
[6] => Array
(
[name] => Crocodiles with friggen laser beams
[depth] => 3
)
[7] => Array
(
[name] => Sharks
[depth] => 1
)
)
Creating a list
This could easily be done with recursion, however, lets do the rendering in a single loop, and put the logic into a function, which can be reused.
<?php
// database parameters
$dsn = 'pgsql:dbname=animals;host=127.0.0.1';
$user = 'kevin';
$password = 'secret_password';
try
{
// connect to database
$dbh = new PDO($dsn, $user, $password);
// the query
$sql = "SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM categories AS node
CROSS JOIN categories AS parent
WHERE node.left_node BETWEEN parent.left_node AND parent.right_node
GROUP BY node.name, node.left_node
ORDER BY node.left_node";
// always use prepared statements
$stmt = $dbh->prepare( $sql );
// run the query
$stmt->execute();
// fetch the tree results
$tree = $stmt->fetchAll( PDO::FETCH_ASSOC );
// show the results
echo makeTree( $tree );
}
catch (PDOException $e)
{
echo 'Error: ' . $e->getMessage();
}
/**
*
* Render a nested set into a HTML list
*
* @param array $tree
* @return string the formated tree
*
*/
function makeTree( array $tree, $css_class='menu' )
{
$result = '<ul class="'.$css_class.'">';
$currDepth = 0;
$css = 'info';
foreach( $tree as $item )
{
if($item['depth'] > $currDepth)
{
$result .= '<li><ul>'; // open sub tree if level up
}
if($item['depth'] < $currDepth)
{
$result .= str_repeat("</ul></li>", $currDepth - $item['depth']); // close sub tree if level down
}
$result .= "<li>$item[name]</li>";
$currDepth = $item['depth'];
}
$result .= "</ul>";
return $result;
} // end of func
?>
The list is now in the format of a HTML unordered list, and can be used for menu's and styled with CSS to make vertical or horizontal presentation.
Credits
Thanks to Captain James Cook for discovering Australia just 60,000 years after it was already inhabited.