Improving search results

Since the thread title is Improving Search Results perhaps this warrants a mention:

https://github.com/lat9/prioritize_matching_names

torvista:

Since the thread title is Improving Search Results perhaps this warrants a mention:

https://github.com/lat9/prioritize_matching_names

That's a good update since it makes products with the search keywords in the product name appear before ones just matching words in the description. A slight enhancement would be to have product names that begin with the keywords listed first. I haven't tested it, but something like the following might work:

public function update(&$class, $eventID) 
    {
        switch ($eventID) {
            case 'NOTIFY_SEARCH_SELECT_STRING':
                global $db, $keywords, $select_str;
                if (!empty($keywords) && zen_parse_search_string(stripslashes($_GET['keyword']), $search_keywords)) {
                    $in_name_select1 = '';
                    $in_name_select2 = '';
                    foreach ($search_keywords as $current_keyword) {
                        switch ($current_keyword) {
                            case '(':
                            case ')':
                            case 'and':
                            case 'or':
                                $in_name_select1 .= " $current_keyword ";
                                $in_name_select2 .= " $current_keyword ";
                                break;

                            default:
                                $in_name_select1 .= "pd.products_name LIKE '%:keywords'";
                                $in_name_select1 = $db->bindVars($in_name_select1, ':keywords', $current_keyword, 'noquotestring');
                                $in_name_select2 .= "pd.products_name LIKE '%:keywords%'";
                                $in_name_select2 = $db->bindVars($in_name_select2, ':keywords', $current_keyword, 'noquotestring');
                                break;
                        }
                    }
                    $select_str .= ", IF ($in_name_select1, 1, 0) AS in_name1, IF ($in_name_select2, 1, 0) AS in_name2 ";
                    $this->order_by = ' in_name1 DESC, in_name2 DESC,';
                }        
                break;

            case 'NOTIFY_SEARCH_ORDERBY_STRING':
                global $listing_sql;
                $listing_sql = str_ireplace('order by', 'order by' . $this->order_by, $listing_sql);
                break;

            default:
                break;
        }

Hi @jadebox, I just tried your modified code in zc1.58. Unfortunately, it doesn't make any difference on my search result (not work). Actually the original code from github doesn't make any difference neither. Not sure why...I have changed "header_php.php" file back to the default before the test. No log file is received during my test. The original code is based on zc 1.53 though.

For your mentioned code, I suspected you want to rank by the product name at first, then product description, correct? So your following line below

$in_name_select2 .= "pd.products_name LIKE '%:keywords%'";

It should be changed to the following, if I'm correct.

$in_name_select2 .= "pd.products_description LIKE '%:keywords%'";

It doesn't work no matter I changed the line above or not...

njcyx:

Hi @jadebox, I just tried your modified code in zc1.58. Unfortunately, it doesn't make any difference on my search result (not work). Actually the original code from github doesn't make any difference neither. Not sure why...I have changed "header_php.php" file back to the default before the test. No log file is received during my test. The original code is based on zc 1.53 though.

For your mentioned code, I suspected you want to rank by the product name at first, then product description, correct? So your following line below

$in_name_select2 .= "pd.products_name LIKE '%:keywords%'";

It should be changed to the following, if I'm correct.

$in_name_select2 .= "pd.products_description LIKE '%:keywords%'";

It doesn't work no matter I changed the line above or not...

No, I wanted it to sort products that have names that start with the keywords before products that just have the keyword in the name. The first "like" returns True for product names starting with the keywords (:keyword%). The second returns True for product names which include the keywords anywhere (%: keywords%). Using "DESC" causes tbe LIKEs that return True to be listed before the ones that are False.

So, if your keyword is "Red" then a product called "Red Rider BB Gun" will be listed before "Daisy Red Rider BB Gun."

I see. Thanks for your explanation.

I changed the related code to the following and it doesn't work neither...I just want the basic keyword match.

                            
default:
                                $in_name_select1 .= "pd.products_name LIKE ':keywords'";
                                $in_name_select1 = $db->bindVars($in_name_select1, ':keywords', $current_keyword, 'noquotestring');
                                $in_name_select2 .= "pd.products_description LIKE ':keywords'";
                                $in_name_select2 = $db->bindVars($in_name_select2, ':keywords', $current_keyword, 'noquotestring');
                                break;

I would recommend going back to the original code from GitHub and getting it to work before trying to change it. I apologize that I haven't tried torvista's code, but I don't see any obvious reason that it wouldn't work. My guess is that you might not have installed the file in the right folder of your store

There's a support thread for the code at:

https://www.zen-cart.com/showthread.php?218702-Search-Prioritize-Matching-Names-Support-Thread

Hi @jadebox, thanks for your suggestion. I will do some tests to make the original code work at first then....

To muddy the waters further, this thread is related, for those with the time to experiment:
https://github.com/zencart/zencart/issues/5369

I think there is certainly scope to use subqueries in this as described in the comments, but I am just not finding the time...

I recommend looking at Elastic Search for improving the search functionality. We are using it in both zen cart and non-Zen Cart implementations with great success. You can see a zen cart example at redlinestands.com.

numinix:

I recommend looking at Elastic Search for improving the search functionality. We are using it in both zen cart and non-Zen Cart implementations with great success. You can see a zen cart example at redlinestands.com.

Hi @numinix, thanks for your suggestion. Can you please post some links for the Elastic Search? Is there a plug-in for Zen Cart?

I checked your example link and its search function is too slow. It takes me about 7-8s to load, when I hit the "search" button every time. Also, if I search keyword "2 POST LIFT", the first 10 results don't have "2 POST LIFT" in their titles...

madmouse:

Ok, I finally sussed this. I now have relevant ranked results using the built in ZenCart search. It's a bit of a hack (modifying a core file) but I don't know how to do it properly using the notifers, etc. But it is a very simple hack. It uses the full text searching capabilities built in to mySQL to rank each result and then it sorts the results by that rank. I've done lots of test searches on my site and it really does work well.

Step one: enable full text indexing
Using phpMyAdmin, go to the structure of products_description. On the row for products_name, look at the end of the row and press the T icon (if you hover the tooltip is Fulltext). Now do the same for products_description.

Step two: edit the code in includes/modules/pages/advanced_search_result/header_php.php

Around line 219, add the code in red. The commented out line above is nothing to do with me, it was already commented out.

// Notifier Point
$zco_notifier->notify('NOTIFY_SEARCH_SELECT_STRING');

// $from_str = "from " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m using(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c";

// FullText Ranking code by Rob - www.funkyraw.com
$from_str = ", MATCH(pd.products_name) AGAINST(:keywords) AS rank1, MATCH(pd.products_description) AGAINST(:keywords) AS rank2 ";
$from_str = $db->bindVars($from_str, ':keywords', stripslashes($_GET['keyword']), 'string');
//end FullText ranking code

$from_str = "FROM (" . TABLE_PRODUCTS . " p
LEFT JOIN " . TABLE_MANUFACTURERS . " m
USING(manufacturers_id), " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_CATEGORIES . " c, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c )
LEFT JOIN " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd
ON mtpd.products_id= p2c.products_id
AND mtpd.language_id = :languagesID";

