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: