mysql部门无限分类_mysql 无限级分类

两种思路吧，递归 和 非递归

递归

$arr = [

1=>['id'=>1,'pid'=>0],

2=>['id'=>2,'pid'=>0],

3=>['id'=>3,'pid'=>1],

4=>['id'=>4,'pid'=>1],

5=>['id'=>5,'pid'=>0],

6=>['id'=>6,'pid'=>3],

7=>['id'=>7,'pid'=>6],

8=>['id'=>8,'pid'=>3],

9=>['id'=>9,'pid'=>4],

10=>['id'=>10,'pid'=>7],

11=>['id'=>11,'pid'=>7],

];

//排序数组arr 层数level 键val

function display_test($arr,$level,$val){

if($level==0)

{

echo "顶级目录\n";

}else{

echo str_repeat(' ',$level).$arr[$val]['id']."\n";

}

$temp = get($arr,$val);

foreach ($temp as $v)

{

display_test($arr,$level+1,$v['id']);

}

}

//获取 pid的所有下一级子节点

function get($arr,$pid)

{

$res = [];

foreach ($arr as $v)

{

if($v['pid']==$pid)

{

$res[] = $v;

}

}

return $res;

}

display_test($arr,0,0);die;

非递归 左右值法

表结构

CREATE TABLE `sys_department` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(20) DEFAULT NULL COMMENT '部门名称',

`description` varchar(200) DEFAULT NULL COMMENT '描述',

`create_time` int(11) DEFAULT NULL COMMENT '添加时间',

`left_value` int(11) DEFAULT NULL,

`right_value` int(11) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='部门表'

添加新下级：用事物

//修改右值

$sql = "UPDATE {$this->table} SET right_value = right_value+2 WHERE right_value>=:right_value";

//修改左值

$sql = "UPDATE {$this->table} SET left_value = left_value+2 WHERE left_value>=:left_value";

//插入

$sql = "INSERT INTO {$this->table}(name,description,left_value,right_value,create_time) VALUES(:name,:description,:left_value,:right_value,:create_time)";

删除节点： 用事物

//删除

$sql = "DELETE FROM {$this->table} WHERE left_value>=:left_value AND right_value<=:right_value";

//修改左值

$sql = "UPDATE {$this->table} SET left_value=left_value-{$offset} WHERE left_value>:left_value";

//修改右值

$sql = "UPDATE {$this->table} SET right_value=right_value-{$offset} WHERE right_value>:right_value";

获取所有节点，包含缩进

public function display_tree($id){

//获取节点

$model = $this->model->getById($id);

if(!$model)

{

return false;

}

$left = $model['left_value'];

$right = $model['right_value'];

//获取子节点 按left_value排序

$res = $this->getByLeftRight($left,$right);

$temp = [];

//循环找出节点所在级数

foreach ($res as $k=>$v)

{

if(count($temp)>0)

{

// 检查我们是否应该将节点移出堆栈

while ($temp[count($temp) - 1] < $v['right_value']) {

array_pop($temp);

}

}

//echo str_repeat("*",count($temp)).$v['name']."\n";

$res[$k]['level'] = count($temp);

$temp[] = $v['right_value'];

}

//level 第几层

return $res;

}