Improving search results

The basic search feature within ZenCart doesn't work very well for my store. Many of my products will mention other products within their descriptions, and often they come up before the actual product.

To solve this problem, I would like to be able to sort the search results so that if the search term is in the product name, that product would be listed before a product with the search term only in the description.

The simple solution would be to re-write the SQL for the search function to do this, but I can't work out a way of doing this.

The only way I can think of to do this is to execute the SQL query, get the results into an array and then sort that array. But I can't work out how to do this due to the way the code is structured - currently the SQL statement for the search is passed to the result page which displays the products and I can't see where I would be able to add my new code.

Hope this all makes sense and someone can point me in the right direction...

thanks, Rob

(For an example of my problem, if you visit my site in my signature and search for "lucuma" you will see that first you see products which mention lucuma in their description, the actual lucuma product is way down the list.)

Try looking at /includes/modules/pages/advanced_search_result/ header_php.php

Quote:

---

Originally Posted by kobra

Try looking at /includes/modules/pages/advanced_search_result/ header_php.php

---

Thank, I have looked at that file. The sql statement is passed to splitPageResults which outputs the results. Without a simple modification to the sql statement it looks like it is going to be a major hack.

cheers, Rob

I believe you can rearrange the array

Code:

---

$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,
                    'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,
                    'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,
                    'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,
                    'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,
                    'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,
                    'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE);

---

Easy to test

Quote:

---

Originally Posted by kobra

I believe you can rearrange the array

Code:

---

$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,
                    'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,
                    'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,
                    'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,
                    'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,
                    'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,
                    'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE);

---

Easy to test

---

How would you modify that? I'm lost. The list above doesn't include the item description, which is what search results seem to place a higher value on than the item name.

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.

Code:

---

// 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:

Code:

---

$from_str .= "FROM (" . TABLE_PRODUCTS . " p

---

(just added a . before the = sign)



Now find this line at around line 415

Code:

---

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

---

and change it to:

Code:

---

$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

Ps By default, the mySQL full text search ignores words shorter than 4 characters. I found this didn't work for my store as there are a few important 3 letter words. So in the mySQL configuration file my.cnf, add the following:

