[Resolved] A space messed up a Search

[Dirty Butter]

I make a habit of checking in Customers Online to see what search terms were NOT successful for them. I was surprised today to see that the word Snoopy was not successful, as we have a whole category of snoopy items. The only thing I could think of was that they had perhaps written some more text and then changed their mind. So evidently they had actually searched for Snoopy 

Is there any way to stop that trailing space from messing up a search?

[bsandall]

Use PHP's trim function on each of the search terms.

In catalogue.class.php, under the searchCatalogue function, you can try adding the following as the first few lines of that function:

public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'fulltext') {
   if (is_array($search_data)) {
      array_walk($search_data, 'trim');
   } else {
       $search_data = trim($search_data);
   }
   ...

I haven't tested that, but it should be pretty close.

[bsandall]

MySQL typically ignores leading and trailing whitespace when performing searches (with the '=' comparison, and CubeCart already removes white space by using that to split the search string), but the spaces (among other things) were being deformed during the redirect process. This was fixed in GitHub issue # 1520.

Trying a search on my own store with an extraneous space (or more, both before and after) returns results as though no space were there.

In other words, you shouldn't need to edit anything - just pull the latest code from GitHub =)

Edited March 8, 2017 by bsandall

[Dirty Butter]

I tried this on my test site on current commit - Still did not find with the trailing space. So I need to be sure I merged the #1520 edits correctly.

[Dirty Butter]

Hmmm double checked my functions.inc.php and products.index.inc.php, and everything looks as it should with your #1520 changes. I'll play with this again tomorrow when I'm fresh.

[bsandall]

Have you customized your Catalogue class' searchCatalogue function?

Do you have any plugins that interact with the search query?

With the current code, you shouldn't end up with ' ' in a search query, though it may get logged that way (I'd have to double-check, but I think the log may send some data through htmlentities which would cause an unencoded space to appear that way).

If you can, enable debugging and search 'Snoopy   ' on your store, then check the debug output at the bottom of the screen. As long as you are not seeing ' ' at that point, you should be okay.

[Dirty Butter]

I may have confused the issue by saying the space was seen as   - It was NOT shown that way as the Search keyword(s) used. I was trying to convey the idea that there was a trailing space in the customer's search.

My Search code is far from stock - the line before you said to add code uses RLIKE instead of Fulltext.

[bsandall]

23 minutes ago, Dirty Butter said:

My Search code is far from stock - the line before you said to add code uses RLIKE instead of Fulltext.

Ever since this change by Al, it is no longer necessary to force 'RLIKE' in the function declaration. Searching for e.g. 'A1' will return results despite being only 2 characters in length, unlike previously.

You may want to consider using stock code for your search functionality. If there is something you have added that you can't do without and that isn't possible with the stock code, you may want to look at re-implementing it using the current code as the chassis.

[Dirty Butter]

I tried our test site with stock search as of current GitHub commit, but it still has the same issues I had before.

Bsmither developed a Search for us that disregards the order the search terms are in, as well as limiting the search to AND, not OR. Relevance just does not work for our products.

https://forums.cubecart.com/topic/46555-is-there-any-way-to-improve-customer-search-experience/

I've attached mine from live plushcatalog:

catalogue.class.php

 

[bsandall]

On 3/11/2017 at 9:18 AM, Dirty Butter said:

I tried our test site with stock search as of current GitHub commit, but it still has the same issues I had before.

Is your test site running any custom code? Because I've tried searching e.g. '    M&M    ' in both Firefox and Chrome using stock code, and both will return results provided I have an 'M&M' product. In other words, I am unable to reproduce your issue.

Speaking of which, I saw you mention in the other thread that special characters were causing your searches to fail (back in 2012) - that's fixed as of #1520. So if you have an 'M&M' product and put "M&M's" somewhere in the product description, that product would be returned for either of those search terms, whereas neither would work previously.

Since you seem to have a lot of customizations, you may need to hire a developer or open a support ticket (if Al accepts them for modified sites) to resolve your issue.

EDIT: Actually, the apostrophe, while encoded correctly, didn't turn up any search results with the default search code, even after adding "M&M's" to the product description.

Edited March 13, 2017 by bsandall

[Dirty Butter]

The special character issue was resolved a long time ago, as you said. As I long ago, before the fix, added all variations of such words in the keywords, so it will find M&M as well as Carters, Carter's, Carter, etc. The problem I still have is the trailing space for some reason. Thanks for trying to help. I've only seen that happen once, so I'll just have to chalk it up to "can't be helped".

I appreciate your time in making suggestions on this and trying to help!!

[Dirty Butter]

Well, it happened again today.

https://dirtybutter.com/plushcatalog/not-found-search-tips.html?search[keywords]=Bear

They should have found hundreds of results. I tried the search for myself with just Bear, and I did get the expected results. Then I put a space after Bear and got the not found page.

[bsmither]

Can you tell if the search was initiated from the quick-search entry field, or the Advanced Search page?

