php+mysql查询实现无限下级分类树输出示例_php技巧_脚本之家

极端分类是个老话题了,PHP和MySQL中贯彻Infiniti分类的笔触也会有成都百货上千种,本文首要介绍当中2种最高超的十二万分分类方法,一齐来探视吧。

最佳分类是个老话题了,来探访PHP结合Mysql如何兑现。

正文实例陈诉了php+mysql查询实现Infiniti下级分类树输出。分享给大家供咱们参照他事他说加以考察,具体如下:

图片 1

率先种办法

此地介绍的php结合mysql查询Infiniti下级树输出,其实正是极端分类。给诸位收拾了多少个phpInfiniti分类的例子.

率先种办法

这种方法是很广阔、很传统的风华正茂种,先看表构造

表:category
id   int   主键,自增
name    varchar    分类名称
pid    int    父类id,默认0

蓬蓬勃勃品分类的 pid
默许就是0了。当我们想抽取有些分类的子分类树的时候,基本思路正是递归,当然,出于作用难题不提出每一次递归都查询数据库,常常的做法是先讲全部分类抽取来,保存到PHP数组里,再开展管理,最后仍可以将结果缓存起来以抓实后一次乞请的作用。

先来营造叁个原始数组,那一个一向从数据库中拉出去就行:

$categories = array(
    array('id'=>1,'name'=>'电脑','pid'=>0),
    array('id'=>2,'name'=>'手机','pid'=>0),
    array('id'=>3,'name'=>'笔记本','pid'=>1),
    array('id'=>4,'name'=>'台式机','pid'=>1),
    array('id'=>5,'name'=>'智能机','pid'=>2),
    array('id'=>6,'name'=>'功能机','pid'=>2),
    array('id'=>7,'name'=>'超级本','pid'=>3),
    array('id'=>8,'name'=>'游戏本','pid'=>3),
);

对象是将它转载为下边这种组织

电脑
—笔记本
——-超级本
——-游戏本
—台式机
手机
—智能机
—功能机

用数组来表示的话,能够扩展一个 children 键来存款和储蓄它的子分类:

array(
    //1对应id,方便直接读取
    1 => array(
        'id'=>1,
        'name'=>'电脑',
        'pid'=>0,
        children=>array(
            &array(
                'id'=>3,
                'name'=>'笔记本',
                'pid'=>1,
                'children'=>array(
                    //此处省略
                )
            ),
            &array(
                'id'=>4,
                'name'=>'台式机',
                'pid'=>1,
                'children'=>array(
                    //此处省略
                )
            ),
        )
    ),
    //其他分类省略
)

管理进程:

$tree = array();
//第一步,将分类id作为数组key,并创建children单元
foreach($categories as $category){
    $tree[$category['id']] = $category;
    $tree[$category['id']]['children'] = array();
}
//第二部,利用引用,将每个分类添加到父类children数组中,这样一次遍历即可形成树形结构。
foreach ($tree as $k=>$item) {
    if ($item['pid'] != 0) {
        $tree[$item['pid']]['children'][] = &$tree[$k];
    }
}
print_r($tree);

打印结果如下:

Array
(
    [1] => Array
        (
            [id] => 1
            [name] => 电脑
            [pid] => 0
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 3
                            [name] => 笔记本
                            [pid] => 1
                            [children] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 7
                                            [name] => 超级本
                                            [pid] => 3
                                            [children] => Array
                                                (
                                                )
                                        )
                                    [1] => Array
                                        (
                                            [id] => 8
                                            [name] => 游戏本
                                            [pid] => 3
                                            [children] => Array
                                                (
                                                )
                                        )
                                )
                        )
                    [1] => Array
                        (
                            [id] => 4
                            [name] => 台式机
                            [pid] => 1
                            [children] => Array
                                (
                                )
                        )
                )
        )
    [2] => Array
        (
            [id] => 2
            [name] => 手机
            [pid] => 0
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 5
                            [name] => 智能机
                            [pid] => 2
                            [children] => Array
                                (
                                )
                        )
                    [1] => Array
                        (
                            [id] => 6
                            [name] => 功能机
                            [pid] => 2
                            [children] => Array
                                (
                                )
                        )
                )
        )
    [3] => Array
        (
            [id] => 3
            [name] => 笔记本
            [pid] => 1
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 7
                            [name] => 超级本
                            [pid] => 3
                            [children] => Array
                                (
                                )
                        )
                    [1] => Array
                        (
                            [id] => 8
                            [name] => 游戏本
                            [pid] => 3
                            [children] => Array
                                (
                                )
                        )
                )
        )
    [4] => Array
        (
            [id] => 4
            [name] => 台式机
            [pid] => 1
            [children] => Array
                (
                )
        )
    [5] => Array
        (
            [id] => 5
            [name] => 智能机
            [pid] => 2
            [children] => Array
                (
                )
        )
    [6] => Array
        (
            [id] => 6
            [name] => 功能机
            [pid] => 2
            [children] => Array
                (
                )
        )
    [7] => Array
        (
            [id] => 7
            [name] => 超级本
            [pid] => 3
            [children] => Array
                (
                )
        )
    [8] => Array
        (
            [id] => 8
            [name] => 游戏本
            [pid] => 3
            [children] => Array
                (
                )
        )
)

