MySql：在like子句中匹配的无序单词

问题描述：

How can I achieve name matching. Something like "John S Smith" must match "Smith John S" in mysql database. i.e words can be unordered.

Is it possible to do this in a single SQL query? Since it is for names it will not exceed 3 words.

My code is not logically correct. Any help would be highly appreciated.

$words=explode(" ", $name);

$sql="SELECT * FROM sent WHERE 1=1";

foreach ($words as $word)
{
    $sql.=" AND customer_name LIKE '%$word%'";
}

The Resulting SQL that I have looks like this.

$sql="SELECT * FROM sent WHERE 1=1 AND customer_name LIKE '%John%' AND customer_name LIKE '%S%' AND customer_name LIKE '%Smith%'" ;

如何实现名称匹配。像“John S Smith”这样的东西必须与mysql数据库中的“Smith John S”相匹配。单词可以是无序的。 p>

是否可以在单个SQL查询中执行此操作？因为它是名字，它不会超过3个字。 p>

我的代码在逻辑上不正确。任何帮助都将受到高度赞赏。 p>

  $ words = explode（“”，$ name）; 
 
 $ sql =“SELECT * FROM sent WHERE 1 = 1”  ; 
 
foreach（$ words as $ word）
 {
 $ sql。=“AND customer_name LIKE'％$ word％'”; 
} 
  code>  pre> 
 
  我得到的结果SQL如下所示。 p> 
 
 
  $ sql =“SELECT * FROM sent WHERE 1 = 1 AND customer_name LIKE'％John％'AND customer_name LIKE  '％S％'和customer_name LIKE'％Smith％'“; 
  code>  pre> 
  div>

答

Below Code will first find all the possible combination of the words & then match it with database Since your code have only 3 words at the max , hence it is not a bad option

    <?php
    $name ='John S Smith';
    $words=explode(" ", $name);;

    function get_all_combination($arr, $temp_string, &$collect) {
        if ($temp_string != "") 
            $collect []= $temp_string;

        for ($i=0; $i<sizeof($arr);$i++) {
            $arrcopy = $arr;
            $elem = array_splice($arrcopy, $i, 1); // removes and returns the i'th element
            if (sizeof($arrcopy) > 0) {
                get_all_combination($arrcopy, $temp_string ." " . $elem[0], $collect);
            } else {
                $collect []= $temp_string. " " . $elem[0];
            }   
        }   
    }

    $collect = array();
    get_all_combination($words, "", $collect);

       /* 
        $collect now have 

        [0] =>  John
        [1] =>  John S
        [2] =>  John S Smith
        [3] =>  John Smith
        [4] =>  John Smith S
        [5] =>  S
        [6] =>  S John
        [7] =>  S John Smith
        [8] =>  S Smith
        [9] =>  S Smith John
        [10] =>  Smith
        [11] =>  Smith John
        [12] =>  Smith John S
        [13] =>  Smith S
        [14] =>  Smith S John 
        */

    $sql="SELECT * FROM sent WHERE 1=1 AND (customer_name = '".implode("' OR customer_name = '",$collect)."')" ;




    ?>

MySql：在like子句中匹配的无序单词

相关推荐