I just verified that searching on a term with and without a trailing space, from both the Quick Search field and from the Advanced Search page, works as expected.

[Dirty Butter]

It worked correctly for you on OUR store, with the special search code you worked out for me?

Neither box nor Advanced works for me with a space after the last term or before the first term.

[bsmither]

To clarify: searching on my local debugging store worked. I did not search your store.

 

[Dirty Butter]

I tried looking at the waterfall for two searches from the box - one with, one without, the trailing space.

See the plus sign??? That's the one with the trailing space.

https://dirtybutter.com/plushcatalog/search.html?search%5Bkeywords%5D=Turtle+&_a=category

https://dirtybutter.com/plushcatalog/search.html?search%5Bkeywords%5D=Turtle&_a=category

 

[Dirty Butter]

Here's the way a successful search looks from the box with more than one word and no superfluous spaces:

https://dirtybutter.com/plushcatalog/search.html?search%5Bkeywords%5D=Green+Turtle&_a=category

 

[bsmither]

The FULLTEXT mode works:

SELECT I.*, MATCH (I.product_code,I.description,I.name) AGAINST('BlahBC ' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM CubeCart_pricing_group WHERE group_id = 0 GROUP BY product_id) as G ON G.product_id = I.product_id  WHERE I.product_id IN (SELECT product_id FROM `CubeCart_category_index` as CI INNER JOIN CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (MATCH (I.product_code,I.description,I.name) AGAINST('BlahBC ' IN BOOLEAN MODE)) >= 0.5  AND ((I.use_stock_level = '0') OR (I.use_stock_level = '1' AND I.stock_level > 0)) ORDER BY Relevance DESC LIMIT 12 OFFSET 0

Note the BlahBC<space> in apostrophes.

The next test is to force the code to use RLIKE mode.

The RLIKE mode did not work, but the LIKE mode did.

If RLIKE failed, CubeCart is supposed to retry with LIKE. The LIKE mode worked. What went wrong?

Looking.

[bsmither]

Well, I was writing up a nice report and hit the wrong key. Ugh!

if (!empty($search_data['keywords'])) {
  $searchwords = preg_split( '/[\s,]+/', $GLOBALS['db']->sqlSafe($search_data['keywords']));
  foreach ($searchwords as $word) {
    $searchArray[] = $word;
  }

Change to:

if (!empty($search_data['keywords'])) {
  $searchwords = preg_split( '/[\s,]+/', $GLOBALS['db']->sqlSafe($search_data['keywords']));
  foreach ($searchwords as $word) {
    if(empty($word)) continue;
    $searchArray[] = $word;
  }

That was one too many times I hit the wrong key and lost the entire composition. Never again am I composing a post in a web-based editor.

[Dirty Butter]

Thank you for perservering! Sorry you had to do it twice. I'm on the kindle, but will work on this first thing in the morning.

[bsmither]

Actually, try this new statement:

if(empty($word) && !is_numeric($word)) continue;

 

[Dirty Butter]

I used the line you changed it to last night, and it is working properly now! THANK YOU!!! My search now uses AND with RLIKE. And it ignores order of the terms and any superfluous spaces!

/* BSMITHER SEARCH */
					$this->_sort_by_relevance = false;
					$rlike = '';
					if (!empty($search_data['keywords'])) {
											$searchwords = preg_split( '/[\s,]+/', $GLOBALS['db']->sqlSafe($search_data['keywords']));
					    foreach ($searchwords as $word) {
					if(empty($word) && !is_numeric($word)) continue;
					        if ((strtoupper($word) != 'AND') && (strtoupper($word) != 'OR')) $searchArray[] = $word;
					    }

					    $regexp = '';
					    $columnsToSearch = array(
					      'I.name',
					        'I.description',
					        'I.product_code',
					'I.seo_meta_keywords',
					    );

					    foreach($columnsToSearch as $col) {
					        for ($i=0, $noKeys=count($searchArray); $i<$noKeys; ++$i) {
					            $regexp[$col][] = $col."  RLIKE '[[:<:]]".$searchArray[$i]."[[:>:]]'";
					        }
					        $regexp[$col] = '(' . implode(' AND ', $regexp[$col]) . ')';
					    }
					    $rlike = " AND (" . implode(' OR ', $regexp) . ")";
					}
					$q2 = "SELECT I.* FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_pricing_group $group_id GROUP BY product_id) as G ON G.product_id = I.product_id $joinString WHERE I.product_id IN (SELECT product_id FROM `".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_category_index` as CI INNER JOIN ".$GLOBALS['config']->get('config', 'dbprefix')."CubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 ".$whereString.$rlike;
					$query = $q2.' '.$order_string.' '.$limit;
					if (($search = $GLOBALS['db']->query($query)) !== false) {
						$count = $GLOBALS['db']->query($q2, false, 0);
						$this->_category_count  = (int)count($count);
						$this->_category_products = $search;
						return true;
					}
				}
			}
		} else {
	/*	 END OF BSMITHER SEARCH */