PHP实现查询多级分类的程序代码
程序员文章站
2022-05-22 17:17:44
...
无限级分类是我们常用见的一个程序方法了,原理是得到有层级关系的数组,就是*是*分类,然后每个分类中有个children子数组,记录它的子分类,这样一级一级的分级数组.
算法的数据库结构设计最为简单,category表中一个字段id,一个字段fid(父id),这样可以根据WHERE id = fid来判断上一级内容,运用递归至最顶层.
我们先查出数组,代码如下:
array( 'aaaaaa' => array( '111111', '222222', '333333' ) , 'bbbbbb' => array( '111111', '222222', '333333' ) , 'cccccc' => array( '111111', '222222', '333333' ) , ) , 'BBBBBB' => array( 'aaaaaa' => array( '111111', '222222', '333333' ) , 'bbbbbb' => array( '111111', '222222', '333333' ) , 'cccccc' => array( '111111', '222222', '333333' ) , ) , 'CCCCCC' => array( 'aaaaaa' => array( '111111', '222222', '333333' ) , 'bbbbbb' => array( '111111', '222222', '333333' ) , 'cccccc' => array( '111111', '222222', '333333' ) , ) , ); foreach ($a as $k => $v) { echo $k . "
"; // if(is_array($v)){ foreach ($v as $key => $val) { echo " " . $key . "
"; // } if (is_array($val)) { foreach ($val as $kkk => $vall) { echo " " . $vall . "
"; } } } echo "
"; } /*******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['big']] = $arr['small']; } ***/ ?>
好了下面先读取数据库然后再递归读出.
分类表,比如category,字段有 id,parentid,title,代码如下:
SetQuery("SELECT * FROM category ORDER BY sortorder ASC"); $dsql->Execute('parentlist'); $array = array(); $parentlist = array(); while ($rs = $dsql->getObject('parentlist')) { 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['parentid'] == $arr['id']) { $list[$key]['children'][] = $value; unset($array[$k]); } } } foreach ($list as $key => $arr) { if (is_array($arr['children']) && count($arr['children']) > 0) { $list[$key]['children'] = cat_options($list[$key]['children'], $array); } } return $list; } ?>
其它的方法,设置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 '1为文章类型2为产品类型3为下载类型', `title` varchar(50) NOT NULL, `lft` int(11) NOT NULL, `rgt` int(11) NOT NULL, `lorder` int(11) NOT NULL COMMENT '排序', `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, '*栏目', 1, 18, 1, 1261964806), (2, 1, '公司简介', 14, 17, 50, 1264586212), (3, 1, '新闻', 12, 13, 50, 1264586226), (4, 2, '公司产品', 10, 11, 50, 1264586249), (5, 1, '荣誉资质', 8, 9, 50, 1264586270), (6, 3, '资料下载', 6, 7, 50, 1264586295), (7, 1, '人才招聘', 4, 5, 50, 1264586314), (8, 1, '留言板', 2, 3, 50, 1264586884), (9, 1, '总裁', 15, 16, 50, 1267771951);
category->where("title = '*栏目'")->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['lft'] . " AND lft $v['id'], 'type' => $type, 'title' => str_repeat(' ', count($right)) . $title, 'name' => $v['title'] ); $right[] = $v['rgt']; } return $arr_list; } } ?>
好了 只要这样所有的分类都可以一次性查询出来了,而不用通过递归了.
永久链接:
转载随意!带上文章地址吧。