find the [mysqld] section (or create if doesn't exist) and add
ft_min_word_len=3

Find [myisamchk] (or create if doesn't exist) and add
ft_min_word_len=3

You then have to restart mySQL and then rebuild the table index by using phpMyAdmin to Repair the products_description table.

Rob

Thanks for posting this. :)

Quote:

---

Originally Posted by kobra

I believe you can rearrange the array

Code:

---

$define_list = array('PRODUCT_LIST_MODEL' => PRODUCT_LIST_MODEL,
                    'PRODUCT_LIST_NAME' => PRODUCT_LIST_NAME,
                    'PRODUCT_LIST_MANUFACTURER' => PRODUCT_LIST_MANUFACTURER,
                    'PRODUCT_LIST_PRICE' => PRODUCT_LIST_PRICE,
                    'PRODUCT_LIST_QUANTITY' => PRODUCT_LIST_QUANTITY,
                    'PRODUCT_LIST_WEIGHT' => PRODUCT_LIST_WEIGHT,
                    'PRODUCT_LIST_IMAGE' => PRODUCT_LIST_IMAGE);

---

Easy to test

---

I am having problems with this.

I have many custom fields which I have added to search function.

If you add the lines in here a column will be created, but I need to check whether there will be results in this column. Because if there are no results i dont want the column to show....

I can do this on the product listing page but not on the advanced search result page...

does anyone know how to do this?

Rob, thank you sharing this solution. It has saved me loads of time and effort. Works like a dream. :clap:

Quote:

---

Originally Posted by fontaholic

rob, thank you for this code. i'm hoping to get it to work. i did the text indexing and put the code changes above into my header_php.php file, but it's not working (as far as i can tell). you reference something called 'robs_keywords'... how do i define that for my own site? ultimately i'm hoping to have this sort search results by product category, then products name if possible. do you think that is possible? thanks!

---

SWEEEEEEEET!!!! that sorted it right out. top notch!!!

Nice one! that sorted my search out loads; there's still a few stragglers here and there but it's infinitely better! thanks man i really appreciate that!

I just performed the mod on my 1.3.9h store and it works great! I just need to do the additional modification to the mysql config file to account for the 3 letter words. Thanks for posting the code!

trying to add full text using phpmyadmin but it doesnt have that option, its grayed out. anyone else have this issue?

Quote:

---

Originally Posted by 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.

Code:

---

// 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:

Code:

---

$from_str .= "FROM (" . TABLE_PRODUCTS . " p

---

(just added a . before the = sign)



Now find this line at around line 415

Code:

---

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

---

and change it to:

Code:

---

$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

---

This trick also works on 1.57c. Thanks!

But I did notice that sometimes my screen will auto scroll down several rows. It only happens on my PC, not on my phone. I did some tests and it looked like that it is related to jscript_onload_helper.php under the same folder. I deleted that file and it worked properly. It doesn't generate any log files or warning.

This trick also works on zc1.58. Thanks!

But the file has been moved. This modification should be applied to the following file instead.

\includes\modules\pages\search_result\header_php.php

jscript_onload_helper.php file is gone as well. I cannot find this file and I cannot find function onloadFocus() in any files in zc1.58 now. I didn't notice the "auto scroll" issue I mentioned in my last post anyway.

Using Rob's full-search technique really improved the search results on our web site. I wrote a blog post a while back on how I implemented it for 1.5.7. I tweaked it a little for slightly better results and added a field so that you can specify additional text to search that isn't displayed to the customer:

https://jonrocket.com/zencart/2021/0...earch-results/

Quote:

---

Originally Posted by jadebox

Using Rob's full-search technique really improved the search results on our web site. I wrote a blog post a while back on how I implemented it for 1.5.7. I tweaked it a little for slightly better results and added a field so that you can specify additional text to search that isn't displayed to the customer:

https://jonrocket.com/zencart/2021/0...earch-results/

---

for anyone thinking about doing this, i think it is a MAJOR mistake. granted this is only my opinion, but still...

as the blog post says in it's preface:

"The method I describe here involves directly modifying Zen Cart core files. Generally, it isn’t a good idea to modify the core files of an application since it makes upgrading more difficult and error-prone...."

i agree with the preface that searching on zen-cart was not ideal... which is why there has been some MASSIVE overhaul of said searching in v158. i should know... i coded most of it... and the initial merge on my PR on searching was over 2 years ago (or something like that). and i have waited for this code to be released to the ZC community. which is now is. and part of it can be seen here:

https://github.com/zencart/zencart/b....php#L160-L210

in fact, i saw that my code was being re-used on the search modal in the bootstrap template.

now, could this be improved? no doubt; i think most code can be improved.

but coming onto the ZC forum and promoting code that modifies the ZC core is not my idea of supporting the community. if you think your code is so great, where is the PR on github? why not use notifiers?

i think anyone who is thinking about changing all of those core files should think long and hard.

and if you really want better searching on the customer side, i would look at this plugin:

https://github.com/marco-pm/zencart_instantsearch

finally, the new search functions that are now merged into the v158 core are used throughout the repository; including on the admin side.

at some point, i hope to write something up in the docs.

second finally: i will look close at the blog post, and perhaps consider adding some of the code to the ZC core. which i think better supports the community. or maybe @jadebox can do it.

best.

I shared something I found useful and gave a warning about why it mignt not be the best way to accomplish the goal. I documented it for my own reference and thought it might be useful to other users of Zen Cart and to the developers.

I didn't intend to disparage you or any others working on Zen Cart since I really appreciate the work that has gone into it. I would have suffered though the effort to convert to another shopping cart if the 1.5.x upgrade had not been such an improvement over the 1.3.x version I was using.

You are quite welcome to use any of the code or ideas from the blog post if you find them useful. I am willing to help but free time is in short supply for me until I finally decide to retire.

Thanks for all the inputs.

Instant search plug-in mentioned by @carlwhat is great. However, it doesn't search the description by the latest published version (v2.1.0). Its author mentioned adding description search could slow down the site which makes senses to me. I don't want to make my site slower again...

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

The trick mentioned in this thread is not ideal, but it works for description search and it is not difficult to modify the code.

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

https://github.com/lat9/prioritize_matching_names

Quote:

---

Originally Posted by 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:

Code:

---

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...

Quote:

---

Originally Posted by 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.

Code:

---

                           
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....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.

Quote:

---

Originally Posted by 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...

Quote:

---

Originally Posted by 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.

Code:

---

// 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:

Code:

---

$from_str .= "FROM (" . TABLE_PRODUCTS . " p

---

(just added a . before the = sign)



Now find this line at around line 415

Code:

---

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

---

and change it to:

Code:

---

$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.