数据表结构和数据
表结构
- CREATE TABLE `classify` (
- `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
- `name` VARCHAR(20) NOT NULL COLLATE 'utf8_general_ci',
- `lft` INT(11) NOT NULL,
- `rgt` INT(11) NOT NULL,
- `parentId` INT(11) NOT NULL,
- PRIMARY KEY (`id`),
- INDEX `scope` (`lft`, `rgt`)
- )
- COLLATE='utf8_bin'
- ENGINE=InnoDB
- AUTO_INCREMENT=13;
测试数据
- INSERT INTO `classify` VALUES (1, '郑州', 1, 16, -1);
- INSERT INTO `classify` VALUES (19, '荥阳', 10, 15, 1);
- INSERT INTO `classify` VALUES (20, '荥阳东', 13, 14, 19);
- INSERT INTO `classify` VALUES (21, '荥阳西', 11, 12, 19);
- INSERT INTO `classify` VALUES (22, '开封', 2, 9, 1);
- INSERT INTO `classify` VALUES (23, '开封东', 7, 8, 22);
- INSERT INTO `classify` VALUES (24, '开封西', 5, 6, 22);
- INSERT INTO `classify` VALUES (25, '开封府', 3, 4, 22);
文件结构
- /——
- |--index.php 入口文件
- |--conf.php 配置文件
- /db
- |--DB.class.php 数据库操作类
- /lib
- |--ClassifyTree.class.php 左右树操作文件【core】
index.php 代码
conf.php 文件
- <?php
- return array(
- 'dbHost'=>'localhost',
- 'dbName'=>'classify_infinite',
- 'dbUser'=>'root',
- 'dbPass'=>'root',
- 'dbCharset'=>'utf8'
- );
DB.class.php 文件
ClassifyTree.class.php 文件
- <?php
- class ClassifyTree
- {
- private $db;
- private $error;
- public function __construct($conf){
- $this->db = new DB($conf);
- $this->error = $this->db->getError();
- }
- /**
- * [返回错误信息]
- * @return [string] [错误信息]
- */
- public function getError(){
- return $this->error;
- }
- /**
- * [获取整个分类树结构]
- * @return [array|boolean] [如果查询失败,则返回false;否则,返回格式化后的分类数组]
- */
- public function getAll(){
- $sql = 'SELECT `id`,`name`,`lft`,`rgt`,`parentId` FROM `classify`';
- $classifyInfo = $this->db->queryList($sql);
- if(false === $classifyInfo){
- $this->error = '查询出错。'.$this->db->getError();
- return false;
- }
- //格式化数组
- $result = $this->format($classifyInfo);
- return $result;
- }
- /**
- * [格式化检索到的分类数据库中的数据,将信息组织成
- * array(
- * array(** 分类a **)
- * 'childrens'=>array(
- * array(** 分类b **)
- * 'childrens'=>array(
- * ......
- * )
- * )
- * )
- * 的形式
- * ]
- * @param [array] $classifyInfo [需要格式化的数据]
- * @param [integer] $parentId [父分类的id]
- * @return [array] [格式化后的数据]
- */
- private function format(&$classifyInfo, $parentId=-1){
- $result = array();//需要返回的结果数组
- foreach($classifyInfo as $key=>$oneInfo){
- if($parentId == $oneInfo['parentId']){
- $childrens = $this->format($classifyInfo, $oneInfo['id']);
- if(!empty($childrens)){
- $oneInfo['childrens'] = $childrens;
- }
- $result[] = $oneInfo;
- }
- }
- return $result;
- }
- /**
- * [获取某个分类到根分类的路径]
- * @param [int] $id [分类id]
- * @return [array|boolean] [如果查询失败,则返回false;否则,返回路径数组]
- */
- public function getPath($id){
- //查询$id的分类和根分类的左右值
- $sql = 'SELECT `id`,`lft`,`rgt` FROM `classify` WHERE `id`='.$id.' or `parentId`=-1';
- $classifyInfo = $this->db->queryList($sql, true);
- if(false === $classifyInfo){
- $this->error = '查询失败:'.$this->db->getError();
- return false;
- }
- if(1 != count($classifyInfo)){
- $left = $classifyInfo[$id]['lft'];
- $right = $classifyInfo[$id]['rgt'];
- unset($classifyInfo[$id]);
- $classifyInfo = array_pop($classifyInfo);
- $rootLeft = $classifyInfo['lft'];
- $rootRight = $classifyInfo['rgt'];
- }else{
- $rootLeft = $left = $classifyInfo[$id]['lft'];
- $rootRight = $right = $classifyInfo[$id]['rgt'];
- }
- //查询当前节点到根节点的距离
- $sql = 'SELECT `id`,`name`,`lft`,`rgt`,`parentId` '
- .'FROM `classify` '
- .'WHERE `lft`>='.$rootLeft.' AND `lft`<='.$left.' AND `rgt`<='.$rootRight.' AND `rgt`>='.$right
- .' ORDER BY `lft` ';
- $classifyPath = $this->db->queryList($sql);
- if(false === $classifyPath){
- $this->error = '查询失败:'.$this->db->getError();
- return false;
- }
- return $classifyPath;
- }
- /**
- * [获取指定分类下的分类]
- * @param [int] $id [分类id]
- * @return [array|boolean] [如果查询失败,则返回false;否则,返回格式化后的分类数组]
- */
- public function getOne($id){
- //查询$id分类的左右值
- $sql = 'SELECT `lft`,`rgt` FROM `classify` WHERE `id`='.$id;
- $oneInfo = $this->db->queryOne($sql);
- if(false === $classifyInfo){
- $this->error = '查询失败:'.$this->db->getError();
- return false;
- }
- $left = $oneInfo['lft'];
- $right = $oneInfo['rgt'];
- //查询该分类下的所有分类
- $sql = 'SELECT `id`,`name`,`lft`,`rgt`,`parentId` FROM `classify` WHERE `lft`>='.$left.' AND `rgt`<='.$right;
- $classifyInfo = $this->db->queryList($sql);
- if(false === $classifyPath){
- $this->error = '查询失败:'.$this->db->getError();
- return false;
- }
- //格式化数组
- $result = $this->format($classifyInfo);
- return $result;
- }
- /**
- * [在一个分类下添加子分类]
- * @param [int] $parentId [父分类的id]
- * @param [string] $name [增加的分类的名称]
- * @return [boolean] [增加成功,则返回true;否则,返回false]
- */
- public function insertNew($parentId, $name){
- $this->db->begin();
- //查询当前分类的左右值
- $sql = 'SELECT `lft` FROM `classify` WHERE `id`='.$parentId;
- $oneInfo = $this->db->queryOne($sql);
- if(false === $oneInfo){
- $this->error = '查询失败:'.$this->db->getError();
- $this->db->rollBack();
- return false;
- }
- $left = $oneInfo['lft'];
-
- //将所有左值大于当前分类左值的分类左右值加2
- $sql = 'UPDATE `classify` SET `lft`=`lft`+2,`rgt`=`rgt`+2 WHERE `lft`>'.$left;
- $result = $this->db->update($sql);
- if(false === $result){
- $this->error = '更新节点左右值失败.'.$this->db->getError();
- $this->db->rollBack();
- return false;
- }
- //更新右节点大于当前分类左值的分类节点
- $sql = 'UPDATE `classify` SET `rgt`=`rgt`+2 WHERE `rgt`>'.$left.' AND `lft`<='.$left;
- $result = $this->db->update($sql);
- if(false === $result){
- $this->error = '更新节点右值失败.'.$this->db->getError();
- $this->db->rollBack();
- return false;
- }
- //插入新的节点
- $left += 1;
- $right = $left+1;
- $sql = "INSERT INTO `classify` VALUES(null, '{$name}', {$left}, {$right}, {$parentId})";
- $result = $this->db->insert($sql);
- if(false === $result){
- $this->error = '插入新节点失败.'.$this->db->getError();
- $this->db->rollBack();
- return false;
- }
- $this->db->submit();
- return true;
- }
- /**
- * [删除一个分类信息]
- * @param [int] $id [分类id]
- * @return [boolean] [删除成功,则返回true;否则,返回true]
- */
- public function delete($id){
- $this->db->begin();
- //查询当前分类的左右值
- $sql = 'SELECT `lft`,`rgt` FROM `classify` WHERE `id`='.$id;
- $oneInfo = $this->db->queryOne($sql);
- if(false === $oneInfo){print_r(debug_backtrace());
- $this->error = '查询失败:'.$this->db->getError();
- $this->db->rollBack();
- return false;
- }
- $left = $oneInfo['lft'];
- $right = $oneInfo['rgt'];
- $dValue = $right - $left + 1;
- //删除当前分类及其子分类
- $sql = 'DELETE FROM `classify` WHERE `lft`>='.$left.' AND `rgt`<='.$right;
- $result = $this->db->delete($sql);
- if(false === $result){
- $this->error = '删除失败:'.$this->db->getError();
- $this->db->rollBack();
- return false;
- }
- //将所有左值大于当前分类左值的分类左右值加2
- $sql = 'UPDATE `classify` SET `lft`=`lft`-'.$dValue.' , `rgt`=`rgt`-'.$dValue.' WHERE `lft`>'.$right;
- $result = $this->db->update($sql);
- if(false === $result){
- $this->error = '更新节点左值失败.'.$this->db->getError();
- $this->db->rollBack();
- return false;
- }
- //更新右节点大于当前分类左值的分类节点
- $sql = 'UPDATE `classify` SET `rgt`=`rgt`-'.$dValue.' WHERE `lft`<'.$left.' AND '.' `rgt`>'.$left;
- $result = $this->db->update($sql);
- if(false === $result){
- $this->error = '更新节点右值失败.'.$this->db->getError();
- $this->db->rollBack();
- return false;
- }
- $this->db->submit();
- return true;
- }
- /**
- * [根据id查询一个分类的信息]
- * @param [int] $id [分类id]
- * @return [array|boolean] [查询失败,则返回false;否则,返回分类信息数组]
- */
- public function searchById($id){
- //查询当前分类的左右值
- $sql = 'SELECT `id`,`name`,`lft`,`rgt`,`parentId` FROM `classify` WHERE `id`='.$id;
- $oneInfo = $this->db->queryOne($sql);
- if(false === $oneInfo){
- $this->error = '查询失败:'.$this->db->getError();
- return false;
- }
- return $oneInfo;
- }
- /**
- * [保存修改过的分类信息]
- * @param [string] $newName [分类的新名称]
- * @param [int] $id [分类id]
- * @return [boolean] [如果修改成功,则返回true;否则,返回false]
- */
- public function modify($newName, $id){
- $sql = "UPDATE `classify` SET `name`='{$newName}' WHERE `id`={$id}";
- $result = $this->db->update($sql);
- if(false === $result){
- $this->error = '更新失败:'.$this->db->getError();
- return false;
- }
- return true;
- }
- }
例子程序链接:
左右值无限级分类
相关教程
mysql左右值无限分类原理及实现
mysql 无限级分类实现思路
预排序遍历树算法(非递归无限极分类算法)学习笔记
|