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是最重要的   

  

 

 

二  实现原理

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('&nbsp;',$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);

实现的效果  

 

实例二  面包屑导航

 

 

 

 

 

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=');