<?php
/**
* A class to use nested sets easiely
* @author Mark Stuppacher <hide@address.com>
* @license http://opensource.org/licenses/gpl-3.0.html GNU General Public License version 3 (GPLv3)
* @version 0.6
* @
*
*/
class NestedSet
{
/*Properties*/
/**
* Mysqli object
* @var object
*/
protected $db;
/**
* Name of the database table
* @var string
*/
public $table = '';
/**
* Primary key of the database table
* @var string
*/
public $pk = '';
/**
* Namefield in the database table
* @var unknown_type
*/
public $name = '';
/*Methods*/
/**
* Stores a Mysqli object for further use
* @param object $mysqli Mysqli object
* @return boolean true
*/
public function __construct($mysqli) {
$this->db = $mysqli;
return true;
}
/**
* Creates the root node
* @param string $name Name of the new node
* @return boolean true
*/
public function createRootNode($name) {
$this->db->query("LOCK TABLES " . $this->table . " WRITE");
$sql = "SELECT rgt FROM " . $this->table . " ORDER BY rgt DESC LIMIT 1";
$result = $this->db->query($sql);
if ($this->db->affected_rows == 0) {
$lft = 1;
$rgt = 2;
} else {
$obj = $result->fetch_object();
$lft = $obj->rgt + 1;
$rgt = $lft + 1;
}
$sql = "INSERT INTO " . $this->table . " (" . $this->name . ", lft, rgt) VALUES ('" . $name . "', " . $lft . ", " . $rgt . ");";
$this->db->query($sql);
$this->db->query("UNLOCK TABLES");
return true;
}
/**
* Creates a new node
* @param string $name name of the new node
* @param integer $lft lft of parent node
* @param integer $rgt rgt of parent node
* @return boolean true
*/
protected function insertNode($name, $lft, $rgt) {
$sql = "UPDATE " . $this->table . " SET rgt = rgt + 2 WHERE rgt >= " . $rgt . ";";
$this->db->query($sql);
$sql = "UPDATE " . $this->table . " SET lft = lft + 2 WHERE lft > " . $rgt . ";";
$this->db->query($sql);
$sql = "INSERT INTO " . $this->table . " (" . $this->name . ", lft, rgt) VALUES ('" . $name . "', " . $rgt . ", " . $rgt . "+1);";
$this->db->query($sql);
return true;
}
/**
* Gets an object with all data of a node
* @param integer $id id of the node
* @return object object with node-data (id, lft, rgt)
*/
protected function getNode($id) {
$sql = "SELECT " . $this->pk . ", lft, rgt, " . $this->name . " FROM " . $this->table . " WHERE " . $this->pk . " = " . $id . ";";
$result = $this->db->query($sql);
if ($this->db->affected_rows == 0) {
return $this->error(0);
}
$node = $result->fetch_object();
return $node;
}
/**
* Creates a new child node of the node with the given id
* @param string $name name of the new node
* @param integer $parent id of the parent node
* @return boolean true
*/
public function insertChildNode($name, $parent) {
$this->db->query("LOCK tables " . $this->table . " WRITE;");
$p_node = $this->getNode($parent);
$this->insertNode($name, $p_node->lft, $p_node->rgt);
$this->db->query("UNLOCK TABLES;");
return true;
}
/**
* Creates a multi-dimensional array of the whole tree
* @return array multi-dimenssional array of the whole tree
*/
public function getTree() {
$sql = "SELECT n." . $this->pk . ", n." . $this->name . ", COUNT(*)-1 AS level FROM " . $this->table . " AS n, " . $this->table . " AS p WHERE n.lft BETWEEN p.lft AND p.rgt GROUP BY n.lft ORDER BY n.lft;";
$result = $this->db->query($sql);
if ($this->db->affected_rows == 0) {
return $this->error(1, true);
}
$tree = array();
$i = 0;
while ($row = $result->fetch_assoc()) {
$tree[$i] = $row;
$i++;
}
return $tree;
}
/**
* Get the HTML code for an unordered list of the tree
* @return string HTML code for an unordered list of the whole tree
*/
public function treeAsHtml() {
$tree = $this->getTree();
$html = "<ul>\n";
for ($i=0; $i<count($tree); $i++) {
$html .= "<li>" . $tree[$i][$this->name];
if ($tree[$i]['level'] < $tree[$i+1]['level']) {
$html .= "\n<ul>\n";
} elseif ($tree[$i]['level'] == $tree[$i+1]['level']) {
$html .= "</li>\n";
} else {
$diff = $tree[$i]['level'] - $tree[$i+1]['level'];
$html .= str_repeat("</li>\n</ul>\n", $diff) . "</li>\n";
}
}
$html .= "</ul>\n";
return $html;
}
/**
* Deletes a node an all it's children
* @param integer $id id of the node to delete
* @return boolean true
*/
public function deleteNode($id) {
$this->db->query("LOCK tables " . $this->table . " WRITE;");
$node = $this->getNode($id);
$sql = "DELETE FROM " . $this->table . " WHERE lft BETWEEN " . $node->lft . " AND " . $node->rgt . ";";
$this->db->query($sql);
$sql = "UPDATE " . $this->table . " SET lft = lft - ROUND((" . $node->rgt . " - " . $node->lft . " + 1)) WHERE lft > " . $node->rgt . ";";
$this->db->query($sql);
$sql = "UPDATE " . $this->table . " SET rgt = rgt - ROUND((" . $node->rgt . " - " . $node->lft . " + 1)) WHERE rgt > " . $node->rgt . ";";
$this->db->query($sql);
$this->db->query("UNLOCK TABLES;");
return true;
}
/**
* Deletes a node and increases the level of all children by one
* @param integer $id id of the node to delete
* @return boolean true
*/
public function deleteSingleNode($id) {
$this->db->query("LOCK tables " . $this->table . " WRITE;");
$node = $this->getNode($id);
$sql = "DELETE FROM " . $this->table . " WHERE lft = " . $node->lft . ";";
$this->db->query($sql);
$sql = "UPDATE " . $this->table . " SET lft = lft - 1, rgt = rgt - 1 WHERE lft BETWEEN " . $node->lft . " AND " . $node->rgt . ";";
$this->db->query($sql);
$sql = "UPDATE " . $this->table . " SET lft = lft - 2 WHERE lft > " . $node->rgt . ";";
$this->db->query($sql);
$sql = "UPDATE " . $this->table . " SET rgt = rgt - 2 WHERE rgt > " . $node->rgt . ";";
$this->db->query($sql);
$this->db->query("UNLOCK TABLES;");
return true;
}
/**
* Gets a multidimensional array containing the path to defined node
* @param integer $id id of the node to which the path should point
* @return array multidimensional array with the data of the nodes in the tree
*/
public function getPath($id) {
$sql = "SELECT p." . $this->pk . ", p." . $this->name . " FROM " . $this->table . " n, " . $this->table . " p WHERE n.lft BETWEEN p.lft AND p.rgt AND n." . $this->pk ." = " . $id . " ORDER BY p.lft;";
$result = $this->db->query($sql);
if ($this->db->affected_rows == 0) {
return $this->error(0);
}
$path = array();
$i = 0;
while ($row = $result->fetch_assoc()) {
$path[$i] = $row;
$i++;
}
return $path;
}
/**
* Gets the id of a node depending on it's rgt value
* @param integer $rgt rgt value of the node
* @return integer id of the node
*/
protected function getIdRgt($rgt) {
$sql = "SELECT " . $this->pk . " FROM " . $this->table . " WHERE rgt = " . $rgt . ";";
$result = $this->db->query($sql);
if ($this->db->affected_rows == 0) {
return false;
}
$obj = $result->fetch_object();
return $obj->{$this->pk};
}
/**
* Moves a node one position to the left staying in the same level
* @param $nodeId id of the node to move
* @return boolean true
*/
public function moveLft($nodeId) {
$this->db->query("LOCK tables " . $this->table . " WRITE;");
$node = $this->getNode($nodeId);
$brotherId = $this->getIdRgt($node->lft-1);
if ($brotherId == false) {
return $this->error(3);
}
$brother = $this->getNode($brotherId);
$nodeSize = $node->rgt - $node->lft + 1;
$brotherSize = $brother->rgt - $brother->lft + 1;
$sql = "SELECT " . $this->pk . " FROM " . $this->table . " WHERE lft BETWEEN " . $node->lft . " AND " . $node->rgt . ";";
$result = $this->db->query($sql);
$idsNotToMove = array();
while ($obj = $result->fetch_object()) {
$idsNotToMove[] = $obj->{$this->pk};
}
$sql = "UPDATE " . $this->table . " SET lft = lft - " . $brotherSize . ", rgt = rgt - " . $brotherSize . " WHERE lft BETWEEN " . $node->lft . " AND " . $node->rgt . ";";
$this->db->query($sql);
$sql = "UPDATE " . $this->table . " SET lft = lft + " . $nodeSize . ", rgt = rgt + " . $nodeSize . " WHERE lft BETWEEN " . $brother->lft . " AND " . $brother->rgt;
for ($i = 0; $i < count($idsNotToMove); $i++) {
$sql .= " AND " . $this->pk . " != " . $idsNotToMove[$i];
}
$sql .= ";";
$this->db->query($sql);
$this->db->query("UNLOCK TABLES;");
return true;
}
/**
* Gets the id of a node depending on it's lft value
* @param integer $lft lft value of the node
* @return integer id of the node
*/
protected function getIdLft($lft) {
$sql = "SELECT " . $this->pk . " FROM " . $this->table . " WHERE lft = " . $lft . ";";
$result = $this->db->query($sql);
if ($this->db->affected_rows == 0) {
return false;
}
$obj = $result->fetch_object();
return $obj->{$this->pk};
}
/**
* Moves a node one position to the right staying in the same level
* @param $nodeId id of the node to move
* @return boolean true
*/
public function moveRgt($nodeId) {
$this->db->query("LOCK tables " . $this->table . " WRITE;");
$node = $this->getNode($nodeId);
$brotherId = $this->getIdLft($node->rgt+1);
if ($brotherId == false) {
return $this->error(2);
}
$brother = $this->getNode($brotherId);
$nodeSize = $node->rgt - $node->lft + 1;
$brotherSize = $brother->rgt - $brother->lft + 1;
$sql = "SELECT " . $this->pk . " FROM " . $this->table . " WHERE lft BETWEEN " . $node->lft . " AND " . $node->rgt . ";";
$result = $this->db->query($sql);
$idsNotToMove = array();
while ($obj = $result->fetch_object()) {
$idsNotToMove[] = $obj->{$this->pk};
}
$sql = "UPDATE " . $this->table . " SET lft = lft + " . $brotherSize . ", rgt = rgt + " . $brotherSize . " WHERE lft BETWEEN " . $node->lft . " AND " . $node->rgt . ";";
$this->db->query($sql);
$sql = "UPDATE " . $this->table . " SET lft = lft - " . $nodeSize . ", rgt = rgt - " . $nodeSize . " WHERE lft BETWEEN " . $brother->lft . " AND " . $brother->rgt;
for ($i = 0; $i < count($idsNotToMove); $i++) {
$sql .= " AND " . $this->pk . " != " . $idsNotToMove[$i];
}
$sql .= ";";
$this->db->query($sql);
$this->db->query("UNLOCK TABLES;");
return true;
}
/**
* Prints a error message
* @param int $id array-key of the message
* @param boolean $continue continue script or not
* @return void
*/
public function error($id, $continue = false) {
$errors = array ();
$errors[] = 'There is no node with the given id!';
$errors[] = 'No entries!';
$errors[] = 'Node can\'t be moved to the right!';
$errors[] = 'Node can\'t be moved to the left!';
echo $errors[$id];
if ($continue == false) {
exit;
}
}
}
?>