Location: PHPKode > scripts > MPTTA for PHP and MySQL > mptta-for-php-and-mysql/PHP4_Mysql3MPTTA.class.php
<?php //4
/**
 * This class is designed to add, move or delete branches inside
 * a database tree created with the 
 * Modified Preorder Tree Traversal Algorithm
 * [ Joe Celko - http://www.dbmsmag.com/9603d06.html ]
 * using MySQL 3.X database engine ( with mysql extension ).
 * The database should be as you want but requires at least 2
 * tables to use correctly this class:
 * 	1 - a table with 3 fields, called for example 'tree'
 *      	CREATE TABLE tree (
 *			id INT(10) NOT NULL AUTO_INCREMENT,
 *			sx INT(10) UNSIGNED NOT NULL,
 *			dx INT(10) UNSIGNED NOT NULL,
 *                      PRIMARY KEY (id)
 *		);
 * 	2 - another table with at least 2 fields, called for example 'branch'
 *      	CREATE TABLE branch (
 *			id INT(10) NOT NULL AUTO_INCREMENT,
 *			tree_id INT(10) UNSIGNED NOT NULL
 *                      [, name VARCHAR(255) NOT NULL, description TEXT, ....] 
 *		);
 * NOTE: the name of theese fields:
 * 		id, sx, dx, tree_id
 *       must be exactly these, other 'branch' fields should be
 *       called as you want.
 * ______________________________________________________________
 * EXAMPLE:
 * 	// database resource
 *      $db = mysql_connect( 'localhost', 'root', 'password' );
 *      // chose a database
 *	mysql_select_db( 'tree' );
 * 	// class reference
 * 	$MPTTA = &new PHP4_Mysql3MPTTA( $db );
 *      // root node
 *      $MPTTA->addRoot( Array( 'name', 'surname', 'age', ... ) );
 *      // branch node
 *      $MPTTA->addNode( 1, Array( 'sub_name1', 'sub_surname1', 'sub_age1', ... ) );
 *      $MPTTA->addNode( 1, Array( 'sub_name2', 'sub_surname2', 'sub_age2', ... ) );
 *      // move node
 *      $MPTTA->moveNode( 2, 3 ); // move sub_name1 under sub_name2
 *      // remove node
 *      $MPTTA->delNode( 2 ); // remove both sub_name1 and sub_name2
 * --------------------------------------------------------------
 * @Compatibility	>= PHP 4.3.X / MySQL 3.X
 * @Author		Andrea Giammarchi
 * @Site		http://www.devpro.it/
 * @Mail		andreaAT3siteDOTit
 * @Date		03/02/2005
 * @LastModified	19/05/2005 12:30
 * @Version		0.1b [ tested, seems efficient ]
 * @Limits		there are not transations
 */
class PHP4_Mysql3MPTTA {
	
	/**
	 * private variables
         * 	Resource		SQLITE database resource
         *      String			tree table name
         *      String			branch table name
         *      Boolean			used in internal operations
	 */
	var $__db;
	var $__tree, $__branch;
	var $__skeep = false;
	var $__qs = '__';
	
	/**
	 * constructor
         * 	Creates a new PHP4_Mysql3MPTTA obect.
         *      Assigns internal private variables
         *      	new PHP4_Mysql3MPTTA( Resource $db [, String $tree_name[, String $branch_name]] )
         *              
         * @param	Resource	mysql 3.x dtabase resource
         * @param	String		tree table name
         * @param	String		branch table name 
	 */
	function PHP4_Mysql3MPTTA( &$db, $tree_name = 'tree', $branch_name = 'branch' ) {
		$this->__db = &$db;
		$this->__tree = &$tree_name;
		$this->__branch = &$branch_name;
		$this->__qs = $this->__qs.md5(microtime()).$this->__qs;
	}
	
	/**
	 * public method
         * 	Checks if id exists ( branch id ) then returns all nested informations
         *      for that id.
         *      	Mixed self->getNode( Integer $id )
         * @param	Integer		branch node to show
         * @return	Mixed		query result / false if node doesn't exist
	 */
	function getNode( $branch_id ) {
		$branch_id = (int)$branch_id;
		return $this->__getNode( $branch_id );
	}
	
