php複雜查詢的問題

最新推荐文章于 2024-09-17 20:18:24 发布

wust_star

最新推荐文章于 2024-09-17 20:18:24 发布

阅读量494

点赞数

分类专栏： PHP基础技术 MySQL 文章标签： php session query mysql search

本文链接：https://blog.csdn.net/wust_star/article/details/7993081

版权

PHP基础技术同时被 2 个专栏收录

29 篇文章 0 订阅

订阅专栏

MySQL

17 篇文章 0 订阅

订阅专栏

這個問題主要存在于商品的複雜查詢，我只是介紹一種思想：

把查詢的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;
}