MySQL首先显示最接近的匹配 [英] MySQL show closest match first

问题描述

我有一个功能可以根据用户搜索在 Wordpress 类别中搜索匹配项，并且工作正常，但是我想在结果顶部显示与搜索最接近的匹配项.例如，如果用户搜索附件"，我希望它首先显示，然后在其后显示任何其他匹配项.理想情况下，它们将按相关性排序，但我不确定如何实现.

I have a function that searches Wordpress categories for a match based on users search and it works fine, however I'd like to display the closest match to the search at the top of the results. If for example a user searches "Accessories", i'd like that to display first and then any other matches displayed after it. Ideally they will be ordered by relevenacy, but I'm not sure how to achieve that.

目前，下面的代码按在查询中找到的顺序而不是按特定性显示它们.下面的代码产生以下结果.

At the moment the code below displays them in order they were found in the query rather than by specificity. The code below yields the below results.

代码返回什么

Exhaust Accessories
Accessories
Centre Stand Accessories
Rear Stand Accessories
Side Stand Accessories

我正在使用的代码

$arr = explode(' ','Accessories');
    $str = '';
    $i = 1;
    $arrCount = count($arr);

    foreach($arr as $v){
        if($arrCount > 1 && $i == 1) { $str.= '('; }
        $str.= 'wpmj8c_terms.name LIKE "%'.$v.'%" ';
          if($arrCount > 1 && $arrCount == $i) { $str.= ')'; } elseif($arrCount > 1 && $arrCount != $i) { $str .= " OR " ;}
        $i++;
    }

$cat = $wpdb->get_results("SELECT *
    FROM wpmj8c_term_relationships
    LEFT JOIN wpmj8c_term_taxonomy
    ON (wpmj8c_term_relationships.term_taxonomy_id = wpmj8c_term_taxonomy.term_taxonomy_id)
    LEFT JOIN wpmj8c_terms on wpmj8c_term_taxonomy.term_taxonomy_id = wpmj8c_terms.term_id
    WHERE wpmj8c_term_taxonomy.taxonomy = 'product_cat'  AND  $str
    GROUP BY wpmj8c_term_taxonomy.term_id");

推荐答案

可以使用like 子句搜索字符串和order by 对列进行排序

you can use like clause to search string and order by to order the column

$arr = explode(' ','Accessories');
$str = '';
$str1 = '';
$i = 1;
$arrCount = count($arr);

foreach($arr as $v){
    if($arrCount > 1 && $i == 1) { $str.= '('; }
    $str.= 'wpmj8c_terms.name LIKE "%'.$v.'%" ';
    $str1.='OR wpmj8c_terms.name LIKE "%"';
      if($arrCount > 1 && $arrCount == $i) { $str.= ')'; } elseif($arrCount > 1 && $arrCount != $i) { $str .= " OR " ;}
    $i++;
}

$cat = $wpdb->get_results("SELECT *
FROM wpmj8c_term_relationships
LEFT JOIN wpmj8c_term_taxonomy
ON (wpmj8c_term_relationships.term_taxonomy_id = wpmj8c_term_taxonomy.term_taxonomy_id)
LEFT JOIN wpmj8c_terms on wpmj8c_term_taxonomy.term_taxonomy_id = wpmj8c_terms.term_id
WHERE wpmj8c_term_taxonomy.taxonomy = 'product_cat'  AND $str $str1 
GROUP BY wpmj8c_term_taxonomy.term_id order by wpmj8c_terms.name = $str");

这篇关于MySQL首先显示最接近的匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！