	/**
	 * public method
         * 	Creates a new root branch node
         *      	Void self->addRoot( Array $what )
         * @param	Array		all informations about this node
         * 				[ all fields on 'branch' table but not id and tree_id,
         *                                these are inserted automatically ]
	 */
	function addRoot( $new_branch ) {
		$this->__addNode( $target_branch_id = 0, $new_branch );
	}
	
	/**
	 * public method
         * 	Add a new branch ( or leaf ) under a node id, if this exists, or
         *      creates a new root branch node ( for example with an id = 0 )
         *      	Void self->addNode( Integer $where_id, Array $what )
         * @param	Integer		branch node to show
         * @param	Array		all informations about this node
         * 				[ all fields on 'branch' table but not id and tree_id,
         *                                these are inserted automatically ]
	 */
	function addNode( $target_branch_id, $new_branch ) {
		$target_branch_id = (int)$target_branch_id;
		if( $target_branch_id > 0 ) {
			$this->__addNode( $target_branch_id, $new_branch );
		}
	}
	
	/**
	 * public method
         * 	Removes a root node by id ( root branch id ) and all its sub-nodes
         *      	Void self->delNode( Integer $root_id )
         * @param	Integer		root branch node id to remove ( all subnodes are removed too )
	 */
	function delRoot( $root_id ) {
		$root_id = (int)$root_id;
		$this->__delNode( $root_id );
	}
	
	/**
	 * public method
         * 	Removes a node by id ( branch id ) and all its sub-nodes
         *      	Void self->delNode( Integer $where_id )
         * @param	Integer		branch node id to remove ( all subnodes are removed too )
	 */
	function delNode( $branch_id ) {
		$branch_id = (int)$branch_id;
		if( $branch_id > 1 ) {
			$this->__delNode( $branch_id );
		}
	}
	
	/**
	 * public method
         * 	Moves a node by id to another node by destination id
         *      nodes id are branches id, not tree.id.
         *      	Void self->moveNode( Integer $id_from, Integer $id_to )
         * @param	Integer		branch node id to move
         * @param	Integer		destination branch node id where to add moved branch
	 */
	function moveNode( $moved_branch_id, $target_branch_id ) {
		$moved_branch_id = (int)$moved_branch_id;
		$target_branch_id = (int)$target_branch_id;
		if( $moved_branch_id !== $target_branch_id ) {
			$this->__moveNode( $moved_branch_id, $target_branch_id );
		}
	}
	
	/**
	 * All final private methods
         * 	Sorry, no time to comment each private method ...
	 */
	function __getNode( &$branch_id ) {
		$q1 = &$this->__firstQuery( $branch_id );
		if( mysql_num_rows( $q1 ) === 1 ) {
			while( $r =  mysql_fetch_row( $q1 ) ) {
				$q2 = mysql_query (
					"SELECT
						{$this->__branch}.*
					FROM
						{$this->__branch}, {$this->__tree}
					WHERE
						{$this->__branch}.tree_id = {$this->__tree}.id
					AND
						{$this->__tree}.sx <= {$r[0]}
					AND
						{$this->__tree}.dx >= {$r[1]}",
					$this->__db
				);
			}
		}
		else {
			$q2 = false;
		}
		return $q2;
	}
	
