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

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

$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

kobra:

I believe you can rearrange the array

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

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

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

kobra:

I believe you can rearrange the array

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

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?

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

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/08/16/improving-zen-cart-search-results/

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/08/16/improving-zen-cart-search-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/blob/8972fc0fceb0f1d8ffd2828339063e3292bbc1d5/includes/functions/functions_search.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.