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.
and change the line immediately below what you have added to:
// Notifier Point
// $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";
// Ranking code by Rob - www.funkyraw.com
$from_str = ", MATCH(pd.products_name) AGAINST('$robs_keywords') AS rank1, MATCH(pd.products_description) AGAINST('$robs_keywords') AS rank2 ";
//end 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";
(just added a . before the = sign)
$from_str .= "FROM (" . TABLE_PRODUCTS . " p
Now find this line at around line 415
and change it to:
$order_str .= " order by 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.
$order_str .= " order by rank1 DESC, rank2 DESC, p.products_sort_order, pd.products_name";