這個問題主要存在于商品的複雜查詢,我只是介紹一種思想:
把查詢的where語句寫在session裏面;
1、在一張表里的情況比較簡單;
2、如果查詢在多個表里發生,則從副表里搜尋出相應的product_id,再用in語句查詢,如下:
if(@$_GET['s_flavor'] != ""){
$flavor_query = RunQuery("select `product`.`id` from `product`,`p_taste` where `product`.`id`=`p_taste`.`product_id` and `p_taste`.`falvor` like '%,".$_GET['s_flavor'].",%'");
$flavor_array = array();
while($flavor_record = mysql_fetch_assoc($flavor_query)){
array_push($flavor_array,$flavor_record['id']);
}
$where_clause .= " and `product`.`id` in(".implode(',',$flavor_array).")";
}
3、有時出現像checkbox這樣的複雜查詢,可以像下麵這樣寫:
前提是id在數據庫里存著如:“,23,24,25,26,”
$categories="";
if(isset($_GET["categories"])){
$array = $_GET["categories"];
$size = count($array);
$where_clause .= " and (`product`.`categories` is null";
for($i=0; $i< $size; $i++){
$where_clause .= " or `product`.`categories` like '%,".$array[$i].",%'";
}
$where_clause .= ")";
}
4、最后說一下,程序的結構,還是蠻重要的,可以有多種查詢模式:
switch ($_GET['filtertype'])
{
case "search":
if (isset($_GET['consist']) && $_GET['consist'] == "1")
$where_clause = $_SESSION['product_where_clause'];
else
{
$where_clause = "`product`.`id`>'0' and `status_d`=1 and `status_a`=1 and `block`=0";
// cate
if (@$_GET['cate'] != "")
$where_clause .= " and `product`.`categories` like '%,".$_GET['cate'].",%'";
// type
if (@$_GET['type'] != "")
$where_clause .= " and `product`.`type` = '".$_GET['type']."'";
// keyword
if (@$_GET['keyword'] != "")
$where_clause .= " and (`product`.`name` like '%".$_GET['keyword']."%' or `product`.`refer_no` like '%".$_GET['keyword']."%')";
//right search
if(@$_GET['searchbtn'] != ''){
if (@$_GET['s_keyword'] != "")
$where_clause .= " and (`product`.`name` like '%".$_GET['s_keyword']."%' or `product`.`refer_no` like '%".$_GET['s_keyword']."%')";
if(@$_GET['s_type'] != ""){
$where_clause .= " and `product`.`type` = '".$_GET['s_type']."'";
}
if(@$_GET['s_country'] != ""){
$where_clause .= " and `product`.`country` = '".$_GET['s_country']."'";
}
if(@$_GET['s_varietal'] != ""){
$where_clause .= " and (`product`.`varietal1` = '".$_GET['s_varietal']."' or `product`.`varietal2` = '".$_GET['s_varietal']."' or `product`.`varietal3` = '".$_GET['s_varietal']."' or `product`.`varietal4` = '".$_GET['s_varietal']."' or `product`.`varietal5` = '".$_GET['s_varietal1']."')";
}
if(@$_GET['s_flavor'] != ""){
$flavor_query = RunQuery("select `product`.`id` from `product`,`p_taste` where `product`.`id`=`p_taste`.`product_id` and `p_taste`.`falvor` like '%,".$_GET['s_flavor'].",%'");
$flavor_array = array();
while($flavor_record = mysql_fetch_assoc($flavor_query)){
array_push($flavor_array,$flavor_record['id']);
}
$where_clause .= " and `product`.`id` in(".implode(',',$flavor_array).")";
}
if(@$_GET['s_award'] != ""){
$award_query = RunQuery("select `product`.`id` from `product`,`p_accolades` where `product`.`id`=`p_accolades`.`product_id` and `p_accolades`.`is_award` = '".$_GET['s_award']."'");
$award_array = array();
while($award_record = mysql_fetch_assoc($award_query)){
array_push($award_array,$award_record['id']);
}
$where_clause .= " and `product`.`id` in(".implode(',',$award_array).")";
}
if(@$_GET['s_price'] != ""){
if(@$_GET['s_price'] !=6){
$b_price = ($_GET['s_price']-1)*100;
$t_price = $_GET['s_price']*100;
$where_clause .= " and `product`.`price1` > '".$b_price."' and `product`.`price1` <= '".$t_price."'";
}else{
$b_price = ($_GET['s_price']-1)*100;
$where_clause .= " and `product`.`price1` > '".$b_price."'";
}
}
}
$_SESSION['product_where_clause'] = $where_clause;
break;
}
default: // default show all record
if (isset($_GET['consist']) && $_GET['consist'] == "1")
$where_clause = $_SESSION['product_where_clause'];
else
{
$where_clause = "`id`>'0' and `status_d`=1 and `status_a`=1 and `block`=0";
$_SESSION['product_where_clause'] = $where_clause;
}
break;
}