我正在尝试修改搜索路由,我有两个字段,artist&标题,例如,当我搜索Foo Fighters伪装者时,我没有得到任何结果,而如果我单独搜索,我会得到。任何帮助都将不胜感激,谢谢。
$arraySearch = explode(" ", $searchvalue);
$arrayFields = array(0 => "Artist", 1 => "Title");
$countSearch = count($arraySearch);
$a = 0;
$b = 0;
$query = "SELECT * FROM ".$table." WHERE (";
$countFields = count($arrayFields);
while ($a < $countFields)
{
while ($b < $countSearch)
{
$query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
$b++;
if ($b < $countSearch)
{
$query = $query." AND ";
}
}
$b = 0;
$a++;
if ($a < $countFields)
{
$query = $query.") OR (";
}
}
$query = $query.")";
$query_result = mysql_query($query);
echo '<h1>Your Search Results</h1>'."'n'n";
if(mysql_num_rows($query_result) < 1)
{
echo '<p>No matches found for "'.$searchvalue.'"</p>';
}
else
{
echo '<p>Search Results for "'.$searchvalue.'":</p>'."'n'n";
while($row = mysql_fetch_assoc($query_result))
{
// output
}
}
此搜索功能效率非常低。由于您在通配符%blah%
上进行搜索,因此必须执行完整的表扫描才能找到任何记录。也就是说,如果你有100万条记录,它必须读取所有100万条纪录才能找到你的搜索结果。在一个有大表的繁忙站点上,这可能会影响您的性能和数据库服务器。
如果您使用的是MyISAM引擎,我建议您在艺术家和标题列中添加一个FULLTEXT索引,然后执行如下全文搜索:
SELECT * FROM `table` WHERE
MATCH(`artist`, `title`)
AGAINST('foo fighters the pretender');
正如OP所写的,这里有一个查询,它根据搜索查询上的匹配相关性进行排序
$search = $db->escape('foo fighters the pretender');
$query = "SELECT *,
MATCH(Artist, Title) AGAINST('$search' IN BOOLEAN MODE) AS relevance
FROM table
WHERE
MATCH (Artist, Title)
AGAINST('$search' IN BOOLEAN MODE)
ORDER BY relevance DESC";
更改此项:
$query = $query." AND ";
到此:
$query = $query." OR ";
本质上你说的是SELECT... WHERE (field1 like 'foo%' AND field1 like 'bar%')
。除非两个单词是完全相同的单词,否则您永远找不到field1与这两个单词相似的行。这就像说SELECT ... WHERE id=1 AND id=2
,不会拉取任何结果,其中SELECT ... WHERE id=1 OR id=2
将拉取第1行和第2行。
关闭查询的括号后,放入var_dump($query),这样您就可以了解发生了什么。
例如:以下代码输出:string(123)"SELECT*FROM WHERE(Artist LIKE'%foo%'AND Artist LIKE'%fights%')OR(Title LIKE'%foo%'AND Title LIKE[%fights%')"
<?php
$searchvalue = 'foo fighters';
$arraySearch = explode(" ", $searchvalue);
$arrayFields = array(0 => "Artist", 1 => "Title");
$countSearch = count($arraySearch);
$a = 0;
$b = 0;
$query = "SELECT * FROM ".$table." WHERE (";
$countFields = count($arrayFields);
while ($a < $countFields)
{
while ($b < $countSearch)
{
$query = $query."$arrayFields[$a] LIKE '%$arraySearch[$b]%'";
$b++;
if ($b < $countSearch)
{
$query = $query." AND ";
}
}
$b = 0;
$a++;
if ($a < $countFields)
{
$query = $query.") OR (";
}
}
$query = $query.")";
var_dump($query);
?>