搜索查询不够准确

首先,我们需要简化您的问题

您的问题可以视为简单的关键字匹配,其中最重要的结果必须与输入的所有关键字匹配.

搜索:some search text应返回包含任何这些单词的所有结果['some', 'search', 'text'],其中顶部的结果与输入的完全 匹配"some search text".

这意味着您需要创建一个聚合字段,允许根据匹配对结果进行排序.我知道这样做的唯一方法是在没有重构数据和/或代码的情况下使用MySql Case语句.

您的查询已简化

SELECT *
FROM questions
WHERE
    question LIKE '%[word1]%' OR answer LIKE '%[word1]%'
    OR question LIKE '%[word2]%' OR answer LIKE '%[word2]%'
    OR question LIKE '%[word3]%' OR answer LIKE '%[word3]%'
ORDER BY quiz_id DESC

案例[完全匹配]和排序

我们需要构建的是一个看起来有点像这样的查询:

SELECT *,
    (CASE WHEN
            question LIKE '%[full-search-query]%'
            OR answer LIKE '%[full-search-query]%'
        THEN 1 ELSE 0
    END) as fullmatch
FROM questions
WHERE
    question LIKE '%[word1]%' OR answer LIKE '%[word1]%'
    OR question LIKE '%[word2]%' OR answer LIKE '%[word2]%'
    OR question LIKE '%[word3]%' OR answer LIKE '%[word3]%'
ORDER BY fullmatch DESC, quiz_id DESC

调整你的代码

// your initial storage of the full search, before you split it on spaces
$keyword = $_GET['mainSearch'];

. . .

// build our sorting field
$sortFullMatch = "(CASE WHEN question LIKE '%".SQLite3::escapeString($keyword)."%' OR answer LIKE '%".SQLite3::escapeString($keyword)."%' THEN 1 ELSE 0 END) as fullmatch";

. . .

// adjust the query and sort
$order = " ORDER BY fullmatch DESC, quiz_id DESC ";
$sql_query = "SELECT *,". $sortFullMatch ." FROM questions WHERE ".$condition.' '.$order.' LIMIT '.$frompage.','.$perpageview;

这是做什么的？

我们在SELECT语句中添加了一个新字段fulltext.当问题或答案完全包含完整搜索时,此字段将为1,否则为0.然后只需对此字段进行排序.

至于你的突出问题,你只是替换$text在每个单词的循环中设置的问题mainSearch.因此,它只是集合中的最后一个单词.相反,你需要在这里做一个类似的循环.

你的守则

$wording = str_replace($text, "".$text."", $row['answer']);

调整

foreach($query as $text) {
    $wording = str_replace($text, "".$text."", $row['answer']);
}