QRdvark.com

Display Render Nested Set MPPT As HTML List

Display Render Nested Set MPPT As HTML List

Contents

  1. Abstract
  2. Database
  3. Retrieving Data
  4. Creating A List
  5. Credits

Abstract

Modified Preorder Tree Traversal or MPTT is a system of Hierarchical data storage covered quite extensively at https://QRdvark.com/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->fetchAllPDO::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->fetchAllPDO::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.