MPTT Modified Preorder Tree Traversal PHP Tree Menu Script

Forums » Scripts » MPTT Modified Preorder Tree Traversal PHP Tree Menu Script
this tutorial guide shows you how you can make mptt work so you can understand

hi im looking for a MPTT (modified pre-order tree trasversal) script or a function class in php to help me retrieve a tree menu from mysql database. i was wondering if you can help me with the

for example:
wallpapers
  -animals
    -cats
     -lions
  -birds

screensavers
  -fish
    -sharks
  -water
    -beaches
    -ocean  
categories
  -subcategory
    -subsubcategory    
thanks
php
this is a good article about MPTT: http://www.sitepoint.com/hierarchical-data-database-2/
italian
you can go to

Joe Celko Nested Sets nested, nested sets
www.phpclasses.org/browse/package/1374.html



seo
this is really hard to comprehend
phpgandi87
this so hard to understand????
wallpaperama
hi, my screen name is hostman. im with www.webune.com support.

to help our customer who have dedicated server we are creating this tutorial on how to create this type of algorithym.

Modified Preorder Tree Transversal Totorial

ok this is what i did

STEP 1: CREATE MYSQL DATABASE DUMP:

CREAT A DATABASE CALLED mptt

--
-- Table structure for table `mptt`
--

CREATE TABLE `mptt` (
`id` int(11) NOT NULL auto_increment,
`title` varchar(50) NOT NULL default '',
`left` int(11) NOT NULL default '0',
`right` int(11) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

--
-- Dumping data for table `mptt`
--

INSERT INTO `mptt` VALUES (1, 'food', 1, 18);
INSERT INTO `mptt` VALUES (2, 'fruit', 2, 11);
INSERT INTO `mptt` VALUES (3, 'meat', 12, 17);
INSERT INTO `mptt` VALUES (4, 'red', 3, 6);
INSERT INTO `mptt` VALUES (5, 'yellow', 7, 10);
INSERT INTO `mptt` VALUES (6, 'beef', 13, 14);
INSERT INTO `mptt` VALUES (7, 'pork', 15, 16);
INSERT INTO `mptt` VALUES (8, 'cherry', 4, 5);
INSERT INTO `mptt` VALUES (9, 'banana', 8, 9);

wallpaperama

DISPLAYING

STEP 2: create php file called mptt.php with the following code:

mptt.php
<?php
	$db = mysql_connect("HOSTNAME", "USERNAME", "PASSWORD");
	mysql_select_db("DATABASE_NAME",$db);

	$sql = "SELECT title FROM mptt WHERE `left` BETWEEN 2 AND 11";
  	$result = mysql_query($sql ,$db);

  	if ($myrow = mysql_fetch_array($result)) {
  		do {
  			echo $myrow['title'].'<BR>';
  		} while ($myrow = mysql_fetch_array($result));
  	}else{
  		echo '<strong>ERROR: </strong>'.mysql_error();
  	}
  	
?>

NOTE:CHANGE THE FOLLOWING TO YOUR DB CONNECTION SETTINGS:

HOSTNAME (usually localhost)
USERNAME (your database username)
PASSWORD (username password)
DATABASE_NAME (the database where you dump mysql above)


this file will display all file in the FRUIT category

OUTPUT:
fruit
red
yellow
cherry
banana
wallpaperama

INDENTED ORDER DISPLAYING

ok, if you want indent the results so it will be a tree level, you can use this function:

STEP 3: Save this file at mptt-tree.php

mptt-tree.php
PHP / MYSQL Web Hosting at:<br>
<a href="http://www.webune.com"><img src="http://www.webune.com/images/headers/default_logo.jpg" border="0"></a><hr>
<?php
	$db = mysql_connect("HOSTNAME", "USERNAME", "PASSWORD");
	mysql_select_db("DATABASE_NAME",$db);

	echo '<hr>'; 
	function display_mptt($root) {
	global $db;
	// retrieve the left and right value of the $root node
	$sql2 = "SELECT * from mptt where id=$root";

	$result2 = mysql_query($sql2 ,$db);
	if(!$row2 = mysql_fetch_array($result2)) echo mysql_error();
	echo '<h1>Indentation</h1>';

	// start with an empty $right stack
	$right = array();

	// now, retrieve all descendants of the $root node
  	$sql = "SELECT * from mptt WHERE `left` BETWEEN ".$row2['left']." AND ".$row2['right']." ORDER BY 'left' ASC";
 	$result = mysql_query($sql ,$db);

 	// 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['right']) {
  				array_pop($right);
  			}
  		}
 	// display indented node title
  	echo str_repeat('&nbsp;',count($right)).$row['title']."<br>";
 	// add this node to the stack
  	$right[] = $row['right'];
  }
 } 
  
  display_mptt(1);
  ?>


after you save this file and open it with your browser you will see this output:

Indentation

food fruit red cherry yellow banana meat beef pork

as you can see, it matches our table at the begining of this tutorial.
wallpaperama

ADDING

now for the best part, we need to add, so we need to make a webform.

