产品多属性搜索实现
前阵子碰上一个关于产品多属性搜索的问题，给实现了，现在拿出来跟大家探讨探讨，有什么好建议记得留下。
首先说明下，下面的实现，都仅仅是简易版，纯属抛砖引玉而为，更强大的功能只能做相应的额外扩展才行。
本文略过分类、属性、产品的创建过程的源码解析，如下仅附上图片说明。
图一：创建分类（仅两级）
图二：创建属性
图三：创建产品（这里属性是可多选的）
下面直奔产品多属性搜索环节。
首先说明一下，本文所用的搜索程序首次加载会将库中所有的产品显示出来，然后通过搜索结果，隐藏掉不匹配的产品，显示正确的产品，从而实现搜索效果。
下面是整个搜索过程的图片展示。
图四：搜索首次加载结果
图五：父分类搜索（含子分类数据）
图六：子分类搜索
图七：分类+多属性搜索（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="https://files.cnblogs.com/Zjmainstay/jquery-1.6.2.min.js"></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 p.id 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 p.id=pas.product_id 147 WHERE ".implode(' AND ',$where)." 148 group by p.id 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 p.id 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 p.id=pas.product_id 23 WHERE ".implode(' AND ',$where)." //使用AND拼接WHERE子句数组 24 group by p.id 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 }
本文到此结束，谢谢大家耐心阅读！
