php编程——sql查询（条件拼接）

linuxx

function GetProducts($catagorys, $includeSubcatagory, $brandname='', $specialoffer, $pagesize, $pageindex, $orderby){
$where = array(1);
foreach($catagorys as $k=>$v){
$catIDsql='SELECT cat_id FROM sdb_goods_cat WHERE cat_name=\''.$v.'\' ';
$cat_rs[]=$this->db->selectrow($catIDsql);
}
foreach($cat_rs as $k=>$v){
$cat_ids[] = $v['cat_id'];
}
if($includeSubcatagory){
$where[] = ' AND c.parent_id in('.implode(',',$cat_ids).') OR c.cat_id in ('.implode(',',$cat_ids).') ';
}else{
$where[] = ' AND c.cat_id in ('.implode(',',$cat_ids).') ';
}
if($brandname){
$where[] = ' AND g.brand = \''.$brandname.'\' ';
}
if($specialoffer){
$where[] = ' AND g.iflimit = 1 OR g.is_groupbuy = \'true\' ';
}
switch($orderby){
case 0:
$ob = ' g.uptime DESC';
break;
case 1:
$ob = ' g.uptime ';
break;
case 2:
$ob = ' p.price DESC';
break;
case 3:
$ob = ' p.price ';
break;
case 4:
$ob = ' g.buy_count DESC';
break;
case 5:
$ob = ' g.buy_count ';
break;
case 6:
$ob = ' g.score DESC ';
break;
case 7:
$ob = ' g.score ';
break;
}
$sql='SELECT p.product_id,p.bn,p.name,g.cat_id,c.cat_name,p.price,p.mktprice,g.buy_count,g.score,g.uptime,g.thumbnail_pic,g.small_pic,g.big_pic,g.intro,g.params,g.supplier_id,g.score,g.last_modify,g.buy_count,g.store FROM sdb_goods as g,sdb_goods_cat as c,sdb_products as p WHERE g.goods_id=p.goods_id AND g.cat_id = c.cat_id AND 1='.implode(' ',$where).' ORDER BY '.$ob.' limit '.$pagesize*($pageindex-1).','.$pagesize*$pageindex;
$result=$this->db->select($sql);
if(!$result){
$result[0]=array();
}
return $result;
}