now save this file as mptt-add.php and run in your php website, click on the add link to add to each category. i followed the steps by Gijs Van Tulder

mptt-add.php
PHP / MYSQL Web Hosting at:<br>
<a href="http://www.webune.com"><img src="http://www.webune.com/images/headers/default_logo.jpg" border="0"></a><hr>
<?php
	$db = mysql_connect("HOSTNAME", "USERNAME", "PASSWORD");
	mysql_select_db("DATABASE_NAME",$db);
if ($_GET['id']) {
  if(isset($_REQUEST['Submit'])) {
  	$sql = "SELECT * FROM mptt where id=".$_GET['id'];
  	$result = mysql_query($sql ,$db);
  	$row = mysql_fetch_array($result);
  	$right = $row['right'];
  	// UPDATE RIGHT VALUES
  	$sql = "UPDATE mptt SET `right`=`right`+2 WHERE `right` > ". ($right - 1);
  	if(!$result = mysql_query($sql ,$db)) echo "$sql <br>".mysql_error();
  	// UPDATE LEFT VALUES
  	$sql = "UPDATE mptt SET `left`=`left`+2 WHERE `left` > ". ($right - 1);
  	if(!$result = mysql_query($sql ,$db)) echo mysql_error();
  	// INSERT NEW CATEGORY
  	$sql = "INSERT INTO mptt (`left`,`right`,`title`) values ('".$right."', '".($right +1)."', '".$_REQUEST['title']."')";
  	if(!$result = mysql_query($sql ,$db)) echo mysql_error();
  } else {
  	?>
	<form name="form1" method="post" action="<?php echo $_SERVER['REQUEST_URI']; ?>">
  	Title: <input type="text" name="title">
	<input type="submit" name="Submit" value="Submit">
	</form>
	<?php
  }
  } 
function display_mptt($root) {
  global $db;
  echo '<table border="0" width="200">';
  // retrieve the left and right value of the $root node
  $sql2 = "SELECT * from mptt where id=$root";
  //$sql = "SELECT left,right FROM mptt WHERE `id`=1";
  $result2 = mysql_query($sql2 ,$db);
  if(!$row2 = mysql_fetch_array($result2)) echo mysql_error();
  echo '<h1>Modified Preorder Tree Traversal</h1>';
  // start with an empty $right stack
  $right = array();
 // now, retrieve all descendants of the $root node
  $sql = "SELECT * from mptt WHERE `left` BETWEEN ".$row2['left']." AND ".$row2['right']." ORDER BY 'left' ASC";
  $result = mysql_query($sql ,$db);
 // 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['right']) {
  			array_pop($right);
  		}
  	}
 // display indented node title
  echo '<tr><td>'.str_repeat('&nbsp;&nbsp;',count($right)).$row['title'].'</td><td><a href="'.$_SERVER['PHP_SELF'].'?id='.$row['id'].'">[ add ]</a></td></tr>';
 // add this node to the stack
  $right[] = $row['right'];
  }
  echo '</table>';
} 
  
  display_mptt(1);
  echo "<hr>";
?>
 
wallpaperama

EDIT



the next step would be to edit and then to be able to delete..

wallpaperama
this is mysql dump:

-- phpMyAdmin SQL Dump
-- version 2.6.0-pl3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 29, 2008 at 11:31 AM
-- Server version: 4.1.7
-- PHP Version: 5.0.3RC2-dev
--
-- Database: `test`
--

-- --------------------------------------------------------

--
-- Table structure for table `category`
--

