1

I havent been able to find the answer to this, maybe I have overlooked it, but this is a very simple question:

I have a search module for Joomla/Virtuemart and I have 1 problem with the search results generated by this module.

When I type this search word "mysearch" I want it to find all results with words that are part of the search query, e.g. title: "This is my search" or "What a nice search"

How do I accomplish this with MySQL?

This is my working code:

$ab = explode(' ', $keyword);
  $ccc='';
          $ccc2='';
          foreach ($ab as $k)
          {
            if (!empty($k))
           {

          if($ccc==''){         
          $ccc.= " concat(product_name, customtitle) RLIKE '".$db->getEscaped($k)."' AND";
                }
                else{
           $ccc.= " concat(product_name, customtitle) RLIKE '".$db->getEscaped($k)."' AND";
                }

                    if($ccc2==''){
          $ccc2.= " product_sku RLIKE '".$db->getEscaped($k)."' AND";
                }
                else{
            $ccc2.= " product_sku RLIKE '".$db->getEscaped($k)."' AND";
                }


       }
       else { 
       $ccc2.= " product_sku RLIKE '0' AND";
       $ccc.= " concat(product_name, customtitle) RLIKE '0' AND";
       }
    }

$ccc = rtrim($ccc, "AND");  
$ccc2 = rtrim($ccc2, "AND");    




$q ="(SELECT p.virtuemart_product_id, l.product_name
 from #__virtuemart_products p join
      #__virtuemart_products_".VMLANG." l
      on p.virtuemart_product_id = l.virtuemart_product_id
 WHERE ".$ccc." AND
       p.published = '1'
 LIMIT 0,".$prods."
)
union (select p.virtuemart_product_id, l.product_name
 from #__virtuemart_products p join
      #__virtuemart_products_".VMLANG." l
      on p.virtuemart_product_id = l.virtuemart_product_id
 where ".$ccc2." and
       p.published = '1'
 LIMIT 0,".$prods.")";
GeniusDesign
  • 499
  • 7
  • 25

2 Answers2

0

To the best of my knowledge....not possible with MySQL.

If you want true fuzzy matching, you need a search system designed for it. The Lucene library is great for this and is used in some amazing OSS projects.

Check out ElasticSearch and Solr.

of the two I recommend ElasticSearch.

InternalFX
  • 1,475
  • 12
  • 14
  • Thanks, that is what I was afraid of. I will look into the links you have posted and maybe I can use some of those libraries. – GeniusDesign Apr 06 '14 at 22:12
0

I was curious how to answer your same question, and though I'm unfamiliar with levenshtein, I found this question which I believe is very similar to yours, answered with an effective approach.

Note: the other states this is ONLY ok for small data searches. Because it compounds repeatedly, you can imagine how costly it could be (CPU and Memory).

Stack Overflow Reference php (fuzzy) search matching

PHP Documentation: PHP:net levenshtein

Community
  • 1
  • 1
Shaun Cameron McKinnon
  • 65
  • 6
  • Thank you, Shaun. I will look into this. Unfortunately the application for which this search module is used is a webshop with more than 200 categories and 3000 products, so it must be light weight – GeniusDesign Apr 06 '14 at 22:11