PHP Classes

Zebra MPTT: Store a tree using the MPTT algorithm in MySQL

Recommend this page to a friend!
  Info   View files Example   View files View files (13)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog    
Ratings Unique User Downloads Download Rankings
Not enough user ratingsTotal: 127 This week: 2All time: 9,384 This week: 96Up
Version License PHP version Categories
zebra_mptt 1.0.0GNU Lesser Genera...5PHP 5, Databases, Data types
Description 

Author

This package can be used to store a tree using the MPTT algorithm in MySQL.

It can create a tree of nodes and add nodes passing a value to store in each tree node as well an object to the parent node, which can be 0 for the root node.

The package can also perform other operations with nodes like moving nodes to other positions, get the whole tree as a multi-dimensional array or get only the children of a given tree node.

Innovation Award
PHP Programming Innovation award winner
April 2019
Winner
Many applications need to manage information that is stored in tree-like hierarchical data structures.

This package can manage trees efficiently in a MySQL database using the MPTT algorithm.

Manuel Lemos
Picture of Stefan Gabos
Name: Stefan Gabos <contact>
Classes: 12 packages by
Country: Romania Romania
Age: 44
All time rank: 312 in Romania Romania
Week rank: 106 Down3 in Romania Romania Down
Innovation award
Innovation award
Nominee: 2x

Winner: 1x

Example

<!doctype html>
<html>

    <head>
        <title>Zebra_Mptt example</title>
        <meta http-equiv="content-type" content="text/html;charset=UTF-8">
    </head>

    <body>

        <h2>Zebra_Mptt, database example</h2>

        <p>For this example, you need to first import the <strong>mptt.sql</strong> file from the <strong>install</strong>
        folder and to edit the <strong>example.php file and change your database connection related settings.</strong></p>

        <?php

       
// database connection details
       
$mysql_host = '';
       
$mysql_username = '';
       
$mysql_password = '';
       
$mysql_database = '';

       
// if could not connect to database
       
($connection = @mysqli_connect($mysql_host, $mysql_username, $mysql_password)) or

           
// stop execution and display error message
           
die('Error connecting to the database!<br>Make sure you have specified correct values for host, username and password.');

       
// if database could not be selected
       
@mysqli_select_db($connection, $mysql_database) or

           
// stop execution and display error message
           
die('Error selecting database!<br>Make sure you have specified an existing and accessible database.');

       
// first, clear everything in the database
       
mysqli_query($connection, 'TRUNCATE TABLE mptt');

       
// include the Zebra_Mptt class
       
require '../Zebra_Mptt.php';

       
// instantiate the Zebra_Mptt object
       
$mptt = new Zebra_Mptt($connection);

       
// populate the table

        // add 'Food' as a topmost node
       
$food = $mptt->add(0, 'Food');

       
// 'Fruit' and 'Meat' are direct descendants of 'Food'
       
$fruit = $mptt->add($food, 'Fruit');
       
$meat = $mptt->add($food, 'Meat');

       
// 'Red' and 'Yellow' are direct descendants of 'Fruit'
       
$red = $mptt->add($fruit, 'Red');
       
$yellow = $mptt->add($fruit, 'Yellow');

       
// add a fruit of each color
       
$cherry = $mptt->add($red, 'Cherry');
       
$banana = $mptt->add($yellow, 'Banana');

       
// add a color, but in the wrong position
       
$orange = $mptt->add($banana, 'Orange');

       
// now move it to fruits, after the "red" node
       
$orange = $mptt->move($orange, $red, 'after');

       
// add two kinds of meat
       
$meat = $mptt->add($meat, 'Beef');
       
$pork = $mptt->add($meat, 'Pork');

       
// get descendants of 'Red'
       
print_r('<p>Descendants of "Red"');
       
print_r('<pre>');
       
print_r($mptt->get_descendants($red, false));
       
print_r('</pre>');

       
// get descendants of 'Meat'
       
print_r('<p>Descendants of "Meat"');
       
print_r('<pre>');
       
print_r($mptt->get_descendants($meat, false));
       
print_r('</pre>');

       
// data in the database as a multidimensional array
       
print_r('<p>The entire tree');
       
print_r('<pre>');
       
print_r($mptt->get_tree());
       
print_r('</pre>');

       
?>

    </body>
</html>


Details

<img src="https://github.com/stefangabos/zebrajs/blob/master/docs/images/logo.png" alt="zebrajs" align="right">

Zebra_Mptt

A PHP library providing an implementation of the modified preorder tree traversal algorithm

Latest Stable Version Total Downloads Monthly Downloads Daily Downloads License

What is Modified Preorder Tree Traversal

MPTT is a fast algorithm for storing hierarchical data (like categories and their subcategories) in a relational database. This is a problem that most of us have had to deal with, and for which we've used an adjacency list, where each item in the table contains a pointer to its parent and where performance will naturally degrade with each level added as more queries need to be run in order to fetch a subtree of records.

