MySql:在like子句中匹配的无序词 [英] MySql: unordered words matching in like clause
问题描述
如何实现名称匹配.像John S Smith"这样的东西必须匹配 mysql 数据库中的Smith John S".即单词可以是无序的.
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.
是否可以在单个 SQL 查询中执行此操作?因为是名字,所以不会超过3个字.
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%'";
}
我的结果 SQL 看起来像这样.
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%'" ;
推荐答案
下面的代码会首先找到单词 & 的所有可能组合.然后将其与数据库匹配由于您的代码最多只有 3 个单词,因此这不是一个糟糕的选择
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子句中匹配的无序词的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!