MySQL show closest match first

Question

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.

What is returned by the code

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

The code I'm using

$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");

Answer 1

Hello_

I will give you a simple solution but not quite flexible.

So my idea is to ORDER BY found results character count. In this case we can assume that the result with less characters is the nearest or exact match.

Your sql query will look something like this:

"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
    ORDER BY LENGTH(wpmj8c_terms.name)"

This query should output following result:

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

Again I want to tell you this is just an idea, that is not very flexible but I hope you get the point.

Good luck!

Answer 2

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");