начал портировать класс dbtree для своих нужд. если кому интересно:
mysql helper class:
Код:
<?php
///////////////////////////////
// Description: ClassHelper for Mysql
// Author: Denis 'Sherman' Gabaidulin © 2002-2004
// Version: 0.3.3 alpha-test(global version 2)
// Comments: __________________
///////////////////////////////
////////////////////////////////////////////////////////
// +added mysql_num_rows check for result
////////////////////////////////////////////////////////
class MySql
{
var $dbServer = "";
var $dbName = "";
var $dbUser = "";
var $dbPass = "";
var $dbConn;
var $dbResult;
var $dbErrorNumber = 0;
var $dbErrorMessage = "";
var $debugFromHanlder = "";
/*function MySql()
{
//default constructor
}*/
function MySql($dbServer = null, $dbName = null, $dbUser = null, $dbPass = null)
{
//default constructor
if ($dbServer != null)
{
$this->dbServer = $dbServer;
}
if ($dbName != null)
{
$this->dbName = $dbName;
}
if ($dbUser != null)
{
$this->dbUser = $dbUser;
}
if ($dbPass != null)
{
$this->dbPass = $dbPass;
}
}
function MySql_Connect()
{
if (!isset($this->dbConn) || $this->dbConn == null)
{
$this->dbConn = @mysql_connect(
$this->dbServer,
$this->dbUser,
$this->dbPass);
$this->MySql_ErrorHanlder("connect");
}
}
function MySql_SelectDb()
{
if (isset($this->dbName) && $this->dbName != null)
{
@mysql_select_db($this->dbName,$this->dbConn);
$this->MySql_ErrorHanlder("select");
}
}
function MySql_QueryDb($dbQuery)
{
if (isset($dbQuery) && $dbQuery != null)
{
$this->dbResult = @mysql_query($dbQuery,$this->dbConn);
//echo $dbQuery;
$this->MySql_ErrorHanlder("query");
if (@mysql_num_rows($this->dbResult) == 0)
{
$this->dbResult=null;
}
}
}
function MySql_Close()
{
if (isset($this->dbConn) && $this->dbConn != null)
{
@mysql_close($this->dbConn);
$this->MySql_ErrorHanlder("close");
}
}
function MySql_Free()
{
@mysql_free($this->dbResult);
$this->MySql_ErrorHanlder("free");
}
function MySql_ErrorHanlder($debugFromHanlder)
{
$this->dbErrorNumber = mysql_errno();
$this->dbErrorMessage = mysql_error();
if ($this->dbErrorNumber != 0)
{
echo $this->dbErrorNumber . " " . $this->dbErrorMessage;
echo " " . $debugFromHanlder . "<BR>\r\n";
die;
}
/*if ($this->dbErrorNumber == 2003)
{
sleep(2);
}*/
$this->dbErrorNumber = 0;
$this->dbErrorMessage = "";
}
}
?>
Table
Код:
CREATE TABLE categories (
cat_ID int(10) unsigned NOT NULL auto_increment,
cat_LEFT int(10) unsigned NOT NULL default '0',
cat_RIGHT int(10) unsigned NOT NULL default '0',
cat_LEVEL int(10) unsigned NOT NULL default '0',
cat_TITLE varchar(255) default NULL,
cat_CREATION_DATE timestamp(14) NOT NULL,
cat_MODIFY_DATE timestamp(14) NOT NULL,
cat_DESC text NOT NULL,
cat_PHOTO_PATH text,
PRIMARY KEY (cat_ID),
KEY cat_left (cat_LEFT,cat_RIGHT,cat_LEVEL)
) TYPE=MyISAM;
Это пока еще рыба:
Код:
require_once $DOCUMENT_ROOT . "/sherman/classes/mysql.inc";
// nested sets implementation(by Sherman)
// based on "dbtree" class
// Author: Maxim Poltarak <maxx at e dash taller dot net>
// WWW: http://dev.e-taller.net/dbtree/
class Category2
{
var $nodeInfo = array(
"id"=>0,
"left"=>0,
"right"=>0,
"level"=>0,
"data"=>array(
"title"=>"",
"creation_date"=>0,
"modify_date"=>0,
"desc"=>"",
"photo"=>""));
var $nodeInfoCollection = array();
function getNodeInfo($categoryID)
{
$db = new MySql(null,"mport4",null,null);
$db->MySql_Connect();
$db->MySql_SelectDb();
$selectQuery = "select *
from categories where cat_ID = " . $categoryID;
$db->MySql_QueryDb($selectQuery);
if ($db->dbResult != null)
{
while($row = mysql_fetch_assoc($db->dbResult))
{
$this->nodeInfo["id"] = $row["cat_ID"];
$this->nodeInfo["left"] = $row["cat_LEFT"];
$this->nodeInfo["right"] = $row["cat_RIGHT"];
$this->nodeInfo["level"] = $row["cat_LEVEL"];
$this->nodeInfo["data"]["title"] = $row["cat_TITLE"];
$this->nodeInfo["data"]["creation_date"] = $row["cat_CREATION_DATE"];
$this->nodeInfo["data"]["modify_date"] = $row["cat_MODIFY_DATE"];
$this->nodeInfo["data"]["desc"] = $row["cat_DESC"];
$this->nodeInfo["data"]["photo"] = $row["cat_PHOTO_PATH"];
}
return 0;
}
return -1;
}
function enumChildren($categoryID, $startLevel=1, $endLevel=1, $order=null)
{
$orderExpression = "";
if($startLevel < 0)
{
return -1;
}
if ($order != null)
{
$orderExpression = " order by " . $order;
}
// We could use sprintf() here, but it'd be too slow
$whereSql1 = " and categories.cat_LEVEL";
$whereSql2 = '_categories.cat_LEVEL+';
if (!$endLevel)
{
$whereSql = $whereSql1 . ">=" . $whereSql2 . (int)$startLevel;
}
else
{
$whereSql = ($endLevel <= $startLevel)
? $whereSql1.'='.$whereSql2.(int)$startLevel
: ' and categories.cat_LEVEL between _categories.cat_LEVEL+'.(int)$startLevel
.' and _categories.cat_LEVEL+'.(int)$endLevel;
}
$selectQuery = "select categories.*
from categories _categories, categories
where _categories.cat_ID = " . $categoryID .
" and categories.cat_LEFT between _categories.cat_LEFT and _categories.cat_RIGHT" .
$whereSql . $orderExpression;
$db = new MySql(null,"mport4",null,null);
$db->MySql_Connect();
$db->MySql_SelectDb();
$db->MySql_QueryDb($selectQuery);
if ($db->dbResult != null)
{
while($row = mysql_fetch_assoc($db->dbResult))
{
$this->nodeInfo["id"] = $row["cat_ID"];
$this->nodeInfo["left"] = $row["cat_LEFT"];
$this->nodeInfo["right"] = $row["cat_RIGHT"];
$this->nodeInfo["level"] = $row["cat_LEVEL"];
$this->nodeInfo["data"]["title"] = $row["cat_TITLE"];
$this->nodeInfo["data"]["creation_date"] = $row["cat_CREATION_DATE"];
$this->nodeInfo["data"]["modify_date"] = $row["cat_MODIFY_DATE"];
$this->nodeInfo["data"]["desc"] = $row["cat_DESC"];
$this->nodeInfo["data"]["photo"] = $row["cat_PHOTO_PATH"];
$this->nodeInfoCollection[] = $this->nodeInfo;
}
return 0;
}
return -1;
}
function EnumChildrenAll($categoryParentID)
{
return $this->enumChildren($categoryParentID, 1, 0);
}
function insertNode($categoryParentID, $nodeInfoData)
{
//get node info
if ($this->getNodeInfo($categoryParentID) != 0)
{
return -1;
}
// creating a place for the record being inserted
if($categoryParentID)
{
$updateQuery = "update categories
set cat_LEFT=if(cat_LEFT>" . (int)$this->nodeInfo["right"] . ",cat_LEFT+2,cat_LEFT),"
. "cat_RIGHT=if(cat_RIGHT>=" .(int)$this->nodeInfo["right"] . ",cat_RIGHT+2,cat_RIGHT)"
. "where cat_RIGHT>=" . (int)$this->nodeInfo["right"];
$db = new MySql(null, "mport4", null, null);
$db->MySql_Connect();
$db->MySql_SelectDb();
$db->MySql_QueryDb($updateQuery);
//echo $updateQuery . "<BR>";
if (mysql_affected_rows($db->dbConn) > 0) //need to fix?
{
//echo mysql_affected_rows();
// inserting new record
$insertQuery = "insert into categories
(cat_LEFT, cat_RIGHT, cat_LEVEL,
cat_TITLE, cat_CREATION_DATE,
cat_MODIFY_DATE, cat_DESC,
cat_PHOTO_PATH)
values(" .
(int)$this->nodeInfo["right"] . "," .
(int)($this->nodeInfo["right"]+1) . "," .
(int)($this->nodeInfo["level"]+1) . ",'" .
$nodeInfoData["title"] . "'," .
(int)time() . "," .
(int)time() . ",'" .
$nodeInfoData["desc"] . "','" .
$nodeInfoData["photo"] . "')";
//echo $insertQuery . "<BR>";
$db = new MySql(null, "mport4", null, null);
$db->MySql_Connect();
$db->MySql_SelectDb();
$db->MySql_QueryDb($insertQuery);
if (mysql_affected_rows($db->dbConn) > 0)
{
//echo mysql_affected_rows();
return 0;
}
}
}
return -1;
}
}
?>
желательно все бы делать в транзакциях, но где их взять-то?
на mysql 3.23.58 --no-innodb
еще один метод(удаляет ветку):
Код:
function deleteAll($categoryID)
{
//get node info
if ($this->getNodeInfo($categoryID) != 0)
{
return -1;
}
$db = new MySql(null, "mport4", null, null);
$db->MySql_Connect();
$db->MySql_SelectDb();
// Deleteing record(s)
$deleteQuery = "DELETE FROM categories
WHERE cat_LEFT BETWEEN "
.(int)$this->nodeInfo["left"] .
" AND " .(int)$this->nodeInfo["right"];
//echo $deleteQuery;
$db->MySql_QueryDb($deleteQuery);
if (mysql_affected_rows($db->dbConn) > 0)
{
// Clearing blank spaces in a tree
$deltaID = ((int)$this->nodeInfo["right"] - (int)$this->nodeInfo["left"])+1;
$updateQuery = 'UPDATE categories SET
cat_LEFT=IF(cat_LEFT>'.(int)$this->nodeInfo["left"].',cat_LEFT-'.$deltaID.',cat_LEFT),
cat_RIGHT=IF(cat_RIGHT>'.$this->nodeInfo["left"].',cat_RIGHT-'.$deltaID.',cat_RIGHT)
WHERE cat_RIGHT>'.(int)$this->nodeInfo["right"];
//echo $updateQuery;
$db = new MySql(null, "mport4", null, null);
$db->MySql_Connect();
$db->MySql_SelectDb();
$db->MySql_QueryDb($updateQuery);
if (mysql_affected_rows($db->dbConn) > 0)
{
return 0;
}
}
return -1;
}