Improving search results

[njcyx]

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.