数据库表记录结构
<?php
$sql = "SELECT c.cat_id, c.cat_name, c.measure_unit, c.parent_id, c.is_show,
c.show_in_nav, c.grade, c.sort_order, COUNT(s.cat_id) AS has_children
FROM ecs_category AS c
LEFT JOIN ecs_category AS s ON s.parent_id=c.cat_id
GROUP BY c.cat_id
ORDER BY c.parent_id, c.sort_order ASC";
//获取以上的数组
$res = '以上的数组';
/*
$res =
Array
(
[0] => Array
(
[cat_id] => 1
[cat_name] => 手机类型
[measure_unit] =>
[parent_id] => 0
[is_show] => 1
[show_in_nav] => 0
[grade] => 5
[sort_order] => 50
[has_children] => 4
) [1] => Array
(
[cat_id] => 18
[cat_name] => 圭亚那
[measure_unit] =>
[parent_id] => 0
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
) [2] => Array
(
[cat_id] => 12
[cat_name] => 充值卡
[measure_unit] =>
[parent_id] => 0
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 3
) [3] => Array
(
[cat_id] => 16
[cat_name] => 服装
[measure_unit] => 件
[parent_id] => 0
[is_show] => 1
[show_in_nav] => 0
[grade] => 8
[sort_order] => 50
[has_children] => 0
) [4] => Array
(
[cat_id] => 6
[cat_name] => 手机配件
[measure_unit] =>
[parent_id] => 0
[is_show] => 1
[show_in_nav] => 1
[grade] => 0
[sort_order] => 50
[has_children] => 4
) [5] => Array
(
[cat_id] => 17
[cat_name] => 食品
[measure_unit] => 斤
[parent_id] => 0
[is_show] => 1
[show_in_nav] => 0
[grade] => 5
[sort_order] => 50
[has_children] => 0
) [6] => Array
(
[cat_id] => 5
[cat_name] => 双模手机
[measure_unit] =>
[parent_id] => 1
[is_show] => 1
[show_in_nav] => 0
[grade] => 5
[sort_order] => 50
[has_children] => 0
) [7] => Array
(
[cat_id] => 2
[cat_name] => CDMA手机
[measure_unit] =>
[parent_id] => 1
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
) [8] => Array
(
[cat_id] => 3
[cat_name] => GSM手机
[measure_unit] => 台
[parent_id] => 1
[is_show] => 1
[show_in_nav] => 1
[grade] => 4
[sort_order] => 50
[has_children] => 1
) [9] => Array
(
[cat_id] => 4
[cat_name] => 3G手机
[measure_unit] =>
[parent_id] => 1
[is_show] => 1
[show_in_nav] => 1
[grade] => 0
[sort_order] => 50
[has_children] => 0
) [10] => Array
(
[cat_id] => 19
[cat_name] => 模型机
[measure_unit] =>
[parent_id] => 3
[is_show] => 1
[show_in_nav] => 0
[grade] => 3
[sort_order] => 50
[has_children] => 2
) [11] => Array
(
[cat_id] => 8
[cat_name] => 耳机
[measure_unit] =>
[parent_id] => 6
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
) [12] => Array
(
[cat_id] => 9
[cat_name] => 电池
[measure_unit] =>
[parent_id] => 6
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
) [13] => Array
(
[cat_id] => 11
[cat_name] => 读卡器和内存卡
[measure_unit] =>
[parent_id] => 6
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
) [14] => Array
(
[cat_id] => 7
[cat_name] => 充电器
[measure_unit] =>
[parent_id] => 6
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
) [15] => Array
(
[cat_id] => 15
[cat_name] => 联通手机充值卡
[measure_unit] =>
[parent_id] => 12
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
) [16] => Array
(
[cat_id] => 13
[cat_name] => 小灵通/固话充值卡
[measure_unit] =>
[parent_id] => 12
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
) [17] => Array
(
[cat_id] => 14
[cat_name] => 移动手机充值卡
[measure_unit] =>
[parent_id] => 12
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
) [18] => Array
(
[cat_id] => 20
[cat_name] => 金属机
[measure_unit] => 台
[parent_id] => 19
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 1
) [19] => Array
(
[cat_id] => 21
[cat_name] => 塑料机
[measure_unit] => 台
[parent_id] => 19
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 1
) [20] => Array
(
[cat_id] => 23
[cat_name] => 诺基亚6300
[measure_unit] =>
[parent_id] => 20
[is_show] => 1
[show_in_nav] => 0
[grade] => 1
[sort_order] => 50
[has_children] => 0
) [21] => Array
(
[cat_id] => 22
[cat_name] => 三星
[measure_unit] =>
[parent_id] => 21
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
) )
*/ /*
优化成等级的分层数组，以 level 决定是第几层缩进,
$cat_id为指定的分类,$res为上面的数组分组
*/
$options = cat_options($cat_id, $res);
function cat_options($spec_cat_id, $arr){
static $cat_options = array();
if (!isset($cat_options[0]))
{
$level = $last_cat_id = 0;
$options = $cat_id_array = $level_array = array();
if ($data === false)
{
while (!empty($arr))
{
foreach ($arr AS $key => $value)
{
$cat_id = $value['cat_id'];
if ($level == 0 && $last_cat_id == 0)
{
if ($value['parent_id'] > 0)
{ // 非父类
break;
}
$options[$cat_id] = $value;
$options[$cat_id]['level'] = $level;
$options[$cat_id]['id'] = $cat_id;
$options[$cat_id]['name'] = $value['cat_name'];
unset($arr[$key]);
if ($value['has_children'] == 0)
{
continue;
}
$last_cat_id = $cat_id;
$cat_id_array = array($cat_id);
$level_array[$last_cat_id] = ++$level;
continue;
} // end if ($level == 0 && $last_cat_id == 0)
if ($value['parent_id'] == $last_cat_id)
{
$options[$cat_id] = $value;
$options[$cat_id]['level'] = $level;
$options[$cat_id]['id'] = $cat_id;
$options[$cat_id]['name'] = $value['cat_name'];
unset($arr[$key]);
if ($value['has_children'] > 0)
{
/*
判断是否是有跨级的，如
$array = array(1=>'',
2=>'',
3=>'',
4=>''
5=>'')
$array[3],$array[5]之间，由于在array[4]时，此时为末节点
此时会由
$last_cat_id = array_pop($cat_id_array);
所以会导致 array[3]独立出来,这样就必须加上
*/
if (end($cat_id_array) != $last_cat_id)
{
$cat_id_array[] = $last_cat_id;
}
$last_cat_id = $cat_id;
$cat_id_array[] = $cat_id;
$level_array[$last_cat_id] = ++$level;
}
} // eof if ($value['parent_id'] == $last_cat_id)
elseif ($value['parent_id'] > $last_cat_id)
{
break;
} // eof if ($value['parent_id'] == $last_cat_id)
}
// end foreach ------------
$count = count($cat_id_array);
if ($count > 1)
{
$last_cat_id = array_pop($cat_id_array);
}
elseif ($count == 1)
{ //由于上一级的原因，会导致 $last_cat_id是子节点
if ($last_cat_id != end($cat_id_array))
{
$last_cat_id = end($cat_id_array);
}
else
{
$level = 0;
$last_cat_id = 0;
$cat_id_array = array();
continue;
}
}
if ($last_cat_id && isset($level_array[$last_cat_id]))
{
$level = $level_array[$last_cat_id];
}
else
{
$level = 0;
}
}
// end while ------------
//如果数组过大，不采用静态缓存方式
if (count($options) <= 2000)
{
write_static_cache('cat_option_static', $options);
}
}
else
{
$options = $data;
}
$cat_options[0] = $options;
}
else
{
$options = $cat_options[0];
}
if (!$spec_cat_id)
{
return $options;
}
else
{
if (empty($options[$spec_cat_id]))
{
return array();
}
$spec_cat_id_level = $options[$spec_cat_id]['level'];
foreach ($options AS $key => $value)
{
if ($key != $spec_cat_id)
{
unset($options[$key]);
}
else
{
break;
}
}
$spec_cat_id_array = array();
foreach ($options AS $key => $value)
{
if (($spec_cat_id_level == $value['level'] && $value['cat_id'] != $spec_cat_id) ||
($spec_cat_id_level > $value['level']))
{
break;
}
else
{
$spec_cat_id_array[$key] = $value;
}
}
$cat_options[$spec_cat_id] = $spec_cat_id_array;
return $spec_cat_id_array;
}
} /*
$options 结果为
Array
(
[1] => Array
(
[cat_id] => 1
[cat_name] => 手机类型
[measure_unit] =>
[parent_id] => 0
[is_show] => 1
[show_in_nav] => 0
[grade] => 5
[sort_order] => 50
[has_children] => 4
[goods_num] => 0
[level] => 0
[id] => 1
[name] => 手机类型
[url] => category.php?id=1
) [5] => Array
(
[cat_id] => 5
[cat_name] => 双模手机
[measure_unit] =>
[parent_id] => 1
[is_show] => 1
[show_in_nav] => 0
[grade] => 5
[sort_order] => 50
[has_children] => 0
[goods_num] => 2
[level] => 1
[id] => 5
[name] => 双模手机
[url] => category.php?id=5
) [2] => Array
(
[cat_id] => 2
[cat_name] => CDMA手机
[measure_unit] =>
[parent_id] => 1
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
[goods_num] => 0
[level] => 1
[id] => 2
[name] => CDMA手机
[url] => category.php?id=2
) [3] => Array
(
[cat_id] => 3
[cat_name] => GSM手机
[measure_unit] => 台
[parent_id] => 1
[is_show] => 1
[show_in_nav] => 1
[grade] => 4
[sort_order] => 50
[has_children] => 1
[goods_num] => 13
[level] => 1
[id] => 3
[name] => GSM手机
[url] => category.php?id=3
) [19] => Array
(
[cat_id] => 19
[cat_name] => 模型机
[measure_unit] =>
[parent_id] => 3
[is_show] => 1
[show_in_nav] => 0
[grade] => 3
[sort_order] => 50
[has_children] => 2
[goods_num] => 0
[level] => 2
[id] => 19
[name] => 模型机
[url] => category.php?id=19
) [20] => Array
(
[cat_id] => 20
[cat_name] => 金属机
[measure_unit] => 台
[parent_id] => 19
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 1
[goods_num] => 0
[level] => 3
[id] => 20
[name] => 金属机
[url] => category.php?id=20
) [23] => Array
(
[cat_id] => 23
[cat_name] => 诺基亚6300
[measure_unit] =>
[parent_id] => 20
[is_show] => 1
[show_in_nav] => 0
[grade] => 1
[sort_order] => 50
[has_children] => 0
[goods_num] => 2
[level] => 4
[id] => 23
[name] => 诺基亚6300
[url] => category.php?id=23
) [21] => Array
(
[cat_id] => 21
[cat_name] => 塑料机
[measure_unit] => 台
[parent_id] => 19
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 1
[goods_num] => 0
[level] => 3
[id] => 21
[name] => 塑料机
[url] => category.php?id=21
) [22] => Array
(
[cat_id] => 22
[cat_name] => 三星
[measure_unit] =>
[parent_id] => 21
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
[goods_num] => 0
[level] => 4
[id] => 22
[name] => 三星
[url] => category.php?id=22
) [4] => Array
(
[cat_id] => 4
[cat_name] => 3G手机
[measure_unit] =>
[parent_id] => 1
[is_show] => 1
[show_in_nav] => 1
[grade] => 0
[sort_order] => 50
[has_children] => 0
[goods_num] => 3
[level] => 1
[id] => 4
[name] => 3G手机
[url] => category.php?id=4
) [18] => Array
(
[cat_id] => 18
[cat_name] => 圭亚那
[measure_unit] =>
[parent_id] => 0
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
[goods_num] => 1
[level] => 0
[id] => 18
[name] => 圭亚那
[url] => category.php?id=18
) [12] => Array
(
[cat_id] => 12
[cat_name] => 充值卡
[measure_unit] =>
[parent_id] => 0
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 3
[goods_num] => 0
[level] => 0
[id] => 12
[name] => 充值卡
[url] => category.php?id=12
) [15] => Array
(
[cat_id] => 15
[cat_name] => 联通手机充值卡
[measure_unit] =>
[parent_id] => 12
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
[goods_num] => 2
[level] => 1
[id] => 15
[name] => 联通手机充值卡
[url] => category.php?id=15
) [13] => Array
(
[cat_id] => 13
[cat_name] => 小灵通/固话充值卡
[measure_unit] =>
[parent_id] => 12
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
[goods_num] => 2
[level] => 1
[id] => 13
[name] => 小灵通/固话充值卡
[url] => category.php?id=13
) [14] => Array
(
[cat_id] => 14
[cat_name] => 移动手机充值卡
[measure_unit] =>
[parent_id] => 12
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
[goods_num] => 2
[level] => 1
[id] => 14
[name] => 移动手机充值卡
[url] => category.php?id=14
) [16] => Array
(
[cat_id] => 16
[cat_name] => 服装
[measure_unit] => 件
[parent_id] => 0
[is_show] => 1
[show_in_nav] => 0
[grade] => 8
[sort_order] => 50
[has_children] => 0
[goods_num] => 1
[level] => 0
[id] => 16
[name] => 服装
[url] => category.php?id=16
) [6] => Array
(
[cat_id] => 6
[cat_name] => 手机配件
[measure_unit] =>
[parent_id] => 0
[is_show] => 1
[show_in_nav] => 1
[grade] => 0
[sort_order] => 50
[has_children] => 4
[goods_num] => 0
[level] => 0
[id] => 6
[name] => 手机配件
[url] => category.php?id=6
) [8] => Array
(
[cat_id] => 8
[cat_name] => 耳机
[measure_unit] =>
[parent_id] => 6
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
[goods_num] => 3
[level] => 1
[id] => 8
[name] => 耳机
[url] => category.php?id=8
) [9] => Array
(
[cat_id] => 9
[cat_name] => 电池
[measure_unit] =>
[parent_id] => 6
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
[goods_num] => 0
[level] => 1
[id] => 9
[name] => 电池
[url] => category.php?id=9
) [11] => Array
(
[cat_id] => 11
[cat_name] => 读卡器和内存卡
[measure_unit] =>
[parent_id] => 6
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
[goods_num] => 2
[level] => 1
[id] => 11
[name] => 读卡器和内存卡
[url] => category.php?id=11
) [7] => Array
(
[cat_id] => 7
[cat_name] => 充电器
[measure_unit] =>
[parent_id] => 6
[is_show] => 1
[show_in_nav] => 0
[grade] => 0
[sort_order] => 50
[has_children] => 0
[goods_num] => 0
[level] => 1
[id] => 7
[name] => 充电器
[url] => category.php?id=7
) [17] => Array
(
[cat_id] => 17
[cat_name] => 食品
[measure_unit] => 斤
[parent_id] => 0
[is_show] => 1
[show_in_nav] => 0
[grade] => 5
[sort_order] => 50
[has_children] => 0
[goods_num] => 0
[level] => 0
[id] => 17
[name] => 食品
[url] => category.php?id=17
) ) */
// 结果如下图
