如何对这段代码的结果进行排序? [英] How to sort the results of this code?
问题描述
我将创建一个搜索功能，允许用户键入问题，然后我的代码将尽可能多的单词与MySQL数据库中已经存在的问题进行匹配，并根据单词数量显示前5个结果在问题中匹配.
im creating a search feature that will allow a user to type in a question, my code will then match as many words as possible with the questions already in my MySQL database and display the top 5 results depending on the amount of words that are matched in the question.
我使用一个count()函数来计算匹配单词的数量，但是目前显示的结果显示为数据库中具有50％或更高单词匹配率的前5个结果.我希望结果首先显示为最高匹配，并针对数据库中的每个结果逐步降低结果，但仅显示前5个.
这是我的代码
I use a count() function which counts the number of matching words, however at the moment the results shown are displayed as the first 5 results in the database that have a 50% word match or more. I want the results to be shown as the highest match first and work its way down for every result in the database but only show the top 5.
Here is the code I have
<?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个数学结果，因此它忽略了完整匹配. 这是该搜索的结果.
The image below shows the what happens when I search "How to make my own website" in the search bar. I already have several questions in the database for testing which are all similar questions and one the last entry is an exact match to the question I asked, but as its currently showing them as the first 5 mathing results, it ignores the full match. Here is the results from that search.
我添加了一些代码，它们显示每个问题中有多少个单词匹配，以便您可以更好地看到它.这也是一个巧合，它以升序显示数据库中的前5个匹配结果.
I have added a bit of code which shows how many word matches there are in each question just so you can see it working a bit better. Also its a coincidence that its in ascending order, it is showing the first 5 matching results in the database.
我需要添加什么代码来排列它，以便它首先显示整个数据库中最接近的匹配，然后显示第二个最佳匹配，再显示第三个等等...
What code do I need to add to this to arrange it so that it shows the closest match from the entire database first then the second best match, third etc...?
推荐答案
您可以使用嵌套的SQL查询，在其中可以按计数进行排序或将值首先加载到php数组中，然后对数组进行排序.使用SQL是非常有效的&&更快.
Either you use a nested SQL query where you can order by count or load the values to array php array first and then sort the array. Using SQL is very efficient && faster.
select column1, column2,..., LENGTH(titlecolumn) - LENGTH(REPLACE(titlecolumn, '$search term', '')) AS nummatchwords from posts where " . implode(' OR ', $array) order by nummatchwords;
这篇关于如何对这段代码的结果进行排序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
