Jump to content
Sign in to follow this  
Dirty Butter

PHP 7.2.5

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!!!

Share this post


Link to post
Share on other sites

Posted (edited)

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

Share this post


Link to post
Share on other sites

I think CubeCart's parsing of the search phrase and construction of the query is not that sophisticated.

Share this post


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.

Share this post


Link to post
Share on other sites
Posted (edited)

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

Share this post


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?

Share this post


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.

Sign in to follow this  

×
×
  • Create New...