Location: PHPKode > scripts > PHP Tree Structure stored in MySQL database > php-tree-structure-stored-in-mysql-database/index.php
<?php
/*
cNode

PHP Class for storing and rerieving node based structures in a MySql database

Notes:
cNode::addChild
	breaks encapsulation and uses a hack to pull the mysql_insert_id .. may not be supported in the future versions of PEAR::DB

Must pass a reference to a PEAR::DB object that is already connected to the DB


Class Methods:

To be updated


HELPER Functions:

function createNodeDB( &$db )
	To create a DB with the proper structure call

function checkNode( $nodeId )
	accepts node ID # 
	returns true if node exists, false if none




Copyright (c) 2006, Takeshi Media

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. 

*/

define( 'ROOT', 0 );

class cNode 
{
	protected $db;


// accepts reference to database
	function __construct( $node_id, &$db  )
	{
		$this->db = $db;
		$this->db->setFetchMode(DB_FETCHMODE_ASSOC);

		if( checkNodeExists( $node_id, $db ))
		{
			$this->nodeId = $node_id;
		} else if( $node_id == ROOT ) {
			$this->nodeId = $node_id;	
		} else {
			die('cNode::__construct() failed');
		}
	}

// accepts node id
// returns array of child node id's
	function getChildren()
	{
		$sql = 'SELECT id FROM nodes WHERE parent_id = ' . $this->nodeId;
		$children = false;	
		$children = $this->db->getCol( $sql, 'id' );
		if( PEAR::isError( $parent_id ) )
		{
			die('cNode::getChildren() failed');
		}

		if( $length = count( $children ))
		{
			for( $i = 0 ; $i < $length; $i++ )
			{
				$children[$i] = new cNode( $children[$i], $this->db );
			}
		}

		return $children;	
	}

	function getNodeId()
	{
		return $this->nodeId;
	}


// accpts node id
// returns parent node or false if none
	function getParent()
	{
		$sql = 'SELECT parent_id FROM nodes WHERE id = ' . $this->nodeId;

		$parent_node = false;	
		$parent_node = $this->db->getOne( $sql );
		if( PEAR::isError( $parent_id ) )
		{
			die('cNode::getParent() failed');
		}
		$parent_node = new cNode( $parent_id, $this->db );	

		return $parent_node;
	}

// get all the sibling Nodes of the node passed in 
	function getSiblings()
	{
		$parent_id = $this->getParent( $this->nodeId );
		$sql = 'SELECT id FROM nodes WHERE parent_id = ' . $parent_id . ' AND id != ' . $this->nodeId;
		$result = $this->db->query($sql );
		if( PEAR::isError( $result ))
		{
			die('cNode::getSiblings() failed');
		}

		$siblings = $this->db->getCol($sql, 'id' );
		if( PEAR::isError( $siblings ))
		{
			die('cNode::getSiblings() failed');
		}

		return $siblings;
	}

// accepts node id
// returns array of nodes ordered from starting node to root node or false if root node
	function getNodesToRoot()
	{
		$path = array();
		array_push( $path, $this->nodeId); 

		while( $nodeId = $this->getParent( $this->nodeId ))
		{
			array_push( $path, $nodeId); 
		}

		return $path;
	}

// returns true or dies
	function setParent( cNode $newParent )
	{
		$sql = 'UPDATE nodes SET parent_id = ' . $newParent->getNodeId() . ' WHERE id = ' . $this->getNodeId();

		if( PEAR::isError( $parent_id ) )
		{
			die('cNode::getParent() failed');
		}

		return true;
	}


// add child to parent 
// return the child Id
	function addChild()
	{
		$sql = 'INSERT INTO nodes SET parent_id = ' . $this->nodeId;
		$result = $this->db->query( $sql );
		if( PEAR::isError( $result ) )
		{
			die('cNode::addChild() failed');
		} 

// hack for the stupid non support for AUTO_INCREMENT
		$childNode = new cNode( mysql_insert_id($this->db->connection), $this->db );

		return $childNode;
	}

}




// remove a node and all children Ids
function deleteNode( cNode $node, $removeChildren = true , $db )
{
	$children = $node->getChildren();

	if( $removeChildren ) 
	{

		if( count( $children ))
		{
			foreach( $children as $child )
			{		
				deleteNode( $child, true );
			}
		}
		
	} else {				
		$parent_node = $node->getParent();
		
		foreach( $children as $child )
		{
			$child->setParent( $parent_node );
		}
	}

	$sql = 'DELETE FROM nodes WHERE id = ' . $node->getNodeId();
	$result = $db->query( $sql );
	if( PEAR::isError( $result ) )
	{
		die('deleteNode() failed');
	}
	return true;
}


// check if a node exists
function checkNodeExists( $nodeId, &$db )
{
	$nodeOk = false;

	$sql = 'SELECT id FROM nodes WHERE id = ' . $nodeId;
	$result = $db->query( $sql );
	if( $result->fetchRow() )
	{
		$nodeOk = true;
	} 

	return $nodeOk;
}




// takes reference to a db object
// using PEAR DB Class

function createNodeDB( &$db )
{
	$sql = 	'CREATE TABLE `nodes` (
			  `id` int(11) NOT NULL auto_increment,
			  `parent_id` int(11) NOT NULL default "0",
			  PRIMARY KEY  (`id`)
		) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;';

	$db->query( $sql );
}




?>
Return current item: PHP Tree Structure stored in MySQL database