Jump to content

Recommended Posts

This worked BEAUTIFULLY!!!  I've tested your code on 6.2.1+ dirtybutterestates and my 6.2.1+ plush test sites, AND even my live 6.1.15 plushcatalog store. It works perfectly on all of them using php 7.2.7, Havenswift's latest php version available.

THANK YOU SO MUCH BSMITHER!!!

Link to post
Share on other sites
  • 10 months later...

From what I can tell, this only seems to be working as an AND search. If I try "robot OR dog" what I'm getting is "robot AND dog".

Can this be modified to work with AND, OR and NOT operators in the search, eg. robot AND dog, robot OR dog and robot NOT dog?

Or a complex search like: robot red dog OR cat NOT plush - which would be treated as ((robot AND red) AND (dog OR cat)) NOT plush.

Or even: robot dog OR grumpy cat NOT plush NOT small - ((robot AND dog) OR (grumpy AND cat)) NOT plush AND NOT small

The purpose would be to show filtered search results only with no unwanted items.

---

I'm sure I've posted similar long ago (and working code for 6.0.12), but can't see any of my comments in the forums before 2016. I can only glimpse a cached version in Google search results. In any case, having this working for 6.2.5 would be great.

---

I'm also seeing errors like these in the System Error Log:

File: [catalogue.class.php] Line: [1897] "SELECT COUNT(I.product_id) as count, MATCH (I.product_code,I.name,I.description) AGAINST('2019 DIARIES' 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) AGAINST('2019 DIARIES' IN BOOLEAN MODE)) >= 0.5 AND AND `live_from` < UNIX_TIMESTAMP() AND `live_from` < UNIX_TIMESTAMP() GROUP BY I.product_id ORDER BY `name` ASC " - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND `live_from` < UNIX_TIMESTAMP() AND `live_from` < UNIX_TIMESTAMP() GROUP BY' at line 1

File: [catalogue.class.php] Line: [1997] "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 AND `live_from` < UNIX_TIMESTAMP() AND `live_from` < UNIX_TIMESTAMP() AND ((I.name RLIKE '[[:<:]]2019[[:>:]]' AND I.name RLIKE '[[:<:]]DIARIES[[:>:]]') OR (I.product_code RLIKE '[[:<:]]2019[[:>:]]' AND I.product_code RLIKE '[[:<:]]DIARIES[[:>:]]') OR (I.seo_meta_keywords RLIKE '[[:<:]]2019[[:>:]]' AND I.seo_meta_keywords RLIKE '[[:<:]]DIARIES[[:>:]]') OR (I.description RLIKE '[[:<:]]2019[[:>:]]' AND I.description RLIKE '[[:<:]]DIARIES[[:>:]]')) ORDER BY `name` ASC LIMIT 18 OFFSET 0 " - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'AND `live_from` < UNIX_TIMESTAMP() AND `live_from` < UNIX_TIMESTAMP() AND ((I.' at line 1

So I searched for this line:

                    $query = $q2.' '.$order_string.' '.$limit;

And added this before it:

                    $q2 = str_replace("AND AND","AND", $q2); // fix AND AND

 

Edited by jasehead
Link to post
Share on other sites

Please try this experiment:

In CC625, /classes/class.catalogue.php, near line 1888, find:

$words = $GLOBALS['db']->sqlSafe($words);

Change to:

$words = $GLOBALS['db']->sqlSafe($search_data['keywords']); // ($words);

This is only an experiment.

The statement just prior to the edited statement assigns $words with a string having stripped out all modifiers from $search_data['keywords'], thus destroying the visitor's intent.

The edited statement now does not use $words, but rather the original search phrase as is or expanded.

A search phrase such as:
robot grumpy should get all high semantic* records that have *robot* and *grumpy*
+robot -grumpy should get all high semantic* records that have 'robot' and not have 'grumpy'

* High Semantic: specialized or unique words, if all your products are DVDs, then searching for DVD is a worthless word. The word Hamlet is a very unique word and has high semantic worth. Likewise, Plush is worthless, Cuddly is perhaps just as bad, but Googly (as in Googly Eyes) has high worth (assuming your inventory has a small number of items with googly eyes).

* Asterisk extenders: *robot* is extended to be any word with robot contained within it -- roboto, robots, robotics.

More could be done with this. An issue has been posted in the Github.

Your error is posted in the Github #2279.

Link to post
Share on other sites

So, would that work for OR as well? In the 2018 changes above, OR still resulted in AND.

If I make the 2019 changes above and I have links or adwords set to use AND, OR and NOT in searches, are they going to need to be changed to +/- or can this be handled in the search code?

In my old version, I could use AND, OR and NOT to varying degrees.  My search worked on parts of words, so I could filter results for a particular product code prefix, eg. PB, and then for an item name, then remove any unwanted results.

So right now, I could search for:

2019 diaries NOT bronte

which would bring up all remaining 2019 diaries but keep out the one journal which has text in the description that includes 2019 and that the Bronte sisters used to keep diaries.This is a section of old code (messy, and probably based on 6.0.12 or earlier) where you should see that AND OR and NOT were picked out using $usSearchTerm, and str_replace was used on the search string to cope with NOT / AND NOT. 

So maybe something similar in your 2018 code around:

