Page 1 of 3 123 LastLast
Results 1 to 10 of 30
  1. #1
    Join Date
    May 2005
    Location
    London, England
    Posts
    19
    Plugin Contributions
    0

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

  2. #2
    Join Date
    Aug 2005
    Location
    Arizona
    Posts
    27,762
    Plugin Contributions
    9

    Default Re: Improving search results

    Try looking at /includes/modules/pages/advanced_search_result/ header_php.php
    Zen-Venom Get Bitten

  3. #3
    Join Date
    May 2005
    Location
    London, England
    Posts
    19
    Plugin Contributions
    0

    Default Re: Improving search results

    Quote Originally Posted by kobra View Post
    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

  4. #4
    Join Date
    Aug 2005
    Location
    Arizona
    Posts
    27,762
    Plugin Contributions
    9

    Default Re: Improving search results

    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
    Zen-Venom Get Bitten

  5. #5
    Join Date
    Jul 2006
    Posts
    308
    Plugin Contributions
    0

    Default Re: Improving search results

    Quote Originally Posted by kobra View Post
    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.

  6. #6
    Join Date
    May 2005
    Location
    London, England
    Posts
    19
    Plugin Contributions
    0

    Default Re: Improving search results

    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
    Last edited by DrByte; 22 May 2018 at 10:47 PM. Reason: Updated to allow for apostrophes and quotes, improving security

  7. #7
    Join Date
    May 2005
    Location
    London, England
    Posts
    19
    Plugin Contributions
    0

    Default Re: Improving search results

    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

  8. #8
    Join Date
    Aug 2004
    Posts
    708
    Plugin Contributions
    0

    Default Re: Improving search results

    Thanks for posting this. :)
    Last edited by Jeff_Mash; 25 Jan 2010 at 10:25 PM.

  9. #9
    Join Date
    Sep 2009
    Posts
    71
    Plugin Contributions
    0

    Default Re: Improving search results

    Quote Originally Posted by kobra View Post
    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?

  10. #10

    Default Re: Improving search results

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

 

 
Page 1 of 3 123 LastLast

Similar Threads

  1. v154 Improving search box results on main page
    By cahoca in forum General Questions
    Replies: 2
    Last Post: 24 Jan 2016, 04:26 PM
  2. Improving Search Speed
    By jmcdougal in forum General Questions
    Replies: 0
    Last Post: 28 Jan 2011, 06:27 PM
  3. Improving Site Search
    By mutualadvantage in forum General Questions
    Replies: 1
    Last Post: 18 Feb 2008, 10:47 PM
  4. Replies: 8
    Last Post: 5 Dec 2006, 10:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR