【php】统计goods_category表中各分类下商品的总数量

<?php

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 .= '<cats>';
    //百度商品搜索分类xml
	foreach($get_cats as $key => $v)
    {
    	$cat .= '<cat>';
    	$cat .= '<cid>'.$v['id'].'</cid>';
    	$cat .= '<name>'.$v['name'].'</name>' ;
    	$cat .= '<pid>'.$v['pid'].'</pid>';
    	$cat .= '<number>'.$v['num'].'</number>';
		$cat .= ' </cat>';
		if(!empty($v['cat_id'])){
			foreach($v['cat_id'] as $key=>$v){
				$cat .= '<cat>';
				$cat .= '<cid>'.$v['id'].'</cid>';
				$cat .= '<name>'.$v['name'].'</name>' ;
				$cat .= '<pid>'.$v['pid'].'</pid>';
				$cat .= '<number>'.$v['num'].'</number>';
				$cat .= ' </cat>';
				if(!empty($v['cat_id'])){
					foreach($v['cat_id'] as $key=>$v){
						$cat .= '<cat>';
						$cat .= '<cid>'.$v['id'].'</cid>';
						$cat .= '<name>'.$v['name'].'</name>' ;
						$cat .= '<pid>'.$v['pid'].'</pid>';
						$cat .= '<number>'.$v['num'].'</number>';
						$cat .= ' </cat>';
					}
				}
			}
		}
    }
    $cat .= '</cats>';
    
    file_put_contents(WWW_PATH.'/sitemap/cat.xml', $cat);
    $name['cat'] = 'sitemap/cat.xml';