Jump to content

Archived

This topic is now archived and is closed to further replies.

Dirty Butter

Is there any way to improve customer SEARCH experience?

Recommended Posts

Using the meta keywords as the Search base, as Bsmither helped me do, has helped a lot. BUT the search still will balk if the order of search terms does not match the order the terms are listed. For instance, carter's bear SHOULD return the same results as bear carter's. That doesn't happen.

Share this post


Link to post
Share on other sites

Revisiting this Search term order issue again.

 

As of 5.2.13 this is still a problem for us. I love the code fix Bsmither provided in this thread, but I'm totally frustrated by the order of search terms roadblock. I have trouble myself finding a given product at times, thanks to putting the search terms in the "wrong" order. It's no wonder customers can't find what they want!

Share this post


Link to post
Share on other sites

Be aware that in "Natural Language" mode (as opposed to "Boolean" mode), MySQL will not use any words that are three characters or less. I believe CubeCart strips them out anyway.

Share this post


Link to post
Share on other sites

As it is now, with the various tweaks you've provided, our plushcatalog Search uses the seo keywords.

 

It finds 64 pages for "dog". So that's not my problem.

 

Search for (without the quotes) "puppet dog" and you'll get hugely different results from "dog puppet".

Share this post


Link to post
Share on other sites

STILL trying to fix this search term order problem for our toy site. Is there a way to take the search terms a customer uses and split that string into individual word strings that can be searched for regardless of their order?

 

I'm currently using this code:

$regexp = substr($regexp, 0, strlen($regexp)-2);
						$rlike = " AND (I.seo_meta_keywords RLIKE '".$regexp."' OR I.name RLIKE '".$regexp."' OR I.description RLIKE '".$regexp."' OR I.product_code RLIKE '".$regexp."')";
					}

But the only part I actually need is I.seo_meta_keywords RLIKE ' ".$regexp." '  as all the title and description terms are listed in the keywords.

Share this post


Link to post
Share on other sites

I was under the impression that CubeCart did split the words into an array, then distributed that array to create a RLIKE train of sequences.

 

For the splitting of the words, I know the split is on a comma, but I thought I saw a split on spaces as well.

 

In admin, Store Settings, Advanced tab, enable debugging (this WILL crash the Vector skin - or is it Blueprint? - so try on the site running the stock skin) and add your IP address (www.whatismyip.com) to restrict debugging to your computer only. In the debug area, find the query that shows the actual final form of the search query.

 

Try puppet,dog (with comma), then puppet dog (no comma), then dog,puppet, then dog puppet, and compare the queries.

Share this post


Link to post
Share on other sites

(Debug works with Blueprint)

puppet,dog 18 results

Not sure exactly what you want from debug info

 SELECT I.* 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 (I.seo_meta_keywords RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]' OR I.name RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]' OR I.description RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]' OR I.product_code RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]') LIMIT 100 -- (5.12442207336 sec) [CACHED]
[13] SELECT I.* 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 (I.seo_meta_keywords RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]' OR I.name RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]' OR I.description RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]' OR I.product_code RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]') -- (5.32946300507 sec) [CACHED]

Hope that's what you need to see from Debug. I'll follow with the other scenarios.

Share this post


Link to post
Share on other sites

puppet dog  - same 18 results

SELECT I.* 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 (I.seo_meta_keywords RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]' OR I.name RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]' OR I.description RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]' OR I.product_code RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]') LIMIT 100 -- (5.47906398773 sec) [CACHED]

dog,puppet  - 5 results

SELECT I.* 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 (I.seo_meta_keywords RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]' OR I.name RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]' OR I.description RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]' OR I.product_code RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]') LIMIT 6 OFFSET 0 -- (4.63149690628 sec) [CACHED]
[30] SELECT I.* 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 (I.seo_meta_keywords RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]' OR I.name RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]' OR I.description RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]' OR I.product_code RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]') -- (4.83943891525 sec) [CACHED]

dog puppet - same 5 results

 SELECT I.* 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 (I.seo_meta_keywords RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]' OR I.name RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]' OR I.description RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]' OR I.product_code RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]') LIMIT 6 OFFSET 0 -- (5.01385211945 sec) [CACHED]
[32] SELECT I.* 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 (I.seo_meta_keywords RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]' OR I.name RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]' OR I.description RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]' OR I.product_code RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]') -- (5.00954079628 sec) [CACHED]

