PHP实现查询多级分类的程序代码

WBOY

Libérer： 2016-05-25 16:43:37

original

1690 Les gens l'ont consulté

无限级分类是我们常用见的一个程序方法了,原理是得到有层级关系的数组,就是顶级是顶级分类,然后每个分类中有个children子数组,记录它的子分类,这样一级一级的分级数组.

算法的数据库结构设计最为简单,category表中一个字段id,一个字段fid(父id),这样可以根据WHERE id = fid来判断上一级内容,运用递归至最顶层.

我们先查出数组,代码如下:

<?php
$a = array(
    &#39;AAAAAA&#39; => array(
        &#39;aaaaaa&#39; => array(
            &#39;111111&#39;,
            &#39;222222&#39;,
            &#39;333333&#39;
        ) ,
        &#39;bbbbbb&#39; => array(
            &#39;111111&#39;,
            &#39;222222&#39;,
            &#39;333333&#39;
        ) ,
        &#39;cccccc&#39; => array(
            &#39;111111&#39;,
            &#39;222222&#39;,
            &#39;333333&#39;
        ) ,
    ) ,
    &#39;BBBBBB&#39; => array(
        &#39;aaaaaa&#39; => array(
            &#39;111111&#39;,
            &#39;222222&#39;,
            &#39;333333&#39;
        ) ,
        &#39;bbbbbb&#39; => array(
            &#39;111111&#39;,
            &#39;222222&#39;,
            &#39;333333&#39;
        ) ,
        &#39;cccccc&#39; => array(
            &#39;111111&#39;,
            &#39;222222&#39;,
            &#39;333333&#39;
        ) ,
    ) ,
    &#39;CCCCCC&#39; => array(
        &#39;aaaaaa&#39; => array(
            &#39;111111&#39;,
            &#39;222222&#39;,
            &#39;333333&#39;
        ) ,
        &#39;bbbbbb&#39; => array(
            &#39;111111&#39;,
            &#39;222222&#39;,
            &#39;333333&#39;
        ) ,
        &#39;cccccc&#39; => array(
            &#39;111111&#39;,
            &#39;222222&#39;,
            &#39;333333&#39;
        ) ,
    ) ,
);
foreach ($a as $k => $v) {
    echo $k . "<br>";
    // if(is_array($v)){
    foreach ($v as $key => $val) {
        echo "  " . $key . "<br>";
        //     }
        if (is_array($val)) {
            foreach ($val as $kkk => $vall) {
                echo "    " . $vall . "<br>";
            }
        }
    }
    echo "<br>";
}
/*******mysql查询无限级分类的代码******/
/***
   $sql = "SELECT a.Title AS big, b.Title AS small  
            FROM largeTitle AS a LEFT JOIN smallTitle  AS b ON  a.ID=b.LargeID"; 
             
    $a = array(); 
     
    $r = mysql_query($sql); 
     
    while( $arr = mysql_fetch_array($r)){ 
        $a[$arr[&#39;big&#39;]] = $arr[&#39;small&#39;]; 
    } 
    ***/
?>

Copier après la connexion

好了下面先读取数据库然后再递归读出.

分类表,比如category,字段有 id,parentid,title,代码如下:

<?php
//查询
$dsql->SetQuery("SELECT * FROM category ORDER BY sortorder ASC");
$dsql->Execute(&#39;parentlist&#39;);
$array = array();
$parentlist = array();
while ($rs = $dsql->getObject(&#39;parentlist&#39;)) {
    if ($rs->parentid == 0) {
        $parentlist[$rs->id] = (array)$rs;
    } else {
        $array[$rs->id] = (array)$rs;
    }
}
$parentlist = cat_options($parentlist, $array); //我们求的结果数组
//$list父级分类的数组
//$array是除父级分类外的全部分类的数组
function cat_options(&$list, &$array) {
    foreach ($list as $key => $arr) {
        foreach ($array as $k => $value) {
            if ($value[&#39;parentid&#39;] == $arr[&#39;id&#39;]) {
                $list[$key][&#39;children&#39;][] = $value;
                unset($array[$k]);
            }
        }
    }
    foreach ($list as $key => $arr) {
        if (is_array($arr[&#39;children&#39;]) && count($arr[&#39;children&#39;]) > 0) {
            $list[$key][&#39;children&#39;] = cat_options($list[$key][&#39;children&#39;], $array);
        }
    }
    return $list;
}
?>

Copier après la connexion

其它的方法,设置fid字段类型为varchar,将父类id都集中在这个字段里,用符号隔开,比如:1,3,6,这样可以比较容易得到各上级分类的ID,而且在查询分类下的信息的时候,可以使用:SELECT * FROM category WHERE pid LIKE "1,3%",代码如下:

--  
-- 表的结构 `category`  
--  
CREATE TABLE IF NOT EXISTS `category` (  
`id` int(11) NOT NULL AUTO_INCREMENT,  
`type` int(11) NOT NULL COMMENT &#39;1为文章类型2为产品类型3为下载类型&#39;,  
`title` varchar(50) NOT NULL,  
`lft` int(11) NOT NULL,  
`rgt` int(11) NOT NULL,  
`lorder` int(11) NOT NULL COMMENT &#39;排序&#39;,  
`create_time` int(11) NOT NULL,  
PRIMARY KEY (`id`)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;  
--  
-- 导出表中的数据 `category`  
--  
INSERT INTO `category` (`id`, `type`, `title`, `lft`, `rgt`, `lorder`, `create_time`) VALUES  
(1, 1, &#39;顶级栏目&#39;, 1, 18, 1, 1261964806),  
(2, 1, &#39;公司简介&#39;, 14, 17, 50, 1264586212),  
(3, 1, &#39;新闻&#39;, 12, 13, 50, 1264586226),  
(4, 2, &#39;公司产品&#39;, 10, 11, 50, 1264586249),  
(5, 1, &#39;荣誉资质&#39;, 8, 9, 50, 1264586270),  
(6, 3, &#39;资料下载&#39;, 6, 7, 50, 1264586295),  
(7, 1, &#39;人才招聘&#39;, 4, 5, 50, 1264586314),  
(8, 1, &#39;留言板&#39;, 2, 3, 50, 1264586884),  
(9, 1, &#39;总裁&#39;, 15, 16, 50, 1267771951);

Copier après la connexion

<?php
/**  
 * 显示树,把所有的节点都显示出来。
 * 1、先得到根结点的左右值（默认根节点的title为"顶级目录"）。
 * 2、查询左右值在根节点的左右值范围内的记录，并且根据左值排序。
 * 3、如果本次记录右值大于前次记录的右值则为子分类，输出时候加空格。
 * @return array
 *
 */
function display_tree() {
    //获得root左边和右边的值
    $arr_lr = $this->category->where("title = &#39;顶级栏目&#39;")->find();
    //print_r($arr_lr);
    if ($arr_lr) {
        $right = array();
        $arr_tree = $this->category->query("SELECT id, type, title, rgt FROM category WHERE lft >= " . $arr_lr[&#39;lft&#39;] . " AND lft <=" . $arr_lr[&#39;rgt&#39;] . " ORDER BY lft");
        foreach ($arr_tree as $v) {
            if (count($right)) {
                while ($right[count($right) - 1] < $v[&#39;rgt&#39;]) {
                    array_pop($right);
                }
            }
            $title = $v[&#39;title&#39;];
            if (count($right)) {
                $title = &#39;|-&#39; . $title;
            }
            $arr_list[] = array(
                &#39;id&#39; => $v[&#39;id&#39;],
                &#39;type&#39; => $type,
                &#39;title&#39; => str_repeat(&#39; &#39;, count($right)) . $title,
                &#39;name&#39; => $v[&#39;title&#39;]
            );
            $right[] = $v[&#39;rgt&#39;];
        }
        return $arr_list;
    }
}
?>

Copier après la connexion

好了只要这样所有的分类都可以一次性查询出来了,而不用通过递归了.

永久链接：

转载随意！带上文章地址吧。