<?php// connect to the db$conn = mysql_connect('localhost', 'test', 'test', TRUE);mysql_select_db('test', $conn);// instanciate$mptt = new MPTT($conn);$mptt->configure('mptt', 'left_id', 'right_id', 'id');/*$mptt->insert(0);$mptt->insert(1);$mptt->insert(2);$mptt->insert(3);$mptt->insert(1);$mptt->insert(5);*///$mptt->delete(5);//$mptt->insert(15);$mptt->export();/** * Work with a MPTT tree. Based on the queries described at * http://www.developer.com/db/article.php/10920_3517366_2 * @author Peter Goodman * @copyright Copyright 2007 Peter Goodman, all rights reserved. */class MPTT { /** * Database link resource. * @internal */ private $conn; /** * Databse table configurations. * @internal */ private $db_table, $db_left_id, $db_right_id, $db_primary_id; /** * Constructor, bring in the database connection. */ public function __construct($conn_id) { if(!$conn_id || !is_resource($conn_id)) { throw new Exception("MPTT::__construct expects first parameter passed to be a database resource."); } $this->conn = $conn_id; } /** * Configure the MPTT class. */ public function configure($table, $left, $right, $id) { $this->db_table = $table; $this->db_left_id = $left; $this->db_right_id = $right; $this->db_primary_id = $id; } /** * Query the database. * @internal */ private function db_query($sql, $multi_rows = FALSE) { $res = mysql_query($sql, $this->conn); if(!$res || !is_resource($res)) { return FALSE; } if(($num_rows = mysql_num_rows($res)) > 0) { if($num_rows == 1 || !$multi_rows) { return mysql_fetch_array($res, MYSQL_NUM); } else { $rows = array(); while($row = mysql_fetch_array($res, MYSQL_NUM)) { $rows[] = $row; } return $rows; } } return FALSE; } /** * Get the last insert ID from the database. * @internal */ private function db_insert_id() { return mysql_insert_id($this->conn); } /** * Insert a node onto the MPTT tree given the primary key of * its parent. If zero is supplied, this will be a top-level node. */ public function insert($parent_id = 0) { // get the parent level $parent_level = 1; if(FALSE !== ($row = $this->db_query("SELECT {$this->db_right_id} FROM {$this->db_table} WHERE {$this->db_primary_id}={$parent_id}"))) { $parent_level = (int)$row[0]; } // update the left ids $sql = "UPDATE {$this->db_table} "; $sql .= "SET {$this->db_left_id} = CASE WHEN {$this->db_left_id} > {$parent_level} THEN "; $sql .= " {$this->db_left_id} + 2 "; $sql .= " ELSE "; $sql .= " {$this->db_left_id} "; $sql .= " END "; $sql .= "WHERE {$this->db_right_id} >= {$parent_level};"; $this->db_query($sql); // update the right ids $sql = "UPDATE {$this->db_table} "; $sql .= "SET {$this->db_right_id} = CASE WHEN {$this->db_right_id} >= {$parent_level} THEN"; $sql .= " {$this->db_right_id} + 2"; $sql .= " ELSE"; $sql .= " {$this->db_right_id} "; $sql .= " END "; $sql .= "WHERE {$this->db_right_id} >= {$parent_level};"; $this->db_query($sql); // insert the new node $sql = "INSERT INTO {$this->db_table} ({$this->db_left_id},{$this->db_right_id})"; $sql .= "VALUES({$parent_level}, ({$parent_level} + 1));"; $this->db_query($sql); // return the insert ID if(FALSE !== ($id = $this->db_insert_id())) { return $id; } // whoops, problem. return FALSE; } /** * Delete a node on the MPTT tree by the value of its primary key. */ public function delete($id = 0) { // get the node $sql = "SELECT {$this->db_left_id}, {$this->db_right_id} FROM {$this->db_table} WHERE {$this->db_primary_id}={$id};"; @list($deleted_left, $deleted_right) = $this->db_query($sql); // whoops, this isn't good! Must be nothing to delete if(empty($deleted_left) || empty($deleted_right)) { return FALSE; } // delete those node and all nodes under it $sql = "DELETE FROM {$this->db_table} WHERE {$this->db_left_id} BETWEEN {$deleted_left} AND {$deleted_right};"; $this->db_query($sql); // update the left ids $sql = "UPDATE {$this->db_table} SET "; $sql .= "{$this->db_left_id} = CASE "; $sql .= " WHEN {$this->db_left_id} > {$deleted_left} THEN "; $sql .= " {$this->db_left_id} - ({$deleted_right} - {$deleted_left} + 1) "; $sql .= " ELSE "; $sql .= " {$this->db_left_id} "; $sql .= "END "; $sql .= "WHERE {$this->db_left_id} > {$deleted_left} OR {$this->db_right_id} > {$deleted_right};"; $this->db_query($sql); // update the right ids $sql = "UPDATE {$this->db_table} SET "; $sql .= "{$this->db_right_id} = CASE "; $sql .= " WHEN {$this->db_right_id} > {$deleted_right} THEN "; $sql .= " {$this->db_right_id} - ({$deleted_right} - {$deleted_left} + 1) "; $sql .= " ELSE "; $sql .= " {$this->db_right_id} "; $sql .= "END "; $sql .= "WHERE {$this->db_left_id} > {$deleted_left} OR {$this->db_right_id} > {$deleted_right};"; $this->db_query($sql); return TRUE; } /** * Move a node and all of its children from one part of the tree to another. */ public function move($node_id, $target_parent_id) { } /** * Export the nodes on the tree to a readable format. */ public function export() { $sql = "SELECT a.{$this->db_primary_id}, GROUP_CONCAT(b.{$this->db_primary_id} ORDER BY b.{$this->db_left_id} ASC SEPARATOR ' : ') "; $sql .= "FROM {$this->db_table} a, {$this->db_table} b "; $sql .= "WHERE b.{$this->db_left_id} < a.{$this->db_left_id} AND b.{$this->db_right_id} > a.{$this->db_right_id} "; $sql .= "GROUP BY a.{$this->db_left_id} ORDER BY a.{$this->db_left_id} ASC"; $rows = $this->db_query($sql, TRUE); // ecport the primary keys echo '<table>'; echo '<th>Export of Tree Structure by Values of Primary Keys ['. $this->db_primary_id .'] for Table ['. $this->db_table .']</th>'; if($rows) { foreach($rows as $row) { echo '<tr>'; echo '<td>'. $row[1] .' : '. $row[0] .'</td>'; echo '</tr>'; } } echo '</table>'; }}