> 
> and change the line immediately below what you have added to:
> ```
$from_str .= "FROM (" . TABLE_PRODUCTS . " p

(just added a . before the = sign)

Now find this line at around line 415

$order_str .= " order by p.products_sort_order, pd.products_name";

> 
> and change it to:
> ```
$order_str .= " order by rank1 DESC, rank2 DESC, p.products_sort_order, pd.products_name";

And you're done. Make sure you test before going live. It will fail with errors if you haven't first enabled the full text indexing. Hope it helps someone. And if anyone wants to use the code to create a proper module then please go ahead.

Rob

I just tried to use the following tool to update my database to utf8mb4.

https://www.zen-cart.com/downloads.php?do=file&id=2367

I haven't encountered much issues during the upgrade process. However, after I upgraded my live site, I started to receive warnings in the back end when users tried the search function. Luckily I have a recent database backup to recover so I don't lose much data...

Error looks like the following:

PHP Fatal error: 1191:Can't find FULLTEXT index matching the column list :: SELECT DISTINCT p.products_image, p.products_model, p.products_quantity , p.products_sort_order, m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id, p.product_is_call

Anyway, I did some research later and I found out my database mod used in this thread has been erased or reset during this database upgrade process. After I changed the database again according to this thread (for FULLTEXT), this issue was resolved. No files are changed.

The trick above still works on zc210! But the file has been changed. The following file needs to be changed in the same way.

\includes\classes\class.search.php

Instant Search plug-in has not been updated for zc210 yet.