数据库表记录结构
<?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 ) ) */ // 结果如下图