优点:关系清楚,改良上下级关系轻巧。

弱点:使用PHP管理,假诺分类数量宏大,作用也会减少。

这种方法是很普遍、很守旧的一种,先看表构造

function get_array{global $mysql,$_G; $sql = "select user_id as name from `{spreads_users}` where p1.spreads_userid='{$user_id}'";$rows= $mysql->db_fetch_arrays{ $arr[0]['name']=$user_id; $arr[0]['children']=array(); } $top=$top+1;foreach ($rows as $key=>$value) { $r = get_array; //调用函数,传入参数,继续查询下级 $arr[0]['children'][$key]['name']= $value['username']; //组合数组 if{ $arr[0]['children'][$key]['children']= $r[0]['children']; } $i++; } return $arr; }$list = get_array; //调用函数1000是顶级IDecho 'var data='.json_encode;

其次种情势

这种办法是在表字段中加进三个path字段:

表:category
id   int   主键,自增
name   varchar   分类名称
pid   int   父类id,默认0
path   varchar   路径

示范数据:

id        name        pid        path
1         电脑        0          0
2         手机        0          0
3         笔记本      1          0-1
4         超级本      3          0-1-3
5         游戏本      3          0-1-3
path字段记录了从根分类到上顶尖父类的门径,用id+’-‘表示。

这种措施,即使大家要查询Computer下的全体后代分类,只要求一条sql语句:

select id,name,path from category where path like (select
concat(path,’-‘,id,’%’) as path from category where id=1);
结果:

+—-+———–+——-+
| id | name      | path  |
+—-+———–+——-+
| 3  | 笔记本 | 0-1   |
| 4  | 超级本 | 0-1-3 |
| 5  | 游戏本 | 0-1-3 |
+—-+———–+——-+
这种措施也被许多个人所选用,笔者总括了下:

可取:查询轻便,成效高,path字段可以加索引。

缺欠:更新节点关系麻烦,必要更新具有后辈的path字段。

表:category
id int 主键,自增
name varchar 分类名称
pid int 父类id,默认0
一流分类的 pid
暗中认可就是0了。当大家想抽出某些分类的子分类树的时候,基本思路便是递归,当然,出于功用难点不提议每次递归都查询数据库,通常的做法是先讲全数分类抽取来,保存到PHP数组里,再扩充管理,最后还是能够将结果缓存起来以加强后一次呼吁的效能。

以此是出口 Array 然后转让为 json

先来营造四个原始数组,这一个一贯从数据库中拉出来就可以:

表布局:id字段为分类标志,name字段为分类名,father_id字段为所属父分类的id,path字段为分类路线,积存该分类祖先的汇集,isdir判定是不是是目录,1为是,0为否.

复制代码 代码如下:
$categories = array(
    array(‘id’=>1,’name’=>’电脑’,’pid’=>0),
    array(‘id’=>2,’name’=>’手机’,’pid’=>0),
    array(‘id’=>3,’name’=>’笔记本’,’pid’=>1),
    array(‘id’=>4,’name’=>’台式机’,’pid’=>1),
    array(‘id’=>5,’name’=>’智能机’,’pid’=>2),
    array(‘id’=>6,’name’=>’功能机’,’pid’=>2),
    array(‘id’=>7,’name’=>’超级本’,’pid’=>3),
    array(‘id’=>8,’name’=>’游戏本’,’pid’=>3),
);

来得函数:

对象是将它转载为上面这种组织

//$count为分类等级sort_list($str,$fatherid,$count){$rs = $this->sql->re_datas("select * from sort where father_id = fatherid");$num = $this->sql->sql_numrows();$i=0;$n = 1;while{$name = "";for($n = 1 ; $n < $count ; $n ){$name.="│ ";}if{$name.="└─".$rs[$i][name];}else{$name.="├─".$rs[$i][name];}if{$str.="".$name."";}else{$str.=$name";}$temp = $count 1;$str = $this->sort_list($str,$rs[$i][id],$temp);$i ;}return $str;}

电脑
    笔记本
        超级本
        游戏本
    台式机
手机
    智能机
    功能机
用数组来代表的话,能够扩大叁个 children 键来囤积它的子分类:

此中$this->sql对象为sql操作类对象,re_datas(卡塔尔(قطر‎函数再次来到查到的数组,sql_numrows(State of Qatar函数重回查询到的数目.

复制代码 代码如下:
array(
    //1对应id,方便直接读取
    1 => array(
        ‘id’=>1,
        ‘name’=>’电脑’,
        ‘pid’=>0,
        children=>array(
            &array(
                ‘id’=>3,
                ‘name’=>’笔记本’,
                ‘pid’=>1,
                ‘children’=>array(
                    //此处省略
                )
            ),
            &array(
                ‘id’=>4,
                ‘name’=>’台式机’,
                ‘pid’=>1,
                ‘children’=>array(
                    //此处省略
                )
            ),
        )
    ),
    //别的分类省略
)

$sort_list = sort_list;

管理进度:

id int 主键,自增name varchar 分类名称pid int 父类id,默许0

复制代码 代码如下:
$tree = array();
//第一步,将分类id作为数组key,并创设children单元
foreach($categories as $category){
    $tree[$category[‘id’]] = $category;
    $tree[$category[‘id’]][‘children’] = array();
}
//第二部,利用援引,将每一种分类加多到父类children数组中,那样二遍遍历就能够造成树形布局。
foreach ($tree as $k=>$item) {
    if ($item[‘pid’] != 0) {
        $tree[$item[‘pid’]][‘children’][] = &$tree[$k];
    }
}
print_r($tree);

五星级分类的 pid
暗中认可正是0了,当大家想抽出某些分类的子分类树的时候,基本思路正是递归,当然,出于功用难点不提出每一遍递归都查询数据库,平日的做法是先讲全部分类抽出来,保存到PHP数组里,再张开始拍录卖,最后还足以将结果缓存起来以增加后一次恳求的频率.先来营造二个原始数组,那几个平素从数据库中拉出来就能够:

打字与印刷结果如下:

$categories = array( array('id'=>1,'name'=>'电脑','pid'=>0), array('id'=>2,'name'=>'手机','pid'=>0), array('id'=>3,'name'=>'笔记本','pid'=>1), array('id'=>4,'name'=>'台式机','pid'=>1), array('id'=>5,'name'=>'智能机','pid'=>2), array('id'=>6,'name'=>'功能机','pid'=>2), array('id'=>7,'name'=>'超级本','pid'=>3), array('id'=>8,'name'=>'游戏本','pid'=>3),);

复制代码 代码如下:
Array
(
    [1] => Array
        (
            [id] => 1
            [name] => 电脑
            [pid] => 0
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 3
                            [name] => 笔记本
                            [pid] => 1
                            [children] => Array
                                (
                                    [0] => Array
                                        (
                                            [id] => 7
                                            [name] => 超级本
                                            [pid] => 3
                                            [children] => Array
                                                (
                                                )
                                        )
                                    [1] => Array
                                        (
                                            [id] => 8
                                            [name] => 游戏本
                                            [pid] => 3
                                            [children] => Array
                                                (
                                                )
                                        )
                                )
                        )
                    [1] => Array
                        (
                            [id] => 4
                            [name] => 台式机
                            [pid] => 1
                            [children] => Array
                                (
                                )
                        )
                )
        )
    [2] => Array
        (
            [id] => 2
            [name] => 手机
            [pid] => 0
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 5
                            [name] => 智能机
                            [pid] => 2
                            [children] => Array
                                (
                                )
                        )
                    [1] => Array
                        (
                            [id] => 6
                            [name] => 功能机
                            [pid] => 2
                            [children] => Array
                                (
                                )
                        )
                )
        )
    [3] => Array
        (
            [id] => 3
            [name] => 笔记本
            [pid] => 1
            [children] => Array
                (
                    [0] => Array
                        (
                            [id] => 7
                            [name] => 超级本
                            [pid] => 3
                            [children] => Array
                                (
                                )
                        )
                    [1] => Array
                        (
                            [id] => 8
                            [name] => 游戏本
                            [pid] => 3
                            [children] => Array
                                (
                                )
                        )
                )
        )
    [4] => Array
        (
            [id] => 4
            [name] => 台式机
            [pid] => 1
            [children] => Array
                (
                )
        )
    [5] => Array
        (
            [id] => 5
            [name] => 智能机
            [pid] => 2
            [children] => Array
                (
                )
        )
    [6] => Array
        (
            [id] => 6
            [name] => 功能机
            [pid] => 2
            [children] => Array
                (
                )
        )
    [7] => Array
        (
            [id] => 7
            [name] => 超级本
            [pid] => 3
            [children] => Array
                (
                )
        )
    [8] => Array
        (
            [id] => 8
            [name] => 游戏本
            [pid] => 3
            [children] => Array
                (
                )
        )
)

对象是将它转载为上边这种结构:

优点:关系清楚,纠正上下级关系轻易。

电脑—笔记本——-超级本——-游戏本—台式机手机—智能机—功能机

缺点:使用PHP处理,如若分类数量小幅,作用也会下降。

用数组来代表的话,能够追加贰个 children 键来积累它的子分类:

第二种形式

array( //1对应id,方便直接读取 1 => array( 'id'=>1, 'name'=>'电脑', 'pid'=>0, children=>array( &array( 'id'=>3, 'name'=>'笔记本', 'pid'=>1, 'children'=>array, &array( 'id'=>4, 'name'=>'台式机', 'pid'=>1, 'children'=>array, //其他分类省略)

这种艺术是在表字段中加进一个path字段:

处理进度:

表:category
id int 主键,自增
name varchar 分类名称
pid int 父类id,默认0
path varchar 路径
演示数据:

$tree = array();//第一步,将分类id作为数组key,并创建children单元foreach($categories as $category){ $tree[$category['id']] = $category; $tree[$category['id']]['children'] = array();}//第二部,利用引用,将每个分类添加到父类children数组中,这样一次遍历即可形成树形结构。foreach  { if  { $tree[$item['pid']]['children'][] = &$tree[$k]; }}

id        name        pid        path
1         电脑        0          0
2         手机        0          0
3         笔记本      1          0-1
4         超级本      3          0-1-3
5         游戏本      3          0-1-3
path字段记录了从根分类到上一流父类的路子,用id+’-‘表示。

print_r;打字与印刷结果如下:

这种艺术,假如大家要查询计算机下的有着后代分类,只必要一条sql语句:

Array( [1] => Array ( [id] => 1 [name] => 电脑 [pid] => 0 [children] => Array ( [0] => Array ( [id] => 3 [name] => 笔记本 [pid] => 1 [children] => Array ( [0] => Array ( [id] => 7 [name] => 超级本 [pid] => 3 [children] => Array  [1] => Array ( [id] => 8 [name] => 游戏本 [pid] => 3 [children] => Array  [1] => Array ( [id] => 4 [name] => 台式机 [pid] => 1 [children] => Array  [2] => Array ( [id] => 2 [name] => 手机 [pid] => 0 [children] => Array ( [0] => Array ( [id] => 5 [name] => 智能机 [pid] => 2 [children] => Array  [1] => Array ( [id] => 6 [name] => 功能机 [pid] => 2 [children] => Array  [3] => Array ( [id] => 3 [name] => 笔记本 [pid] => 1 [children] => Array ( [0] => Array ( [id] => 7 [name] => 超级本 [pid] => 3 [children] => Array  [1] => Array ( [id] => 8 [name] => 游戏本 [pid] => 3 [children] => Array  [4] => Array ( [id] => 4 [name] => 台式机 [pid] => 1 [children] => Array  [5] => Array ( [id] => 5 [name] => 智能机 [pid] => 2 [children] => Array  [6] => Array ( [id] => 6 [name] => 功能机 [pid] => 2 [children] => Array  [7] => Array ( [id] => 7 [name] => 超级本 [pid] => 3 [children] => Array  [8] => Array ( [id] => 8 [name] => 游戏本 [pid] => 3 [children] => Array 

select id,name,path from category where path like (select
concat(path,’-‘,id,’%’) as path from category where id=1);
结果:

优点:关系清楚,修改上下级关系轻松.

+—-+———–+——-+
| id | name      | path  |
+—-+———–+——-+
| 3  | 笔记本 | 0-1   |
| 4  | 超级本 | 0-1-3 |
| 5  | 游戏本 | 0-1-3 |
+—-+———–+——-+
这种艺术也被广大人所选用,小编计算了下:

缺点:使用PHP处理,要是分类数量小幅度,效用也会裁减.

亮点:查询轻易,功能高,path字段能够加索引。

越来越多关于PHP相关内容感兴趣的读者可查看本站专项论题:《php+mysql数据库操作入门教程》、《PHP基本语法入门教程》、《PHP运算与运算符用法计算》、《php面向对象程序设计入门教程》、《PHP网络编制程序才具总计》、《PHP数组操作才干大全》、《php字符串用法总括》及《php布衣蔬食数据库操作手艺汇总》

症结:更新节点关系麻烦,供给立异具备后辈的path字段。

指望本文所述对我们PHP程序设计具备助于。

如上就是本文的全体内容了,二种方法,你心爱哪一种?希望大家可以中意。

发表评论

电子邮件地址不会被公开。 必填项已用*标注