CREATE TABLE `category` (
`category_id` int(11) NOT NULL auto_increment,
`category_name` varchar(100) NOT NULL default '',
`category_title` varchar(50) NOT NULL default '',
`category_url` varchar(100) NOT NULL default '',
`category_description` varchar(200) NOT NULL default '',
`category_keywords` varchar(200) NOT NULL default '',
`category_left` int(11) default NULL,
`category_right` int(11) default NULL,
`page_show_header` int(11) NOT NULL default '1',
`page_show_main_menu` int(11) NOT NULL default '1',
`page_show_left` int(11) NOT NULL default '1',
`page_show_footer` int(11) NOT NULL default '1',
PRIMARY KEY (`category_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `category`
--

INSERT INTO `category` VALUES (1, 'Root Category', 'Absolute Root category', 'root', '', '', 1, 20, 1, 1, 1, 1);
INSERT INTO `category` VALUES (2, 'grandfather', 'grandfather', 'grandfather', 'grandfather', 'grandfather', 2, 13, 1, 1, 1, 1);
INSERT INTO `category` VALUES (3, 'grandmother', 'grandmother', 'grandmother', 'grandmother', 'grandmother', 14, 19, 1, 1, 1, 1);
INSERT INTO `category` VALUES (4, 'father', 'father', 'father', 'father', 'father', 3, 12, 1, 1, 1, 1);
INSERT INTO `category` VALUES (5, 'son', 'son', 'son', 'son', 'son', 4, 5, 1, 1, 1, 1);
INSERT INTO `category` VALUES (6, 'mother', 'mother', 'mother', 'mother', 'mother', 15, 18, 1, 1, 1, 1);
INSERT INTO `category` VALUES (7, 'daughter', 'daughter', 'daughter', 'daughter', 'daughter', 16, 17, 1, 1, 1, 1);
INSERT INTO `category` VALUES (8, 'son2', 'son2', 'son2', 'son2', 'son2', 6, 7, 1, 1, 1, 1);
INSERT INTO `category` VALUES (9, 'son3', 'son3', 'son3', 'son3', 'son3', 8, 11, 1, 1, 1, 1);
INSERT INTO `category` VALUES (10, 'son3 -', 'son3 -', 'son3 -', 'son3 -', 'son3 -', 9, 10, 1, 1, 1, 1);
wallpaperama
this is my function:
function mpttt_admin($root) {
global $db,$db_prefix;

// retrieve the category_left and right value of the $root node
$sql2 = &quot;SELECT * from &quot;.$db_prefix.&quot;category where category_id=$root&quot;;
$result2 = mysql_query($sql2 ,$db);
if(!$row2 = mysql_fetch_array($result2)) echo mysql_error();
// start with an empty $right stack
$category_right = array();
echo '&lt;table border=&quot;0&quot; cellpadding=&quot;1&quot; cellspacing=&quot;1&quot; bgcolor=&quot;#EEEEEE&quot; width=&quot;300&quot;&gt;';
// now, retrieve all descendants of the $root node
$sql = &quot;SELECT * from &quot;.$db_prefix.&quot;category WHERE `category_left` BETWEEN &quot;.$row2['category_left'].&quot; AND &quot;.$row2['category_right'].&quot; ORDER BY 'category_left' ASC&quot;;
$result = mysql_query($sql ,$db);

// display each row
while ($row = mysql_fetch_array($result)) {
// only check stack if there is one
if (count($category_right)&gt;0) {
// check if we should remove a node from the stack
while ($category_right[count($category_right)-1]&lt;$row['category_right']) {
array_pop($category_right);
}
}
// display indented node title
echo '&lt;tr bgcolor=&quot;white&quot;&gt;&lt;td&gt;'.str_repeat('&amp;nbsp; ',count($category_right)).$row['category_name'].'&lt;/a&gt;&lt;/td&gt;&lt;td&gt;&lt;a href=&quot;'.$_SERVER['PHP_SELF'].'?category_right='.$row['category_right'].'&amp;action=add&quot;&gt;[ add ]&lt;/a&gt;&lt;/td&gt;&lt;td&gt;&lt;a href=&quot;'.$_SERVER['PHP_SELF'].'?category_id='.$row['category_id'].'&amp;action=edit&quot;&gt;[ edit ]&lt;/a&gt;&lt;/td&gt;&lt;/tr&gt;';
//echo str_repeat('&amp;nbsp; ',count($category_right)).' &lt;a href=&quot;'.pages_cat_url($row['category_url'], $site_url).'&quot; class=&quot;category_link&quot;&gt;'.$row['category_name'].'&lt;/a&gt; &lt;a href=&quot;'.$_SERVER['PHP_SELF'].'?category_right='.$row['category_right'].'&amp;action=add&quot;&gt;[ add ]&lt;/a&gt; &lt;a href=&quot;'.$_SERVER['PHP_SELF'].'?category_id='.$row['category_id'].'&amp;action=edit&quot;&gt;[ edit ] &lt;br&gt;';
// add this node to the stack
$category_right[] = $row['category_right'];
}
echo '&lt;/table&gt;';
}
wallpaperama
i have added a breacrum query so you can display its parents. just go to this post:

breadcrum-modified-pre-order-tree-transversal-mptt-sql-query

by the way, im still working on the editing and deleting part. its more complicated than i thought
Tim
good tutorial, doesn't work properly but it gives me the idea of what needs to be done and i can go from here, for example the mptt-add doesn't add correctly and it does not display correctly. mine displays as:

food [ add ]
fruit [ add ]
meat [ add ]
red [ add ]
yellow [ add ]
beef [ add ]
pork [ add ]
cherry [ add ]
banana [ add ]
Tim
alright, got it fixed. seems to be an error with mysql when you're using the columns named as 'left' or 'right' so i just changed mine to 'lft' and 'rgt'. if anyone seems to have any issues, contact me and i will help you out. good tutorial hostman! now i will go work on editing and deleting since i pretty much have the idea of it now.
mark
at phpbeginner

you claim not to know where you found this article but then hotlink images from it?

sitepoint /article/hierarchical-data-database
Greg
at tim ... left and right are reserved keywords in mysql ... if you wish to use them as field names, you have to enclose the field name in backticks (`)
EZref
MPPT in pieces here ez Preorder Traversal Tree %28MPTT%29 - Introduction.ht
Title: MPTT Modified Preorder Tree Traversal PHP Tree Menu Script [1326]
Tags: mptt modified preorder tree traversal php tree menu script
By , 18 Comments