	function __addNode( &$target_branch_id, &$new_branch ) {
		$q1 = &$this->__firstQuery( $target_branch_id );
		if( mysql_num_rows( $q1 ) === 1 ) {
			while( $r =  mysql_fetch_row( $q1 ) ) {
				$sx = $r[0];
			}
			$dx = $sx + 1;
			$q2 = "UPDATE {$this->__tree} SET sx = ( sx + 2 ) WHERE sx >= {$sx}{$this->__qs}
			UPDATE {$this->__tree} SET dx = ( dx + 2 ) WHERE dx >= {$sx}{$this->__qs}
			INSERT INTO {$this->__tree} VALUES ( NULL, {$sx}, {$dx} ){$this->__qs}
			";
			$this->__queryRollBackable( $q2 );
			$maxid = mysql_insert_id();
			if( $this->__skeep === false ) {
				$q2 = "INSERT INTO {$this->__branch} VALUES ( NULL, {$maxid}, <branch_values> ){$this->__qs}
				";
				if( !is_Array( $new_branch ) ) {
					$new_branch = Array( $new_branch );
				}
				$q3 = "";
				for( $a = 0, $b = count( $new_branch ); $a < $b; $a++ ) {
					$q3 .= '"'.mysql_real_escape_string( $new_branch[$a] ).'", ';
				}
				$this->__queryReplaced( $q2, '<branch_values>', $q3, 2 );
			}
			else {
				$q2 = "UPDATE {$this->__branch} SET tree_id = {$maxid} WHERE id = {$new_branch}{$this->__qs}
				";
				$this->__queryRollBackable( $q2 );
				$this->__skeep = false;
			}
		}
		else {
			if( !is_Array( $new_branch ) ) {
				$new_branch = Array( $new_branch );
			}
			$q3 = "";
			for( $a = 0, $b = count( $new_branch ); $a < $b; $a++ ) {
				$q3 .= '"'.mysql_real_escape_string( $new_branch[$a] ).'", ';
			}
			$maxid = 1;
			$increment = 0;
			$q2 = mysql_unbuffered_query( "SELECT MAX(id), MAX(dx) FROM {$this->__tree}", $this->__db );
			while( $r = mysql_fetch_row( $q2 ) ) {
				$maxid = $r[0] + 1;
				$increment = $r[1];
			}
			$q2 = "INSERT INTO
				{$this->__tree}
			VALUES (
				NULL,
				( {$increment} + 1 ), 
				( {$increment} + 2 )
			){$this->__qs}
			INSERT INTO {$this->__branch} VALUES ( NULL, {$maxid}, <branch_values> ){$this->__qs}
			";
			$this->__queryReplaced( $q2, '<branch_values>', $q3, 2 );
		}
	}
	
	function __delNode( &$branch_id ) {
		$q1 = &$this->__firstQuery( $branch_id );
		if( mysql_num_rows( $q1 ) === 1 ) {
			while( $r = mysql_fetch_row( $q1 ) ) {
				$dx = &$r[0];
				$sx = &$r[1];
				$nx = $dx - $sx + 1;
				$q2 = "";
				if( $this->__skeep === false ) {
					$q2 .= "DELETE FROM {$this->__branch} WHERE <branch_delete>{$this->__qs}
					";
				}
				$q2 .= "DELETE FROM {$this->__tree} WHERE sx >= {$sx} AND dx <= {$dx}{$this->__qs}
				UPDATE {$this->__tree} SET sx = ( sx - {$nx} ) WHERE sx > {$dx}{$this->__qs}
				UPDATE {$this->__tree} SET dx = ( dx - {$nx} ) WHERE dx > {$dx}{$this->__qs}
				";
				if( $this->__skeep === false ) {
					$q3 = mysql_unbuffered_query (
						"SELECT
							id
						FROM
							{$this->__tree}
						WHERE
							sx >= {$sx}
						AND
							dx <= {$dx}",
						$this->__db
					);
					$q4 = "";
					while( $r = mysql_fetch_row( $q3 ) ) {
						$q4 .= "tree_id = {$r[0]} OR ";
					}
					$this->__queryReplaced( $q2, '<branch_delete>', $q4, 4 );
				}
				else {
					$this->__queryRollBackable( $q2 );
					$this->__skeep = false;
				}
			}
		}
	}
	
