PHP统计价格区间商品数,【php】统计goods_category表中各分类下商品的总数量

class sitemap_model extends Abstract_Model {

/**

* 获得指定分类同级的所有分类以及该分类下的子分类

*

* @access public

* @param integer $cat_id 分类编号

* @return array

*/

function get_cat_tree($cat_id = 0){

$tbl_category = TBL_PRE . "goods_category";//商品分类表

$parent_id = 0;//默认情况下 父级分类id为0

/*

判断当前分类中是否是底级分类，

如果是，则取出底级分类上级分类，

如果不是，则取当前分类及其下的子分类

*/

$sql = "SELECT count(*) FROM $tbl_category WHERE category_pid = $parent_id and is_nav = 1";//统计商品分类表中在导航栏显示的顶级分类的数量

if ($this->db->get_one($sql) || $parent_id == 0){//数量大于0的情况

/* 获取当前分类及其子分类 */

$sql = "SELECT category_id,category_name ,category_pid FROM $tbl_category WHERE category_pid = '$parent_id' and is_nav = 1";//统计商品分类表中在导航栏显示的顶级分类的信息

$res = $this->db->get_all($sql);//获取顶级分类的信息，如：女装、男装、配饰

foreach ($res AS $row){//循环顶级分类

$cat_arr[$row['category_id']]['id'] = $row['category_id'];//顶级分类id

$cat_arr[$row['category_id']]['name'] = $row['category_name'];//顶级分类名称

$cat_arr[$row['category_id']]['pid'] = $row['category_pid'];//顶级分类父id

if (isset($row['category_id']) != NULL){//如果当前顶级分类id存在，且不为空的话

$tmparray = $this->get_children($row['category_id']);//获取当前顶级分类下所有一级分类id拼接成的sql，如果含有二级子分类，则也会将二级子分类id拼接进来，如："g.category_id in (1,2,3,4,5)'，这是只是一个拼接的sql语句，真正的统计数量，则是在下面这行代码

$cat_arr[$row['category_id']]['num'] = $this->get_cat_num($tmparray);//获取当前顶级分类下的商品数量

$cat_arr[$row['category_id']]['cat_id'] = $this->get_child_tree($row['category_id']);//获得一级子分类及二级子分类下的商品数量

}

}

}

if(isset($cat_arr)){

return $cat_arr;

}

}

//获取子分类树

function get_child_tree($tree_id = 0){//获取顶级分类下的一级子分类

$tbl_category = TBL_PRE . "goods_category";

$three_arr = array();

$sql = "SELECT count(*) FROM $tbl_category WHERE category_pid = $tree_id and is_nav = 1";//当前顶级分类下的一级子分类数量

if ($this->db->get_one($sql) || $tree_id == 0){

$child_sql = "SELECT category_id,category_name ,category_pid FROM $tbl_category WHERE category_pid = $tree_id and is_nav = 1";

$res = $this->db->get_all($child_sql);

foreach ($res AS $row){

$three_arr[$row['category_id']]['id'] = $row['category_id'];//一级子分类id

$three_arr[$row['category_id']]['name'] = $row['category_name'];//一级子分类名称

$three_arr[$row['category_id']]['pid'] = $row['category_pid'];//一级子分类pid

if (isset($row['category_id']) != NULL){

$tmparray = $this->get_children($row['category_id']);//获取当前一级子分类及其下所有二级子分类ids拼接成的sql

$three_arr[$row['category_id']]['num'] = $this->get_cat_num($tmparray);//获取当前一级子分类及其下所有二级子分类的商品总数量

$three_arr[$row['category_id']]['cat_id'] = $this->get_child_tree($row['category_id']);//递归获取当前一级子分类下的二级子分类

}

}

}

return $three_arr;

}

/**

* 获得指定分类下的所有子分类的ID

* @access public

* @param integer $cat 指定分类的ID

* @return string

*/

function get_children($cat){//pid

$sql = "SELECT category_id FROM ".TBL_PRE."goods_category where category_pid=$cat and is_nav = 1";//顶级分类下的一级子分类ids

$res = $this->db->get_all($sql);

$arr = array();

foreach($res as $val){

$sql = "SELECT count(*) as num FROM ".TBL_PRE."goods_category where category_pid=$val[category_id] and is_nav = 1";//顶级分类下的一级子分类ids，weiyanhui

if($this->db->get_one($sql) > 0){//适用于含有二级子分类的情况

$arr[]=$val['category_id'];//当前一级子分类id；因为一级分类下也可能含有商品，所以必须将此一级分类id存入数组中，如：美妆/护肤

$sql = "SELECT category_id FROM gt_goods_category where category_pid='$val[category_id]' and is_nav = 1";//一级子分类下的二级子分类ids

$res = $this->db->get_all($sql);

foreach($res as $val){

$arr[]=$val['category_id'];//一级子分类下的所有二级子分类ids，如：美妆/护肤/化妆水

}

}else{//适用于只有一级子分类的情况

$arr[]=$val['category_id'];//当前一级子分类ids，如：配饰/腰带

}

}

$arr[]=$cat;

$arr = array_unique($arr);//去除重复

$item_list_tmp = '';

foreach ($arr AS $item){

if ($item !== ''){

$item_list_tmp .= $item_list_tmp ? ",'$item'" : "'$item'";//拼接sql

}

}

return "g.category_id" . ' IN (' . $item_list_tmp . ') ';//如：g.category_id in (1,2,3)

}

/**

* 获得对应分类下所有商品数量(包含自分类数量)

*

* @access public

* @param array$children

* @return $total

*/

function get_cat_num($children){//统计当前分类下的商品数量(顶级分类、一级分类、二级分类、等等)

$tbl_product = TBL_PRE . "products";

$tbl_goods = TBL_PRE . "goods";

$sql ="SELECT count(g.goods_id) FROM $tbl_goods AS g, $tbl_product AS p WHERE $children AND p.goods_id = g.goods_id AND p.is_check = 1 AND p.is_stop = 0 AND p.status = 1";

return $this->db->get_one( $sql);

}

}

?>

二、如图所示：

三、生成xml文件：

$get_cats = $sitemap_mod->get_cat_tree(0);

//百度商品搜索分类xml,weiyanhui:2013-01-30

$cat = '<?xml version="1.0" encoding="UTF-8"?>';

$cat .= '';

//百度商品搜索分类xml

foreach($get_cats as $key => $v)

{

$cat .= '';

$cat .= ''.$v['id'].'';

$cat .= ''.$v['name'].'' ;

$cat .= ''.$v['pid'].'';

$cat .= ''.$v['num'].'';

$cat .= ' ';

if(!empty($v['cat_id'])){

foreach($v['cat_id'] as $key=>$v){

$cat .= '';

$cat .= ''.$v['id'].'';

$cat .= ''.$v['name'].'' ;

$cat .= ''.$v['pid'].'';

$cat .= ''.$v['num'].'';

$cat .= ' ';

if(!empty($v['cat_id'])){

foreach($v['cat_id'] as $key=>$v){

$cat .= '';

$cat .= ''.$v['id'].'';

$cat .= ''.$v['name'].'' ;

$cat .= ''.$v['pid'].'';

$cat .= ''.$v['num'].'';

$cat .= ' ';

}

}

}

}

}

$cat .= '';

file_put_contents(WWW_PATH.'/sitemap/cat.xml', $cat);

$name['cat'] = 'sitemap/cat.xml';