sql like模糊查询,搜索关键字时,增加搜索权重排序及关键字高亮功能

有修改升级.地址为https://blog.csdn.net/viqecel/article/details/121649477
假如搜索关键字.标题中包含.商品简介中也包含,搜索列表怎么排序呢.当然是把标题中包含关键字的优先排前面.实现方式很简单,以thinkphp3 为例
在线测试http://www.microphotons.cn/
效果演示

一,搜索控制器代码

  function search(){
        header("Content-Type:text/html; charset=utf-8");
        $ss=$this->ss;
        $soso=I('soso');
		//print_r(1);exit;
        $this->assign('soso',$soso);
        if($soso){
            $map['title|xinghao|huohao|description|des']=array('like',"%$soso%");
            //搜索标题或型号,货号,参数简介,大段描述等,注意,不区分大小写
            $map2['soso']=$soso;//用于分页时
        }
//like '$soso%' 注意,这个匹配,只包含一个%的,表示,以搜索的关键字开头的,相关性最强,得分最高,
//%$soso%表示搜索的词在标题中间或后面.得分低

//搜索字段 用sql case when 功能,判断查询,并新定义xinghaosort等排序专用字段
$field="id,iss,title,type,xinghao,huohao,description,des,ctime,pic,kucun,

((case when xinghao like '$soso%' then 100 else 0 end)+
 
(case when xinghao like '%$soso%' then 90 else 0 end)) as xinghaosort
 
,
((case when huohao like '$soso%' then 70 else 0 end)+
 
(case when huohao like '%$soso%' then 60 else 0 end)) as huohaosort
,
((case when title like '$soso%' then 400 else 0 end)+
 
(case when title like '%$soso%' then 300 else 0 end)) as titlesort

,
((case when description like '$soso%' then 19 else 0 end)+
 
(case when description like '%$soso%' then 18 else 0 end)) as descriptionsort
,
((case when des like '$soso%' then 16 else 0 end)+
 
(case when des like '%$soso%' then 15 else 0 end)) as dessort
";

        //排序
        $px='titlesort desc,xinghaosort desc,huohaosort desc,descriptionsort desc,dessort desc';
	   
       $cp_list = $this->mypage_soso('contlist',$map,$px,$num=10,$map2,$field);
      
		$cp_list2=array();//关键字高亮
		 foreach ($cp_list as $k => $v) {
        // $cp_list2[$k]['title']=str_ireplace($soso,"<font color='red'><b>{$soso}</b></font>",$v['title']);
		 
		  $cp_list2[$k]['title']= preg_replace("/($soso)/i", "<font color=red>\\1</font>", $v['title']);  
		  //用preg_replace这个函数,可以防止替换时,把原文的小写或大写被强制修改
		 $cp_list2[$k]['xinghao']= preg_replace("/($soso)/i", "<font color=red>\\1</font>", $v['xinghao']); 
		 $cp_list2[$k]['huohao']= preg_replace("/($soso)/i", "<font color=red>\\1</font>", $v['huohao']); 
		 $cp_list2[$k]['description']= preg_replace("/($soso)/i", "<font color=red>\\1</font>", $v['description']); 
		 $cp_list2[$k]['des']= preg_replace("/($soso)/i", "<font color=red>\\1</font>", $v['des']); 
		  $cp_list2[$k]['iss']=$v['iss'];
	 $cp_list2[$k]['type']=$v['type'];
		 $cp_list2[$k]['kucun']=$v['kucun'];
		 $cp_list2[$k]['id']=$v['id'];
		 $cp_list2[$k]['ctime']=$v['ctime'];
		 $cp_list2[$k]['pic']=$v['pic'];

        }
            $this->display();
    }

//分页查询函数 我是这个文件Application\Home\Controller\BaseController.class.php

  public function mypage_soso($mydb,$map,$px,$num=10,$map2,$field){
        $count      = M($mydb)->where($map)->count();
        // 查询满足要求的总记录数
        $Page       = new \Think\Page($count,$num);
        // 实例化分页类 传入总记录数和每页显示的记录数(25)
        foreach($map2 as $key=>$val) {
            $Page->parameter[$key]   =  $val;
        }
        $show       = $Page->show();
        $list = M($mydb)->where($map)->field($field)->order($px)->limit($Page->firstRow.','.$Page->listRows)->select();
        $this->assign('page',$show);
        $this->assign('count',$count);
        return $list;
    }