$like = ' AND ('.implode(' OR ', $regexp).' OR '.implode(' OR ', $regexp_desc).')';

			        $noKeys = count($searchArray);
					for ($i=0; $i<$noKeys; ++$i) {
						$ucSearchTerm = strtoupper($searchArray[$i]);
						if ( ($ucSearchTerm != 'AND') && ($ucSearchTerm != 'OR') && ($ucSearchTerm != 'NOT') && (strlen($ucSearchTerm) > 0) ) {
							$searchArray[$i] = str_replace(array('%','_'), array('\%','\_'), $searchArray[$i]);
							$regexp = '%'.$searchArray[$i].'%';
							$rlike .= "(I.name LIKE '".$regexp."' OR I.description LIKE '".$regexp."' OR I.product_code LIKE '".$regexp."')"; // modified?
							$rlike = str_replace(array(')(',') NOT ('), array(') AND (',') AND NOT ('), $rlike); // add AND between words and cope with NOT
//							if($i+1 < $noKeys){$rlike .= " AND ";} // add AND between words
						} else {
							if(strlen($ucSearchTerm) > 0){
              					$rlike .= " ".$ucSearchTerm." ";
                			}
		                continue;
            			}
        			}
			        $rlike .= " )";

In your 2019 code, if +robot -grumpy was a way to search, then robot NOT grumpy should also be acceptable/compatible.

Edited by jasehead
Link to post
Share on other sites

What should be doesn't work with computer programs. They do exactly what the code says it wants the computer to do -- even with the bugs.

The + and - modifiers are part of the query syntax for searches using the database's "FullText" methodology. The AND, OR, and NOT modifiers are part of the query syntax for searches using the "Regular Expression" methodology. The experiment above applied to only the FullText method.

Currently, there is a bug (in my opinion) with the FullText method, and the RegEx method is too simple.

Is the "2018 code" findable on the forums?

Link to post
Share on other sites
  • 1 year later...

This was the last 100% working version of your special search code I've been using Bsmither, which worked completely correctly on 6.2.8.

When CC added the choices in Advanced Search for different ways to include Manufacturer in the search - I stopped being able to search for manufacturer. So I temporarily commented out the customer's ability to search by manufacturer and never got back to it. I would like to remedy that now if possible.

I want to use the "chosen" way of finding the manufacturer:

{include file='templates/element.search.manufacturers.select.chosen.php'}

It does not find anything when I search and I get this error messages:

File: [catalogue.class.php] Line: [2014] "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 I.manufacturer IN (11) AND `live_from` < UNIX_TIMESTAMP() AND ((I.name RLIKE '[[:<:]]tin[[:>:]]') OR (I.product_code RLIKE '[[:<:]]tin[[:>:]]') OR (I.search_keywords RLIKE '[[:<:]]tin[[:>:]]') OR (I.description RLIKE '[[:<:]]tin[[:>:]]')) ORDER BY `name` ASC LIMIT 12 OFFSET 0 " - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'I.manufacturer IN (11) AND `live_from` < UNIX_TIMESTAMP() AND ((I.name RLIKE '[' at line 1

File: [catalogue.class.php] Line: [2016] "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 I.manufacturer IN (11) AND `live_from` < UNIX_TIMESTAMP() AND ((I.name RLIKE '[[:<:]]tin[[:>:]]') OR (I.product_code RLIKE '[[:<:]]tin[[:>:]]') OR (I.search_keywords RLIKE '[[:<:]]tin[[:>:]]') OR (I.description RLIKE '[[:<:]]tin[[:>:]]')) " - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'I.manufacturer IN (11) AND `live_from` < UNIX_TIMESTAMP() AND ((I.name RLIKE '[' at line 1

 

I had tried adding I.manufacturer to the array of search terms in catalog.class.php, but it did not fix it.

						$columnsToSearch = array('I.name','I.product_code','I.search_keywords','I.manufacturer',); // columns where contents are plain words
						$columnsToSearch_desc = array('I.description','I.manufacturer',); // columns where contents can be rich text

 

Link to post
Share on other sites

In searchCatalogue(), near line 1795:

Find code that looks like:
// Manufacturer
if (isset($search_data['manufacturer']) && is_array($search_data['manufacturer']) && count($search_data['manufacturer'])>0) {
    $where[] = 'I.manufacturer IN ('.implode(',', $this->get_int_array($search_data['manufacturer'])).')';
    //    $where[] = 'I.manufacturer IN ('.implode(',', '\''.$search_data['manufacturer']).'\')';
}

Change to:
// Manufacturer
if (isset($search_data['manufacturer']) && is_array($search_data['manufacturer']) && count($search_data['manufacturer'])>0) {
    $where[] = 'AND I.manufacturer IN ('.implode(',', $this->get_int_array($search_data['manufacturer'])).')';
    //    $where[] = 'I.manufacturer IN ('.implode(',', '\''.$search_data['manufacturer']).'\')';
}

(An issue has been posted in the Github. Let us know if this fixes the issue for you.)

  • Like 1
Link to post
Share on other sites

THANK YOU! Such a little word "and". I really appreciate the time you spend helping so many here. Going to test it now with the 6.4.0 code and will report, but I don't expect any problem there now.

I can verify that your code edit has fixed the 6.4.0 current commit as well. 🤩

Edited by Dirty Butter
  • Like 1
Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...