我正在创建一个搜索功能,它将允许用户键入一个问题,然后我的代码将匹配尽可能多的单词与我的MySQL数据库中已经存在的问题,并显示前5个结果取决于在问题中匹配的单词数量。
我使用了一个count()函数,该函数计算匹配单词的数量,但是目前显示的结果显示为数据库中匹配50%或更多的前5个结果。我希望结果首先显示为最高匹配,然后向下显示数据库中的每个结果,但只显示前5个。
<?php
include("config.php");
$search_term = filter_var($_GET["s"], FILTER_SANITIZE_STRING); //User enetered data
$search_term = str_replace ("?", "", $search_term); //remove any question marks from string
$search_count = str_word_count($search_term); //count words of string entered by user
$array = explode(" ", $search_term); //Seperate user enterd data
foreach ($array as $key=>$word) {
$array[$key] = " title LIKE '%".$word."%' "; //creates condition for MySQL query
}
$q = "SELECT * FROM posts WHERE " . implode(' OR ', $array); //Query to select data with word matches
$r = mysql_query($q);
$count = 0; //counter to limit results shown
while($row = mysql_fetch_assoc($r)){
$thetitle = $row['title']; //result from query
$thetitle = str_replace ("?", "", $thetitle); //remove any question marks from string
$title_array[] = $thetitle; //creating array for query results
$newarray = explode(" ", $search_term); //Seperate user enterd data again
foreach($title_array as $key => $value) {
$thenewarray = explode(" ", $value); //Seperate each result from query
$wordmatch = array_diff_key($thenewarray, array_flip($newarray));
$result = array_intersect($newarray, $wordmatch);
$matchingwords = count($result); //Count the number of matching words from
//user entered data and the database query
}
if(mysql_num_rows($r)==0)//no result found
{
echo "<div id='search-status'>No result found!</div>";
}
else //result found
{
echo "<ul>";
$title = $row['title'];
$percentage = '.5'; //percentage to take of search word count
$percent = $search_count - ($search_count * $percentage); //take percentage off word count
if ($matchingwords >= $percent){
?>
<li><a href='<?php echo $row['url']; ?>'><?php echo $title ?><i> No. matching words: <?php echo $matchingwords; ?></i></a></li>
<?php
$count++;
if ($count == 5) {break;
}
}else{
}
}
echo "</ul>";
}
?>下图显示了当我在搜索栏中搜索“如何创建我自己的网站”时发生的事情。我已经在数据库中有几个问题用于测试,这些问题都是相似的问题,最后一个条目与我提出的问题完全匹配,但由于它当前显示它们作为前5个数学结果,它忽略了完全匹配。以下是搜索的结果。

我添加了一些代码,显示每个问题中有多少个单词匹配,以便您可以看到它工作得更好。这也是一个巧合,它是按升序排列的,它显示了数据库中的前5个匹配结果。
我需要向它添加什么代码来安排它,以便它首先显示整个数据库中最接近的匹配,然后是第二个最佳匹配,第三个等等?
发布于 2012-08-04 21:57:07
你可以使用一个嵌套的SQL查询,在这里你可以按计数排序,或者先将值加载到数组php array中,然后对数组进行排序。使用SQL是非常有效的&&更快。
select column1, column2,..., LENGTH(titlecolumn) - LENGTH(REPLACE(titlecolumn, '$search term', '')) AS nummatchwords from posts where " . implode(' OR ', $array) order by nummatchwords;https://stackoverflow.com/questions/11809077
复制相似问题