前阵子碰上一个关于产品多属性搜索的问题，给实现了，现在拿出来跟大家探讨探讨，有什么好建议记得留下。
首先说明下，下面的实现，都仅仅是简易版，纯属抛砖引玉而为，更强大的功能只能做相应的额外扩展才行。
本文略过分类、属性、产品的创建过程的源码解析，如下仅附上图片说明。
图一：创建分类（仅两级）
图二：创建属性
图三：创建产品（这里属性是可多选的）
下面直奔产品多属性搜索环节。
首先说明一下，本文所用的搜索程序首次加载会将库中所有的产品显示出来，然后通过搜索结果，隐藏掉不匹配的产品，显示正确的产品，从而实现搜索效果。
下面是整个搜索过程的图片展示。
图四：搜索首次加载结果
图五：父分类搜索（含子分类数据）
图六：子分类搜索
图七：分类+多属性搜索（1）
图八：分类+多属性搜索（2）
图九：分类+多属性搜索（3）
图十：分类+多属性搜索（4）
图十一：分类+多属性搜索（5）
图十二：分类+多属性搜索（6）
图十三：分类+多属性搜索（7）
搜索程序前台源码（displayProduct.php）：
View Code
1 <?php
2 require 'product.model.php';
3 ?>
4 <!DOCTYPE>
5 <html>
6 <head>
7 <title>产品展示搜索</title>
8 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
9 <meta http-equiv="Content-Language" content="zh-CN" />
10 <script type="text/javascript" src=""></script>
11 </head>
12 <body>
13 <style>
14 .product{float:left;width: 20%;}
15 body{font-size:14px;}
16 .p_price span{color:#FF0000;}
17 del{color:#C0C0FF;}
18 li{list-style:none;}
19 #searchProduct ul{clear:left;float:left;margin: 3px 0;}
20 .parentAttr{font-size:16px;font-weight:bold;float: left;margin-right: 10px;width: 100px;}
21 .searchAttr{border:1px solid #CFCFCF;height:20px;line-height:20px;float:left;cursor:pointer;margin: 0 3px;padding: 0 3px;}
22 #productList{margin: 0 auto;width: 960px;clear:left;}
23 .selectedAttr{background-color:#ABC;}
24 #searchBar{clear:left;float:left;margin-left: 40px;}
25 #categories span{margin-left:40px;}
26 #emptyProduct{display:none;}
27 </style>
28 <div id="container">
29 <!-- 显示分类 -->
30 <div id="categories">
31 <span class="parentAttr">产品分类</span>
32 <?php echo Product::getCategoryList(); ?>
33 </div>
34 <!-- 显示多属性搜索选项 -->
35 <div id="searchProduct">
36 <?php echo Product::getAttributeList(true); ?>
37 <input type="button" id="searchBar" value="搜索" />
38 </div>
39 <!-- 显示搜索结果 -->
40 <div id="productList">
41 <?php echo Product::getProductList(); ?>
42 <div id="emptyProduct">没有搜索到结果！</div>
43 </div>
44 </div>
45 <script type="text/javascript">
46 $(document).ready(function(){
47 // 搜索选项选中
48 $(".searchAttr").click(function(){
49 $(this).toggleClass('selectedAttr');
50 });
51 //分类选定触发搜索
52 $("#categoryList").change(function(){
53 var catid = $.trim($(this).val());
54 if(isNaN(catid)) return false;
55
56 $.ajax({
57 url:'displayProduct.process.php',
58 type:'POST',
59 data:{catid:catid,type:'cate'},
60 dataType:'json',
61 success:function(data){
62 if(data.status == 1){
63 $(".product").hide(); //先将所有产品隐藏，后面在通过搜索结果显示相应的产品
64 if(data.products.length == 0){ //如果搜索结果为空
65 $("#emptyProduct").show(); //显示“没有搜索到结果！”
66 }else { //否则，隐藏“没有搜索到结果！”，并逐个显示搜索结果中的产品
67 $("#emptyProduct").hide();
68 $.each(data.products,function(i){
69 $("#product-"+data.products[i]).show();
70 });
71 }
72 }else {
73 alert(data.msg);
74 }
75 },
76 error:function(msg){
77 alert(msg);
78 }
79 });
80 });
81 //搜索按钮触发搜索
82 $("#searchBar").click(function(){
83 if($(".selectedAttr").length == 0) {
84 $("#categoryList").change(); //若搜索属性为空，则仅根据分类进行搜索（清除所有选中属性的情况）
85 return false;
86 }
87
88 //进行搜索属性拼接，同级属性(OR)用','分割，不同级属性（AND）用'|'分割
89 var searchString = '';
90 var searchArray = [];
91 $("#searchProduct ul").each(function(){
92 $(".selectedAttr",$(this)).each(function(){
93 var attr = $.trim($(this).attr('attr'));
94 if(!isNaN(attr)) {
95 searchString += attr + ',';
96 searchArray.push(attr);
97 }
98 })
99 searchString = searchString.substr(0,searchString.length-1) + '|';
100 });
101 searchString = searchString.substr(0,searchString.length-1);
102
103 if(searchString == '') return false;
104
105 var catid = $.trim($("#categoryList").val());
106 if(isNaN(catid)) catid = 0;
107
108 $.ajax({
109 url:'displayProduct.process.php',
110 type:'POST',
111 data:{searchString:searchString,catid:catid,type:'attr'},
112 dataType:'json',
113 success:function(data){
114 if(data.status == 1){
115 $(".product").hide(); //先将所有产品隐藏，后面在通过搜索结果显示相应的产品
116 if(data.products.length == 0){ //如果搜索结果为空
117 $("#emptyProduct").show(); //显示“没有搜索到结果！”
118 }else { //否则，隐藏“没有搜索到结果！”，并逐个显示搜索结果中的产品
119 $("#emptyProduct").hide();
120 $.each(data.products,function(i){
121 $("#product-"+data.products[i]).show();
122 });
123 }
124 }else {
125 alert(data.msg);
126 }
127 },
128 error:function(msg){
129 alert(msg);
130 }
131 });
132 });
133 });
134 </script>
135 </body>
136 </html>
搜索程序后台源码（displayProduct.process.php）：
View Code
1 <?php
2 require 'product.model.php';
3 switch($_POST['type']){
4 case 'attr':
5 echo json_encode(Product::searchProductByAttribute(mysql_escape_string($_POST['searchString']),(int)$_POST['catid']));
6 break;
7
8 case 'cate':
9 echo json_encode(Product::searchProductByCategory((int)$_POST['catid']));
10 break;
11 default:
12 echo json_encode(array('status'=>0,'msg'=>'非法查询类型！'));
13 break;
14 }
15 exit;
搜索程序后台数据处理层（product.model.php）源码：
View Code
1 <?php
2 require '../db.php';
3 class Product{
4 public static function getCategoryList(){
5 global $db;
6 $sql = "SELECT id,name,0 AS ordering,id AS 'groupcol' FROM `ju_categories` WHERE parent=0
7 UNION
8 SELECT id,name,ordering,parent AS 'groupcol' FROM `ju_categories`
9 WHERE parent IN(
10 SELECT id FROM `ju_categories` WHERE parent=0
11 ) ORDER BY `groupcol`,`ordering`";
12 $result = mysql_query($sql,$db);
13 $categoryList = '';
14 while($row = mysql_fetch_assoc($result)){
15 if($row['id'] != $row['groupcol']) $pref = '-';
16 else $pref = '';
17 $categoryList .= '<option value="'.$row['id'].'">'.$pref.$row['name'].'</option>';
18 }
19
20 $categoryList = '<select id="categoryList"><option value="0">Root</option>' . $categoryList . '</select>';
21 return $categoryList;
22 }
23
24 public static function getAttributeList($search=false){
25 global $db;
26 $sql = "SELECT id,name,0 AS ordering,id AS 'groupcol' FROM `ju_attributes` WHERE parent=0
27 UNION
28 SELECT id,name,ordering,parent AS 'groupcol' FROM `ju_attributes`
29 WHERE parent IN(
30 SELECT id FROM `ju_attributes` WHERE parent=0
31 ) ORDER BY `groupcol`,`ordering`";
32 $result = mysql_query($sql,$db);
33 $attributeList = '';
34 if($search){
35 while($row = mysql_fetch_assoc($result)){
36 if($row['id'] == $row['groupcol']) {
37 $attributeList .= '</ul><ul><li class="parentAttr">'.$row['name'].'</li>';
38 }else {
39 $attributeList .= '<li attr="'.$row['id'].'" class="searchAttr">'.$row['name'].'</li>';
40 }
41 }
42 if(stripos($attributeList,'</ul>') === 0) $attributeList = substr($attributeList,5);
43 $attributeList .= '</ul>';
44 }else {
45 while($row = mysql_fetch_assoc($result)){
46 if($row['id'] != $row['groupcol']) {
47 $attributeList .= '<option value="'.$row['id'].'">-'.$row['name'].'</option>';
48 }
49 else {
50 $attributeList .= '<option value="'.$row['id'].'" class="parentAttr">'.$row['name'].'</option>';
51 }
52 }
53
54 $attributeList = '<select id="attributeList"><option value="0" class="root">-请选择添加-</option>' . $attributeList . '</select>';
55 }
56 return $attributeList;
57 }
58
59 public static function save($data = array()){
60 global $db;
61 $name = mysql_escape_string($data['name']);
62 $sku = mysql_escape_string($data['sku']);
63 $catid = (int)$data['catid'];
64 $origPrice = mysql_escape_string($data['origPrice']);
65 $price = mysql_escape_string($data['price']);
66 $stock = mysql_escape_string($data['stock']);
67 $attrs = implode(',',(array)$data['attrs']);
68
69 if(empty($name)) {
70 echo '分类名不能为空！';
71 exit;
72 }
73
74 $sql = "INSERT INTO `ju_products`(`id`,`name`,`sku`,`catid`,`origPrice`,`price`,`stock`,`attributes`,`created_on`)"
75 ." VALUES(null,'$name','$sku','$catid','$origPrice','$price','$stock','$attrs',now())";
76 if(mysql_query($sql,$db)){
77 $productId = mysql_insert_id($db);
78 $sql = "INSERT INTO `ju_product_attributes`(`product_id`,`attribute_id`) VALUES";
79 foreach($data['attrs'] as $attr){
80 $sql .="('$productId','$attr'),";
81 }
82 $sql = rtrim($sql,',');
83 mysql_query($sql,$db);
84 return true;
85 }else {
86 return false;
87 }
88
89 }
90
91 public static function getProductList(){
92 global $db;
93 $productTpl = <<<TPL
94 <div class="product" id="product-%d">
95 <div class="p_image"><img src="%s" alt="%s" width="150" height="200"/></div>
96 <div class="p_title">%s</div>
97 <div class="p_price">
98 <span>￥%.2f</span>
99 <del>￥%.2f</del>
100 </div>
101 </div>
102 TPL;
103 $sql = "SELECT id,name,price,origPrice FROM `ju_products` ORDER BY id";
104 $result = mysql_query($sql,$db);
105 $productList = '';
106 while($row = mysql_fetch_assoc($result)){
107 $productList .= vsprintf($productTpl,array($row['id'],'#',$row['name'],$row['name'],$row['price'],$row['origPrice']));
108 }
109 return $productList;
110 }
111
112 public static function searchProductByCategory($catid){
113 global $db;
114 if(!isset($catid)) return array('status'=>0,'msg'=>'分类不能为空！');
115
116 $categories = self::getSubCategories($catid); //默认递归包含子分类
117 $sql = "SELECT id FROM `ju_products` WHERE catid IN(".implode(',',$categories).") ORDER BY id";
118 $result = mysql_query($sql,$db);
119 $productArray = array();
120 while($row = mysql_fetch_assoc($result)){
121 array_push($productArray,$row['id']);
122 }
123 return array('status'=>1,'products'=>$productArray);
124 }
125 public static function searchProductByAttribute($searchString,$catid=0){
126 global $db;
127 if(empty($searchString)) return array('status'=>0,'msg'=>'搜索条件不能为空！');
128
129 if(empty($catid)) $where = array();
130 else $where = array("p.catid IN(".implode(',',self::getSubCategories((int)$catid)).")");
131 $ands = explode('|',$searchString);
132 foreach($ands as $and){
133 $andString = "";
134 $ors = explode(',',$and);
135 foreach($ors as $or){
136 $andString .= "LOCATE(',{$or},',pas.attribute_ids) OR ";
137 }
138 $andString = '('.substr($andString,0,strlen($andString)-4).')'; //-4去掉末尾“ OR ”
139 $where[] = $andString;
140 }
141
142 $sql = "
143 SELECT FROM `ju_products` as p
144 INNER JOIN (
145 SELECT product_id,concat(',,',group_concat(attribute_id),',,') as attribute_ids FROM `ju_product_attributes` GROUP BY product_id
146 ) as pas ON =pas.product_id
147 WHERE ".implode(' AND ',$where)."
148 group by
149 ";
150 $result = mysql_query($sql,$db);
151 $productArray = array();
152 while($row = mysql_fetch_assoc($result)){
153 array_push($productArray,$row['id']);
154 }
155 return array('status'=>1,'products'=>$productArray,'sql'=>$sql);
156 }
157
158 public static function getSubCategories($pid,$recursive=true){
159 global $db;
160 $pid = (int)$pid;
161 $sql = "SELECT id FROM `ju_categories` as cate WHERE cate.parent=".$pid;
162 $result = mysql_query($sql,$db);
163 $subCategories = array($pid); //加入当前分类
164 if($recursive){
165 while($row = mysql_fetch_row($result)){
166 $subCategories = array_merge($subCategories,self::getSubCategories($row[0]));
167 }
168 }
169 return $subCategories;
170 }
171 }
172
173 //End_php
数据库连接文件（db.php）源码：
View Code
1 <?php
2 static $connect = null;
3 if(!isset($connect)){
4 $connect = mysql_connect("localhost","Zjmainstay","") or die('无法连接数据库！');
5 mysql_select_db("test",$connect) or die('无法连接到指定数据库！');
6 mysql_query("SET NAMES UTF8",$connect);
7
8 $db = $conn = $connect;
9 }
10
11 //End_php
重点：多属性搜索方法
1 public static function searchProductByAttribute($searchString,$catid=0){
2 global $db;
3 if(empty($searchString)) return array('status'=>0,'msg'=>'搜索条件不能为空！');
4
5 if(empty($catid)) $where = array();
6 else $where = array("p.catid IN(".implode(',',self::getSubCategories((int)$catid)).")"); //WHERE子句数组，分类搜索（getSubCategories方法默认含子分类）
7 $ands = explode('|',$searchString); //分离不同层级的属性，如品牌与价格范围
8 foreach($ands as $and){
9 $andString = "";
10 $ors = explode(',',$and); //分离同一层级的多个属性，如品牌中的"HP","华硕","联想"等
11 foreach($ors as $or){
12 $andString .= "LOCATE(',{$or},',pas.attribute_ids) OR "; //对每个属性进行LOCATE定位，定位目标为各个产品所有属性组成的属性串，格式为：,,26,33,3,21,,
13 }
14 $andString = '('.substr($andString,0,strlen($andString)-4).')'; //-4去掉末尾“ OR ”
15 $where[] = $andString; //加入WHERE子句数组中
16 }
17 //使用group_concat(attribute_id)拼接每个产品的所有属性，用于WHERE子句进行属性LOCATE搜索
18 $sql = "
19 SELECT FROM `ju_products` as p
20 INNER JOIN (
21 SELECT product_id,concat(',,',group_concat(attribute_id),',,') as attribute_ids FROM `ju_product_attributes` GROUP BY product_id
22 ) as pas ON =pas.product_id
23 WHERE ".implode(' AND ',$where)." //使用AND拼接WHERE子句数组
24 group by
25 ";
26 $result = mysql_query($sql,$db);
27 $productArray = array();
28 while($row = mysql_fetch_assoc($result)){
29 array_push($productArray,$row['id']); //将查得产品id加入$productArray数组中，响应请求。
30 }
31 return array('status'=>1,'products'=>$productArray,'sql'=>$sql);
32 }
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
