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

which is the column that is holding the results? Is it `wpmj8c_terms.name`? — codtex, Feb 28 '17 at 14:54
Yes I saw it there, so then you can try my solution and give some feedback is it working for you :) — codtex, Feb 28 '17 at 15:01
I did try this, but the code isn't complex enough for my particular needs. It's a very clever workaround, but for me it doesn't give me the specificity that I need. Thanks though. — Nikki Mather, Feb 28 '17 at 15:03
I thing for your case using "Match Against" might be faster and much easier to rank. — Andoitz Jordán, Feb 28 '17 at 15:15

codtex · Answer 1 · 2017-02-28T14:58:55.637

0

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!

edited Feb 28 '17 at 14:58

answered Feb 28 '17 at 14:38

codtex

6,128
2
17
34

it will work if you use double quotes. This is a feature of `PHP`. Put the `SELECT` command between `"SELECT ... $str"` then you can directly put variable names there – codtex Feb 28 '17 at 14:45
ohk i think 'column_name = $str' – denny Feb 28 '17 at 14:46
If you do `'SELECT $str'` this will be error, anyway how quickly you were able to build this table :) – codtex Feb 28 '17 at 14:46
Again: you need to put your strings between double quotes if you want to use directly variables inside of them, your string should look like `"SELECT * from table_name $str"` not like `'SELECT * from table_name $str'` and `$str` should hold something of couse, for example `$str='WHERE some condition ...';` – codtex Feb 28 '17 at 14:51

denny · Answer 2 · 2017-02-28T16:37:53.827

0

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

edited Feb 28 '17 at 16:37

answered Feb 28 '17 at 14:39

denny

2,084
2
15
19

1

`ORDER BY` should be after `GROUP BY` – codtex Feb 28 '17 at 14:40
Now looks better :) – codtex Feb 28 '17 at 14:42
product_cat is your column name? – denny Feb 28 '17 at 14:57
I think if you look at the foreach, which is included in the query itself it might help make sense of what is going on. The column is `wpmj8c_terms.name` – Nikki Mather Feb 28 '17 at 15:00
Note that the outcome must work with the foreach, since that is part of the query itself. – Nikki Mather Feb 28 '17 at 15:02
Warning: Division by zero in /home/maver08/public_html/cwm/wp-content/themes/trisupply/search.php on line 43 – Nikki Mather Feb 28 '17 at 15:04
query fetch exact which you want but this error in php error not sql error.this may be helpful you http://stackoverflow.com/questions/3071067/php-how-to-catch-a-division-by-zero – denny Feb 28 '17 at 15:22

MySQL show closest match first

2 Answers2

Linked