MySQL - 按相关性排序结果（MATCH AGAINST？）

Question

I have a query that works for the most part, however the only snag seems to be the order in which the results are displayed. I would like to display the order based on the relevance to the search or closest match; as an example, a user might search for Accessories however the results are displayed as below and would ideally display with Accessories at the top instead.

A user may also search for Exhaust Accessories and of course it would be ideal if that too displayed at the top of the results, rather than them being ordered by what appears to be the ID as it currently is.

I have tried using match and against but can't seem to get it working properly with my limited MySQL knowledge.

Query result when searching for Accessories

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

The code I'm working with

$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

How about doing a strict matching first and a union with the full text matching (so you would have the 'Accessories' first). To eliminate duplications you could use the DISTINCT.

select DISTINCT(tab1.name) FROM (SELECT * FROM myTable WHERE name = 'Accessories' UNION SELECT * FROM myTable WHERE name LIKE '%Accessories') as tab1

I am sure you can improve the above to match your requirements.