Location: PHPKode > scripts > SQL Tree > sql-tree/tree.class.php
<?
/**
* by Alex
* first unstable ;)
* mailto: hide@address.com
* http://codeteam.ru
*/
class CTTree
{
	private	$DBi; // DataBase identeficator
	public	$cfg; // Config options, e.g. DB, User, Host, etc...
	public	$tbl; // Table
	
	// Constructors and Destructors
	public function __construct($db)
	{
		if(is_resource($db))
		{
			$this->DBi = &$db;
		}
		elseif(is_array($db))
		{
			$this->cfg = $db;
			$this->connect();
			if(isset($this->cfg['tbl']))
			{
				$this->tbl = $this->cfg['tbl'];
			}
		}
		elseif(is_object($db))
		{
			
		}
	}
	public function __destruct()
	{
	//	print_r($this->queryToArr($this->query('SELECT * FROM pages')));
		//mysql_close($this->$DBi);
	}
	
	// DB functions
	private function connect()
	{
		$this->DBi = mysql_connect($this->cfg['host'],$this->cfg['user'],$this->cfg['pass'])
			or die("Could not connect: " . mysql_error());
		if(isset($this->cfg['db'])) $this->selDB($this->cfg['db']);
	}
	private function selDB($db)
	{
		mysql_select_db($db,$this->DBi)
			or die("Could not select DB: " . mysql_error());
	}
	private function query($query)
	{
		if(is_resource($this->DBi)) return mysql_query($query,$this->DBi);
		else $this->connect();
	}
	private function queryToArr(&$result)
	{
		$return = array();
		while ($row = mysql_fetch_array($result, MYSQL_ASSOC))
		{
			$return[] = $row;
		}
		//mysql_free_result($result);
		unset($row);
		return $return;
	}
	
	//Install functions
	public function install($table,$other=flase,$returnSQL=false)
	{
		$query = "CREATE TABLE IF NOT EXISTS `".$table."`".
		"(`id` int(10) NOT NULL auto_increment,".
		"`left` int(10) NOT NULL,`right` int(10) NOT NULL,".
		"`lvl` int(10) NOT NULL,".$this->prepareOther($other).
		"PRIMARY KEY  (`id`),KEY `pos` (`left`,`right`,`lvl`))".
		"ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='CTTree structure' AUTO_INCREMENT=1 ;";
		if($returnSQL==false) $this->query($query);
		else return $query;
	}
	private function prepareOther($other)
	{
		if($other==false) return '';
		$query = '';
		foreach($other as $name=>$params)
		{
			$query .= "`{$name}` ".implode(' ',$params).",\n";
		}
		return $query;
	}
	
	//Get structure functions
	public function getFullTree($params=false)
	{
		if(is_array($params)) $params=implode(',',$params);
		elseif($params == false) $params = '*';
		$query = 'SELECT '.$params.' FROM `'.$this->tbl.'` ORDER BY `left`';
		return $this->queryToArr($this->query($query));
	}
	public function getTree($left,$right,$params=false)
	{	
		if(is_array($params)) $params=implode(',',$params);
		elseif($params == false) $params = '*';
		$query = 'SELECT '.$params.' FROM `'.$this->tbl.'` WHERE `left` >= '.$left.' AND `right` <= '.$right.' ORDER BY `left`';
		return $this->queryToArr($this->query($query));
	}
	public function getParentsTree($left,$right,$params=false)
	{	
		if(is_array($params)) $params=implode(',',$params);
		elseif($params == false) $params = '*';
		$query = 'SELECT '.$params.' FROM `'.$this->tbl.'` WHERE `left` <= '.$left.' AND `right` >= '.$right.' ORDER BY `left`';
		return $this->queryToArr($this->query($query));
	}
	public function getOurTree($left,$right,$params=false)
	{	
		if(is_array($params)) $params=implode(',',$params);
		elseif($params == false) $params = '*';
		$query = 'SELECT '.$params.' FROM `'.$this->tbl.'` WHERE `right` > '.$left.' AND `left` < '.$right.' ORDER BY `left`';
		return $this->queryToArr($this->query($query));
	}
	public function getParent($id,$params=false)
	{
		if(is_array($params)) $params=implode(',',$params);
		elseif($params == false) $params = '*';
		$info = $this->getInfo($id);
		$query = 'SELECT '.$params.' FROM `'.$this->tbl.'` WHERE `left` <= '.$info['left'].' AND `right` >= '.$info['right'].' AND `lvl` = '.($info['lvl']-1).' ORDER BY `left`';
		return $this->queryToArr($this->query($query));
	}
	public function getInfo($id)
	{
		$query = 'SELECT left,right,lvl FROM `'.$this->tbl.'` WHERE `id` = '.$id;
		return $this->queryToArr($this->query($query));
	}
	
	//Create structure functions
	public function addEl($parentID,$content=false)
	{
		$info = $this->getInfo($parentID);
		$parm = '';
		if($content!==false) foreach($content as $name=>$par) $parm .= ', '.$name.' = '.((is_numeric($par))?$par:'`'.$par.'`');
		$q1 = "UPDATE `{$this->tbl}` SET `right` = `right`+2, `left` = IF(`left` > {$info['right']},`left`+2,`left`) WHERE `right` >= {$info['right']}";
		$q2 = "INSER INTO `{$this->tbl}` SET `left`={$info['right']}, `right`=({$info['right']}+1), `lvl` = ({$info['lvl']}+1)".$parm;
		$this->query($q1);
		$this->query($q2);
	}
	
	//Delete structure functions
	public function del($left,$right)
	{
		 $q1 = "DELETE FROM `{$this->tbl}` WHERE left>={$left} AND right<={$right}";
		 $q2 = "UPDATE `{$this->tbl}` SET left = IF(left>{$left},left-({$right}-{$left}+1),left), right = right-({$right}-{$left}+1) WHERE right > {$right}";
		 $this->query($q1);
		 $this->query($q2);
	}
	
	//Replace structure functions
	public function replace($id,$newParentId)
	{
		
	}
}
?>
Return current item: SQL Tree