Jump to content

Is there any way to improve customer SEARCH experience?


Dirty Butter

Recommended Posts

  • Replies 58
  • Created
  • Last Reply

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.

Link to comment
Share on other sites

  • 1 month later...

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!

Link to comment
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".

Link to comment
Share on other sites

  • 2 weeks later...

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.

Link to comment
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.

Link to comment
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.

Link to comment
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]
Link to comment
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]
Link to comment
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.

Link to comment
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.

Link to comment
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]
Link to comment
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.

Link to comment
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.
 

Link to comment
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.

Link to comment
Share on other sites

  • 4 months later...

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.

Link to comment
Share on other sites

  • 1 month later...

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/

Link to comment
Share on other sites

  • 1 month later...

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.

Link to comment
Share on other sites

  • 2 weeks later...

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?

Link to comment
Share on other sites

Archived

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




×
×
  • Create New...