Share this post


Link to post
Share on other sites

So what I need is something that would produce

I.seo_meta_keywords RLIKE '[[:<:]]dog[[:>:]].*[[:<:]]puppet[[:>:]]'

 

as well as

 

I.seo_meta_keywords RLIKE '[[:<:]]puppet[[:>:]].*[[:<:]]dog[[:>:]]'

 

no matter how many terms a customer inputs.

 

When I use fulltext search

public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'fulltext') {

dog puppet finds 438 results!!

 SELECT I.*, MATCH (I.product_code,I.name,I.description,I.seo_meta_keywords) AGAINST('dog puppet' 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.name,I.description,I.seo_meta_keywords) AGAINST('dog puppet' IN BOOLEAN MODE)) >= 0.5 ORDER BY Relevance DESC LIMIT 6 OFFSET 432 -- (0.667565822601 sec) [CACHED]
[32] SELECT COUNT(I.product_id) as count, MATCH (I.product_code,I.name,I.description,I.seo_meta_keywords) AGAINST('dog puppet' 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.name,I.description,I.seo_meta_keywords) AGAINST('dog puppet' IN BOOLEAN MODE)) >= 0.5 GROUP BY I.product_id ORDER BY Relevance DESC -- (0.825587034225 sec) [CACHED]

Share this post


Link to post
Share on other sites

In the situation that uses RLIKE, which is a "regular expression" match, the query is having the database look for:

whitespace*

followed by the word puppet

followed by whitespace*

followed by anything or nothing

followed by whitespace*

followed by the word dog

followed by whitespace*

 

The whitespace defines a word boundary, meaning anything except a character, number, or underscore can be next to the word.

 

Note that this regular expression is very much fixated on word order.

 

I will look again at how CubeCart is assembling the query and see if there is something that can be done.

 

By the way, I am investigating the installation of a true search engine - Sphinx. Unfortunately, it is something that the server administrator must install, just like installing a database server, a web server, or PHP onto the server box. So, if you have a VPS or dedicated actual server box for your hosting account, it may be do-able.

Share this post


Link to post
Share on other sites

Please try this experiment. It uses a feature of regular expressions called 'lookahead'. I've been trying to test it on regex testers on the web, and using some utilities I have on my computer, but it doesn't seem to want to work. I can't get a response of a match. Yet, I am convinced the expression is valid.

 

In the file /classes/catalogue.class.php, near line 1213:

Near line 1213:
Was:
$regexp .= '[[:<:]]'.$searchArray[$i].'[[:>:]].*';
 
Now:
$regexp .= '(?=.*[[:<:]]'.$searchArray[$i].'[[:>:]])'; // '[[:<:]]'.$searchArray[$i].'[[:>:]].*';
 
Near line 1216:
Was:
$regexp = substr($regexp, 0, strlen($regexp)-2);
 
Now:
// $regexp = substr($regexp, 0, strlen($regexp)-2);

This expression is supposed to find all the words regardless of the order in which they appear in the source text.

 

You can use spaces or commas to separate the search words. CubeCart can split them on either.

Share this post


Link to post
Share on other sites

No on page error message or anything about nothing being found when I searched for puppet dog.

 

PHP:
[Warning] /home3/butter01/public_html/plushcatalog/classes/catalogue.class.php:146 - Invalid argument supplied for foreach()

if (!empty($products)) {
			foreach ($products as $product) {
				$product = $this->getProductPrice($product);
				$this->productAssign($product, false);
				$product['url'] = $GLOBALS['seo']->buildURL('prod', $product['product_id'], '&amp;');
				$product['name'] = validHTML($product['name']);
				$vars['products'][] = $product;
			}
 INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1261] "SELECT I.* 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 (I.seo_meta_keywords RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])' OR I.name RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])' OR I.description RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])' OR I.product_code RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])') LIMIT 6 OFFSET 0 " - Got error 'repetition-operator operand invalid' from regexp','1415714097'); -- (0.000191926956177 sec)
