我正在从数据库中创建对First_name和Last_name的搜索,用户可以通过该搜索在字段中输入他想要查找的内容,我的php脚本将搜索与这些名称相似的名称。
所以我做了
$q=addslashes($_REQUEST['q']);
$q =mysqli_real_escape_string($connection, $q);
//In order to search word by word i explode the string
$notre_tableau=explode(',',$q);
//I count the string in order to search each word
$compter_tableau=count($notre_tableau);
$req_search = "SELECT first_name, last_name
FROM mytable
WHERE ";
//Now i read the array and search for each word
for ($i = 0; $i < $compter_tableau; $i++)
{
$notremotchercher=trim($notre_tableau["$i"]);
if($i==$compter_tableau) { $liaison="AND"; } else { $liaison=""; }
if($i!=0) { $debutliaison="AND"; } else { $debutliaison=""; }
//I search only if the string is not empty
if($notremotchercher!='')
{
$req_search .= "$debutliaison (first_name LIKE '%$notremotchercher%' OR last_name LIKE '%$notremotchercher%') $liaison";
}
}
//Now i proceed with my search
$requete=mysqli_query($connection, "$req_search ORDER BY id DESC LIMIT 20 ") or die(mysqli_error($connection));
所以,如果某人的名字是Jennifer Jane,姓氏是DOE,例如,如果我输入Jennifer(只有Jennifer),它就起作用了,如果我输入Jennifer Jane,它会起作用,如果我在DOE中输入,它也会起作用。
上面的代码将显示这样的内容,用于基于Jennifer 的搜索
SELECT first_name, last_name FROM mytable WHERE (first_name LIKE '%Jennifer%' OR last_name LIKE '%Jennifer%')
因此我得到一个结果
问题如果我输入Jane Jennifer,它不起作用此外,如果我在Jennifer DOE中输入,则不起作用。请知道我不能告诉用户将在First中输入什么,如何使它为Both列工作?
感谢
您可能需要在搜索中拆分单词。所以如果搜索词是"Jane doe"。。。
<?php
$search_phrase = "Jane Doe";
$terms = explode(" ",$search_phrase);
$query = "SELECT first_name, last_name from myTable WHERE ";
for($i=0;$i<count($terms);$i++){
if($i!=0)
$query.=" OR ";
$query.=" (first_name LIKE '%".$terms[$i]."%' OR last_name LIKE '%".$terms[$i]."%')";
}
echo $query; //Should produce "SELECT first_name, last_name from myTable WHERE (first_name LIKE '%Jane%' OR last_name LIKE '%Jane%') OR (first_name LIKE '%Doe%' OR last_name LIKE '%Doe%')"
?>
你可以使用上面的内容,并根据自己的需要进行taylor,比如只使用术语的第二部分搜索姓氏,等等。
您可以分解搜索字符串:
$searhStr = 'Jane Jennifer';
$searchSplits = array(' ',',','.','-','?','!',"'n");
$searchArray = explode($searchSplits,$searhStr);
然后在数组中查找每个单词:
forach ($searchArray as $searchWord)
{
$queryParts[] = "first_name LIKE '%{$searchWord}%'";
$queryParts[] = "prenom LIKE '%{$searchWord}%'";
}
$query = 'SELECT first_name,
last_name
FROM mytable
WHERE '.implode(' OR ',$queryParts);
学习新事物永远不会迟到。看看MySQL如何帮助您进行搜索:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html