0

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");
Nikki Mather
  • 1,118
  • 3
  • 17
  • 33
  • What is the difference with your previous question?? – Lelio Faieta Mar 01 '17 at 09:40
  • Possible duplicate of [MySQL show closest match first](http://stackoverflow.com/questions/42511277/mysql-show-closest-match-first) – Lelio Faieta Mar 01 '17 at 09:40
  • You mean other than not getting a helpful answer? – Nikki Mather Mar 01 '17 at 09:43
  • see this:http://stackoverflow.com/questions/19378434/mysql-order-by-string-value-first – Suchit kumar Mar 01 '17 at 09:43
  • 2
    @NikkiMather this is not a valid reason to open a new question with the same topic. Edit your previous post trying to understand why you didn't get enough attention. Probably it's because you are asking other users to write code on your behalf – Lelio Faieta Mar 01 '17 at 09:49
  • @Suchitkumar This almost works, but if a user is looking for example "Rear Stand Accessories" and type "Rear Stand", i'd like to show the results for "Rear Stand Accessories" at the top. It's very close to being functional, I just need it to accept partial matches too. – Nikki Mather Mar 01 '17 at 09:50
  • @LelioFaieta I'm not asking for someone to write code for me, just at the very least point me in the right direction to the point I don't need to do a month course on MySQL. As I've seen in many other related posts on StackOverflow, people are happy to provide full answers including the code specific to the users question. Do I need to have a professional working knowledge of the code before I ask a question or ask for further help? – Nikki Mather Mar 01 '17 at 09:52
  • @NikkiMather btw if you have a closer look at what is already in SO you will find other questions answered with the same issue. – Lelio Faieta Mar 01 '17 at 09:54
  • Yep, correct. And with my limited knowledge of MySQL I cannot accomodate their answers to my specific needs/code. So should I just not ask my question on SO in that case? – Nikki Mather Mar 01 '17 at 09:56
  • @NikkiMather Technically, no, you shouldn't. You should hire someone with the require skill set you need. The community is more than happy to help someone learn but you need to ask in a way of "I've seen this answer, done this with it to adapt it to my need and it's not working 'because'". That shows you're trying to learn, not just getting someone to do the work for you ... – webnoob Mar 01 '17 at 12:03

1 Answers1

0

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.

jakub wrona
  • 2,212
  • 17
  • 17