[11] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1261] "SELECT I.* 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 (I.seo_meta_keywords RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])' OR I.name RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])' OR I.description RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])' OR I.product_code RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])') LIMIT 6 OFFSET 0 " - Got error 'repetition-operator operand invalid' from regexp','1415714097'); -- (0.000191926956177 sec) [CACHED]
[12] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1262] "SELECT I.* 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 (I.seo_meta_keywords RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])' OR I.name RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])' OR I.description RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])' OR I.product_code RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])') " - Got error 'repetition-operator operand invalid' from regexp','1415714097'); -- (0.000182867050171 sec)
[13] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1262] "SELECT I.* 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 (I.seo_meta_keywords RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])' OR I.name RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])' OR I.description RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])' OR I.product_code RLIKE '(?=.*[[:<:]]puppet[[:>:]])(?=.*[[:<:]]dog[[:>:]])') " - Got error 'repetition-operator operand invalid' from regexp','1415714097'); -- (0.000182867050171 sec) [CACHED]

Share this post


Link to post
Share on other sites

Well, phooey! I have come to learn that MySQL uses the POSIX language for it's regular expression engine. That means 'lookahead' is not going to work. So let's try this.

 

Lines 1201 - 1218 should look like this (note where you may have edited the code to add the seo field):

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

    $noKeys = count($searchArray);
    $regexp = '';
    for ($i=0; $i<$noKeys; ++$i) {
        $ucSearchTerm = strtoupper($searchArray[$i]);
        if (($ucSearchTerm != 'AND') && ($ucSearchTerm != 'OR')) {
            $regexp .= '[[:<:]]'.$searchArray[$i].'[[:>:]].*';
        }
    }
    $regexp = substr($regexp, 0, strlen($regexp)-2);
    $rlike    = " AND (I.name RLIKE '".$regexp."' OR I.description RLIKE '".$regexp."' OR I.product_code RLIKE '".$regexp."')";
}

Enclose all of that block of code in block comments: /* all the lines of code */

Add this afterwards:

$rlike = '';
if (!empty($search_data['keywords'])) {
    $searchwords = preg_split( '/[ ,]/', $GLOBALS['db']->sqlSafe($search_data['keywords']));
    foreach ($searchwords as $word) {
        if ((strtoupper($word) != 'AND') && (strtoupper($word) != 'OR')) $searchArray[] = $word;
    }

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

    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) . ")";
}

Note the $columnsToSearch array. Add the seo column here - don't forget the trailing comma.

Share this post


Link to post
Share on other sites

Still not showing any search results.

 

PHP:
[Warning] /home3/butter01/public_html/plushcatalog/classes/catalogue.class.php:146 - Invalid argument supplied for foreach()

[10] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1289] "SELECT I.* 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 ((`I.seo_meta_keywords` RLIKE '[[:<:]]dog[[:>:]]' AND `I.seo_meta_keywords` RLIKE '[[:<:]]puppet[[:>:]]') OR (`I.name` RLIKE '[[:<:]]dog[[:>:]]' AND `I.name` RLIKE '[[:<:]]puppet[[:>:]]') OR (`I.description` RLIKE '[[:<:]]dog[[:>:]]' AND `I.description` RLIKE '[[:<:]]puppet[[:>:]]') OR (`I.product_code` RLIKE '[[:<:]]dog[[:>:]]' AND `I.product_code` RLIKE '[[:<:]]puppet[[:>:]]')) LIMIT 6 OFFSET 0 " - Unknown column 'I.seo_meta_keywords' in 'where clause'','1415732518'); -- (0.00981092453003 sec)
[11] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1289] "SELECT I.* 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 ((`I.seo_meta_keywords` RLIKE '[[:<:]]dog[[:>:]]' AND `I.seo_meta_keywords` RLIKE '[[:<:]]puppet[[:>:]]') OR (`I.name` RLIKE '[[:<:]]dog[[:>:]]' AND `I.name` RLIKE '[[:<:]]puppet[[:>:]]') OR (`I.description` RLIKE '[[:<:]]dog[[:>:]]' AND `I.description` RLIKE '[[:<:]]puppet[[:>:]]') OR (`I.product_code` RLIKE '[[:<:]]dog[[:>:]]' AND `I.product_code` RLIKE '[[:<:]]puppet[[:>:]]')) LIMIT 6 OFFSET 0 " - Unknown column 'I.seo_meta_keywords' in 'where clause'','1415732518'); -- (0.00981092453003 sec) [CACHED]
[12] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1290] "SELECT I.* 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 ((`I.seo_meta_keywords` RLIKE '[[:<:]]dog[[:>:]]' AND `I.seo_meta_keywords` RLIKE '[[:<:]]puppet[[:>:]]') OR (`I.name` RLIKE '[[:<:]]dog[[:>:]]' AND `I.name` RLIKE '[[:<:]]puppet[[:>:]]') OR (`I.description` RLIKE '[[:<:]]dog[[:>:]]' AND `I.description` RLIKE '[[:<:]]puppet[[:>:]]') OR (`I.product_code` RLIKE '[[:<:]]dog[[:>:]]' AND `I.product_code` RLIKE '[[:<:]]puppet[[:>:]]')) " - Unknown column 'I.seo_meta_keywords' in 'where clause'','1415732518'); -- (0.00984811782837 sec)
[13] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1290] "SELECT I.* 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 ((`I.seo_meta_keywords` RLIKE '[[:<:]]dog[[:>:]]' AND `I.seo_meta_keywords` RLIKE '[[:<:]]puppet[[:>:]]') OR (`I.name` RLIKE '[[:<:]]dog[[:>:]]' AND `I.name` RLIKE '[[:<:]]puppet[[:>:]]') OR (`I.description` RLIKE '[[:<:]]dog[[:>:]]' AND `I.description` RLIKE '[[:<:]]puppet[[:>:]]') OR (`I.product_code` RLIKE '[[:<:]]dog[[:>:]]' AND `I.product_code` RLIKE '[[:<:]]puppet[[:>:]]')) " - Unknown column 'I.seo_meta_keywords' in 'where clause'','1415732518'); -- (0.00984811782837 sec) [CACHED]

 I moved the 'I.seo_meta_keywords', line to the beginning of the array list - because it was originally at the end and the Debug message was

 

