1.2 全路径实现__无限分类
优点:查询方便,一句sql搞定
缺点:增加、移动分类时 维护数据稍显复杂
全路径实现
表中有字段path:1,2,3 存放各级分类的id值,使用 where path like '1,2,3%' order by cpath asc;
查出它及其子类的列表
一 核心实现
1 用一个path字段(varchar类型)把它所有的父级ID按顺序记录下来实现无限分类
2 一段SQL语句 拼接全路径
$sql = "select id,name,path,concat(path,',',id) as full_path from bbs_demo order by full_path";//这段SQL是最重要的
![](https://img2020.cnblogs.com/blog/1727725/202012/1727725-20201227094856183-410404375.png)
二 实现原理
1 利用全路径字段(1,2,3)加上id组成新字段正序排列
2 利用字段长度(用逗号分割)来计算出层级深度
三 表设置
实例一:添加树形菜单
<?php //1 连接数据库 $link = mysqli_connect('localhost','root','root','sfkbbs'); if (mysqli_connect_errno()) { exit(mysqli_connect_error()); } mysqli_set_charset($link,'utf8'); //2 核心函数 function likeCate($link){ $sql = "select id,name,path,concat(path,',',id) as full_path from bbs_demo order by full_path";//这段SQL是最重要的 $result = mysqli_query($link,$sql); $data = []; while ($row = mysqli_fetch_assoc($result)) { $row['full_path'] = trim($row['full_path'],',');//去除左右的逗号 $deep = count(explode(',',$row['full_path']));//目录的层级深度 $row['name'] = str_repeat(' ',$deep*4).'|----'.$row['name']; $data[] = $row; } return $data; } //3 对外展示函数 function displayCate($link){ $data = likeCate($link); $str = ''; $str .= "<select name=''>"; foreach ($data as $key=>$val) { $str .= "<option>{$val['name']}</option>"; } $str .= "</select>"; return $str; } //4 调用函数 echo displayCate($link);
实现的效果
![](https://img2020.cnblogs.com/blog/1727725/202012/1727725-20201227104123116-827825080.png)
实例二 面包屑导航
1 通过$id查找数据 并构造出全路径 full_path
mysql> select *,CONCAT(path,',',id) as full_path from bbs_demo where id=4; +----+--------+------+-------------+------+-----------+ | id | name | path | create_time | sort | full_path | +----+--------+------+-------------+------+-----------+ | 4 | 太原 | 1,2 | 0 | 0 | 1,2,4 | +----+--------+------+-------------+------+-----------+
2 把全路径当成条件 用in查询
<?php //1 连接数据库 $link = mysqli_connect('localhost','root','root','sfkbbs'); if (mysqli_connect_errno()) { exit(mysqli_connect_error()); } mysqli_set_charset($link,'utf8'); //2 核心函数 function getPath($link,$id=2){ $data = []; $sql = "select *,CONCAT(path,',',id) as full_path from bbs_demo where id=$id"; $result = mysqli_query($link,$sql); $row = mysqli_fetch_assoc($result); if ($row) { $ids = $row['full_path'];//把全路径值赋值给 id的in 条件 $sql = "select * from bbs_demo where id in ({$ids})";//重点是这句sql in查询 $result = mysqli_query($link,$sql); while ($row = mysqli_fetch_assoc($result)) { $data[] = $row; } } return $data; } //3 显示函数 function displayPath($link,$id,$url='area.php?id='){ $data = getPath($link,$id); $str = ''; foreach ($data as $key=>$val) { $str .= "<a href='{$url}{$val['id']}'>{$val['name']}</a>>"; } return $str; } //4 调用函数 echo displayPath($link,4,'index.php?id=');