	function __moveNode( &$moved_branch_id, &$target_branch_id ) {
		$q1 = &$this->__firstQuery( $moved_branch_id );
		if( mysql_num_rows( $q1 ) === 1 ) {
			while( $r = mysql_fetch_row( $q1 ) ) {
				$fromdx = $r[0];
				$fromsx = $r[1];
				$fromid = $r[2];
			}
			$q1 = &$this->__firstQuery( $target_branch_id );
			if( mysql_num_rows( $q1 ) === 1 ) {
				while( $r = mysql_fetch_row( $q1 ) ) {
					$todx = $r[0];
					$tosx = $r[1];
					$toid = $r[2];
				}
				$blocks = ( $fromdx - ( $fromsx - 1 ) ) / 2;
				$changes = $blocks * 2;
				if( $blocks > 1 ) {
					$sql = "CREATE TABLE moved__{$this->__tree} ( id INT(10) NOT NULL AUTO_INCREMENT, old_id INT(10) UNSIGNED, sx INT(10) UNSIGNED, dx INT(10) UNSIGNED, PRIMARY KEY (id) ){$this->__qs}
					INSERT INTO moved__{$this->__tree} SELECT NULL, id, sx, dx FROM {$this->__tree} WHERE sx >= {$fromsx} AND dx <= {$fromdx}{$this->__qs}
					";
					$this->__queryRollBackable( $sql );
					$this->__skeep = true;
					$this->delNode( $moved_branch_id );
					$sql = "SELECT {$this->__tree}.sx, moved__{$this->__tree}.sx FROM {$this->__tree}, moved__{$this->__tree} WHERE {$this->__tree}.id = {$toid} AND moved__{$this->__tree}.old_id = {$fromid}";
					$q = mysql_unbuffered_query( $sql, $this->__db );
					while( $r = mysql_fetch_row( $q ) ) {
						$newsx = $r[0] + 1;
						$r[1] = (int)$r[1];
						$diff = 0;
						while( ( $r[1] + $diff ) !== $newsx ) {
							if( (int)$tosx > (int)$fromsx ) {
								$diff++;
							}
							else {
								$diff--;
							}
						}
						$sql = "UPDATE {$this->__tree} SET sx = ( sx + {$changes} ) WHERE sx >={$newsx}{$this->__qs}
						UPDATE {$this->__tree} SET dx = ( dx + {$changes} ) WHERE dx >={$newsx}{$this->__qs}
						UPDATE moved__{$this->__tree} SET sx = ( sx + {$diff} ), dx = ( dx + {$diff} ){$this->__qs}
						INSERT INTO {$this->__tree} ( id, sx, dx ) SELECT old_id, sx, dx FROM moved__{$this->__tree}{$this->__qs}
						DROP TABLE moved__{$this->__tree}{$this->__qs}
						";
					}
					$this->__queryRollBackable( $sql );
				}
				else {
					$this->__skeep = true;
					$this->delNode( $moved_branch_id );
					$this->__skeep = true;
					$this->addNode( $target_branch_id, $moved_branch_id );
				}
			}
		}
	}
	
	function __queryRollBackable( &$q ) {
		$rows = explode( $this->__qs, $q );
		for( $a = 0, $b = count($rows) - 1; $a < $b; $a++ ) {
			$query = mysql_query( trim( $rows[$a] ), $this->__db );
			if( !$query ) {
				@mysql_free_result( $query );
			}
		}
	}
	
	function __firstQuery( &$branch_id ) {
		$q = mysql_query (
			$this->__getFirstQuery($branch_id),
			$this->__db
		);
		return $q;
	}
	
	function __getFirstQuery( &$branch_id ){
		$query = "
			SELECT
				{$this->__tree}.dx, {$this->__tree}.sx, {$this->__tree}.id 
			FROM
				{$this->__branch}, {$this->__tree}
			WHERE
				{$this->__branch}.tree_id = {$this->__tree}.id
			AND
				{$this->__branch}.id = {$branch_id}
		";
		return $query;
	}
	
	function __queryReplaced( $q, $repl, $mod, $diff ) {
		$q = str_replace( $repl, substr( $mod, 0, -$diff ), $q );
		$this->__queryRollBackable( $q );
	}
}
?>
Return current item: MPTTA for PHP and MySQL