Unknown column 'I.name' in 'where clause'

 

So the array is not being used evidently.
 

Share this post


Link to post
Share on other sites

Right. Make this edit:

Was:
"`".$col."` RLIKE
 
Now:
$col." RLIKE

The backticks are enclosing both the I, which is the alias of the table name, and name, the column name, making the whole thing a column name. So we need to remove the backticks.

Share this post


Link to post
Share on other sites

I tried this, it doesn't seem to be altering the search results all that much, I have it running side by side with the old search on a test site and I'm getting almost identical results.

 

Edit: Actually 1 big plus is that it is a better at handling search queries with symbols in it, so for that alone it's nice

 

My search result go like this,

 

Search: 'Model'

 

Result  -  3 Parts    - All parts with 'model' in the description.

 

Search; 'Model' + 'Hinge' or 'Hinge' + 'Model'

 

Result   - 6 Parts  - All parts with Term 'Hinge' Only

 

(can be repilcated by just flat searching the word hinge)

 

Desired Result   - 1 Part - Single result that matches 'Model' + 'Hinge' only

 

 

 

Have I missed some step ?

 

cause this is the same behaviour as before.

Share this post


Link to post
Share on other sites

yeeeeeeeeep,

 

I forgot to change

 

public function searchCatalogue line to 'like'

 

 

can confirm am idiot.

 

 

 

This is perfect, you are a prince, sir.

Share this post


Link to post
Share on other sites

Revisiting this:

The RLIKE expressions as seen in the seventh post upwards:

RLIKE '[[:<:]]".$searchArray[$i]."[[:>:]]'

is still searching using word boundaries. This is whole-word searches.

I want to make it so that partial-word searches will also work.

Let me play around with this some more: https://forums.cubecart.com/topic/49330-fuzzy-search/

Share this post


Link to post
Share on other sites

I have a lot of issues with the search function as well. My product codes all have a hypen in, and if you search using a product code, nothing is returned. This makes it pretty useless, since they are technical items and most people will use the product code.

Share this post


Link to post
Share on other sites

Sorry for revisiting this again. 

It seems like I could fix the search when product codes contain a hypen, by doing a search in boolean mode as per http://stackoverflow.com/questions/5192499/how-to-allow-fulltext-searching-with-hyphens-in-the-search-query

So if I can do the search like this:

SELECT * FROM your_table_name WHERE MATCH (your_table_column_name) AGAINST ('"SQL-MySQL"' IN BOOLEAN MODE)

Is it possible to modify the search query to work like this?

Share this post


Link to post
Share on other sites

×
×
  • Create New...