The aim of the modified preorder tree traversal algorithm is to make retrieval operations very efficient. With it you can fetch an arbitrary subtree from the database using just two queries. The first one is for fetching details for the root node of the subtree, while the second one is for fetching all the children and grandchildren of the root node.

The tradeoff for this efficiency is that updating, deleting and inserting records is more expensive, as there's some extra work required to keep the tree structure in a good state at all times. Also, the modified preorder tree traversal approach is less intuitive than the adjacency list approach because of its algorithmic flavour.

For more information about the modified preorder tree traversal method, read this excellent article called Storing Hierarchical Data in a Database.

What is Zebra_Mptt

Zebra_Mptt is a PHP library that provides an implementation of the modified preorder tree traversal algorithm making it easy to implement the MPTT algorithm in your PHP applications.

It provides methods for adding nodes anywhere in the tree, deleting nodes, moving and copying nodes around the tree and methods for retrieving various information about the nodes.

Zebra\_Mptt uses table locks making sure that database integrity is always preserved and that concurrent MySQL sessions don't compromise data integrity. Also, Zebra_Mptt uses a caching mechanism which has as result the fact that regardless of the type, or the number of retrieval operations, the database is read only once per script execution!

:books: Check out the awesome documentation!

Support the development of this library

Donate

Features

  • provides methods for adding nodes anywhere in the tree, deleting nodes, moving and copying nodes around the tree and methods for retrieving various information about the nodes
  • uses a caching mechanism which has as result the fact that regardless of the type, or the number of retrieval operations, the database is read only once per script execution
  • uses table locks making sure that database integrity is always preserved and that concurrent MySQL sessions don't compromise data integrity
  • uses mysqli extension
  • has awesome documentation
  • code is heavily commented and generates no warnings/errors/notices when PHP's error reporting level is set to E_ALL

Requirements

PHP 5.0.0+, MySQL 4.1.22+

Installation

You can install Zebra_Mptt via Composer

# get the latest stable release
composer require stefangabos/zebra_mptt

# get the latest commit
composer require stefangabos/zebra_mptt:dev-master

Or you can install it manually by downloading the latest version, unpacking it, and then including it in your project

require_once 'path/to/Zebra_Mptt.php';

Install MySQL table

Notice a directory called install containing a file named mptt.sql. This file contains the SQL code that will create the table used by the class to store its data. Import or execute the SQL code using your preferred MySQL manager (like phpMyAdmin or the fantastic Adminer) into a database of your choice.

How to use

// include the Zebra_Mptt class
require 'path/to/Zebra_Mptt.php';

// instantiate a new object
$mptt = new Zebra_Mptt();

// populate the table

// add 'Food' as a topmost node
$food = $mptt->add(0, 'Food');

// 'Fruit' and 'Meat' are direct descendants of 'Food'
$fruit = $mptt->add($food, 'Fruit');
$meat = $mptt->add($food, 'Meat');

// 'Red' and 'Yellow' are direct descendants of 'Fruit'
$red = $mptt->add($fruit, 'Red');
$yellow = $mptt->add($fruit, 'Yellow');

// add a fruit of each color
$cherry = $mptt->add($red, 'Cherry');
$banana = $mptt->add($yellow, 'Banana');

// add two kinds of meat
$mptt->add($meat, 'Beef');
$mptt->add($meat, 'Pork');

// move 'Banana' to 'Meat'
$mptt->move($banana, $meat);

// get a flat array of descendants of 'Meat'
$mptt->get_children($meat);

// get a multidimensional array (a tree) of all the data in the database
$mptt->get_tree();

:books: Check out the awesome documentation!


  Files folder image Files  
File Role Description
Files folder imagedocs (3 files, 2 directories)
Files folder imageexamples (1 file)
Files folder imageinstall (1 file)
Accessible without login Plain text file CHANGELOG.md Data Auxiliary data
Accessible without login Plain text file composer.json Data Auxiliary data
Accessible without login Plain text file license.txt Doc. Documentation
Accessible without login Plain text file README.md Doc. Documentation
Plain text file Zebra_Mptt.php Class Class source

  Files folder image Files  /  docs  
File Role Description
Files folder imagemedia (2 files)
Files folder imageZebra_Mptt (1 file)
  Accessible without login HTML file elementindex_Zebra_Mptt.html Doc. Documentation
  Accessible without login HTML file index.html Doc. Documentation
  Accessible without login Plain text file README.md Doc. Documentation

  Files folder image Files  /  docs  /  media  
File Role Description
  Accessible without login Plain text file reset.css Data Auxiliary data
  Accessible without login Plain text file style.css Data Auxiliary data

  Files folder image Files  /  docs  /  Zebra_Mptt  
File Role Description
  Accessible without login HTML file Zebra_Mptt.html Doc. Documentation

  Files folder image Files  /  examples  
File Role Description
  Accessible without login Plain text file index.php Example Example script

  Files folder image Files  /  install  
File Role Description
  Accessible without login Plain text file mptt.sql Data Auxiliary data

 Version Control Unique User Downloads Download Rankings  
 100%
Total:127
This week:2
All time:9,384
This week:96Up