Dirty Butter Posted May 2, 2016 Share Posted May 2, 2016 I add common spelling variations to my keywords that I would not want to put in the description for ALL our listings - things like dandee,dan dee, - even misspelled words. Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 2, 2016 Share Posted May 2, 2016 Adding `seo_meta_keywords` to the list, as well as replacing `cat_desc`, should work. Quote Link to comment Share on other sites More sharing options...
Dr. Gonzo Posted May 2, 2016 Author Share Posted May 2, 2016 YES! I THINK IT WORKS!!! I will run some more tests but we are definitely on the right track, the search seems to return a product under the category name so far when you query the category name. Thanks bsmither! Quote Link to comment Share on other sites More sharing options...
Dr. Gonzo Posted May 2, 2016 Author Share Posted May 2, 2016 Bummer. Looks like it is not hitting on anything in the category table's 'seo_meta_keywords' field even if I use the exact syntax of a keyword I entered. Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 2, 2016 Share Posted May 2, 2016 To test: In admin, Store Settings, Advanced tab, enable debug mode and enter your computer's IP address (www.whatismyip.com) in the nearby field. Execute the search using a keyword. There will be a grey section below the page. Look for a long query in a red color. It will be about the 24th query and it will start with something similar to the following:SELECT I.*, MATCH (I.product_code,I.description,I.name) AGAINST Make sure the list of columns in MATCH includes these original three as well as any others you added when you altered the database table. Quote Link to comment Share on other sites More sharing options...
Dr. Gonzo Posted May 2, 2016 Author Share Posted May 2, 2016 I do not seem to have any string containing the words "MATCH" in my debug output. Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 2, 2016 Share Posted May 2, 2016 In the debug section, at about the 24th query, what are the long queries in red that begin with SELECT I.*? (Use the Code feature of this editor to paste in the queries.) Quote Link to comment Share on other sites More sharing options...
Dr. Gonzo Posted May 2, 2016 Author Share Posted May 2, 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 AND ((I.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) LIMIT 12 OFFSET 0 -- (0.0041220188140869 sec) [CACHE WRITE] [14] 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.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) -- (0.01549506187439 sec) [CACHE WRITE] Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 2, 2016 Share Posted May 2, 2016 These queries are when CubeCart takes an alternate approach to having MySQL perform a search. But, I see that this part: ((I.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) has no search terms inside the [[::]] sequence. That's not good. It tells me that an edit to the code got messed up, or the browser isn't sending any terms to give to the search query. This alternate approach is used when the primary approach fails to find anything. And if the fact is that search terms are not being given to the code to use to search with, that would understandable. But, the primary search approach would have its query listed in the debug section. Unless.... When using the primary search approach, there is a limit to the search word length that must be met. (I do not know what that is - I think it is database server dependent. Assume it is four letters.) There must be at least one word that has the number of characters at or above the minimum search word length. Probably the database server will ignore any word(s) that are below this length. The alternate approach is used if there is no word with the number of characters required. Quote Link to comment Share on other sites More sharing options...
Dr. Gonzo Posted May 2, 2016 Author Share Posted May 2, 2016 Ok found out what was causing the sequence to be blank. Turns out this line of code: public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'fulltext') { had been changed to: public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'like') { during the modifications for the product table's seo_meta_keywords search and once I changed it back to 'fulltext', the sequence now shows my keywords. However we are back in the same boat we were in before with the code not querying the category table's 'seo_meta_keywords' field. Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 3, 2016 Share Posted May 3, 2016 So I think you are saying that examining the queries listed in the grey debug section, you find the query that starts with:SELECT I.*, MATCH (I.product_code,I.description,I.name) AGAINST but should also have (maybe I.seo_meta_keywords along with) C.cat_name,C.cat_desc,C.seo_meta_keywords as well? What columns are listed in the MATCH group? Quote Link to comment Share on other sites More sharing options...
Dr. Gonzo Posted May 3, 2016 Author Share Posted May 3, 2016 This is the log from searching and generating a hit on product name: [13] SELECT I.*, MATCH (I.product_code,I.description,I.name) AGAINST('10amp' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS C, 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('10amp' IN BOOLEAN MODE)) >= 0.5 ORDER BY Relevance DESC LIMIT 12 OFFSET 0 -- (0.0097630023956299 sec) [CACHE WRITE] [14] SELECT COUNT(I.product_id) as count, MATCH (I.product_code,I.description,I.name) AGAINST('10amp' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS C, 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('10amp' IN BOOLEAN MODE)) >= 0.5 GROUP BY I.product_id ORDER BY Relevance DESC -- (0.0014050006866455 sec) [CACHE WRITE] And this is the log from searching for a keyword under a product's seo_meta_keywords field(notice it generated a hit but is missing the keyword in the routine). [22] 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.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) LIMIT 12 OFFSET 0 -- (0.0097780227661133 sec) [CACHE WRITE] [23] 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.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) -- (0.010262012481689 sec) [CACHE WRITE] And I unfortunately do not see any reference to the c.cat_name or c.seo_meta_keywords fields I defined under the custom sql earlier in the post. I also notice something at the top of the debug log where it gives a PhP notice: "classes/cubecart.class.php:746 - Undefined index: search" when I search on a term used in a product name and a similar notice and an additional notice when searching on something that generates a hit on a seo_meta_keywords term: "classes/catalogue.class.php:1542 - Undefined variable: order" Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 3, 2016 Share Posted May 3, 2016 This: MATCH (I.product_code,I.description,I.name) tells me the ALTER statement that adds a FULLTEXT index to the CubeCart_category table did not take, or this edit in catalogue.class.php was not performed: Find: $indexes = $GLOBALS['db']->getFulltextIndex('CubeCart_inventory', 'I'); Add after: $indexes = array_merge($indexes,$GLOBALS['db']->getFulltextIndex('CubeCart_category', 'C')); In the debug section, just above the queries numbered 13 and 14, you should also see: SHOW INDEX FROM `CubeCart_inventory`; -- (0 sec) [CACHE WRITE] SHOW INDEX FROM `CubeCart_category`; -- (0 sec) [CACHE WRITE] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0 sec) [CACHE WRITE] If the one for Cubecart_category is not there, then the edit above was not done. You MUST use an external database utility, such as phpMyAdmin, to make a change to the existing FULLTEXT index on Cubecart_inventory. It is not possible to ALTER an existing INDEX. The utility knows this and will ALTER the table by first DROPping the index, then ADDing the index back with the new columns to include. Quote Link to comment Share on other sites More sharing options...
Dr. Gonzo Posted May 3, 2016 Author Share Posted May 3, 2016 I went in to phpMyAdmin and attempted to alter the existing index and received the error that the used table type does not support FULLTEXT indexes. So maybe that explains why the advanced option did not allow that sql command to go through. Ok update on search. I have the code for $indexes you laid out for the catalogue.class.php file, the table has been set to a myISAM engine and has allowed me to specify fulltext indexing which I did directly through myPhPAdmin, and the debug still shows this: [19] SHOW INDEX FROM `CubeCart_inventory`; -- (0.001507043838501 sec) [CACHE READ] [20] SHOW INDEX FROM `CubeCart_category`; -- (0.001507043838501 sec) [CACHE READ] [21] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.001507043838501 sec) [CACHE READ] [22] 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.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) LIMIT 12 OFFSET 0 -- (0.0090909004211426 sec) [CACHE WRITE] [23] 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.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) -- (0.0072259902954102 sec) [CACHE WRITE] Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 3, 2016 Share Posted May 3, 2016 We're back to the case where CubeCart isn't getting any words to search on. Find this code in catalogue.class.php: if ($search_mode == 'fulltext' && $search_str_len >= $max_word_len) { switch (true) { case (preg_match('#[\+\-\>\<][\w]+#iu', $search_data['keywords'])): Add the following ABOVE it: $GLOBALS['debug']->debugMessage('The words to search are: '.$search_data['keywords']); $GLOBALS['debug']->debugMessage('The $search_mode is: '.$search_mode); $GLOBALS['debug']->debugMessage('The $search_str_len is: '.$search_str_len); $GLOBALS['debug']->debugMessage('The $max_word_len is: '.$max_word_len); When you next make a search, the debug section at the bottom of the web page should have a sub-section called Debug Messages. Those entries should give us a clue why CubeCart is jumping to the alternate search method. Quote Link to comment Share on other sites More sharing options...
Dr. Gonzo Posted May 3, 2016 Author Share Posted May 3, 2016 Here is the debug message section: [0] The words to search are: 10ah [1] The $search_mode is: fulltext [2] The $search_str_len is: 4 [3] The $max_word_len is: 4 Weird thing is, now all those MATCH statements show my keywords AND show the c.cat_name, c.cat_desc, c.seo_meta_keywords we were looking for: [14] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1603] \"SELECT I.*, MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS C, 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,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE)) >= 0.5 ORDER BY Relevance DESC LIMIT 12 OFFSET 0 \" - Unknown column \'C.cat_name\' in \'field list\'','1462310409'); -- (0.00051498413085938 sec) [ERROR - NOT CACHED] [15] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1603] \"SELECT I.*, MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS C, 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,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE)) >= 0.5 ORDER BY Relevance DESC LIMIT 12 OFFSET 0 \" - Unknown column \'C.cat_name\' in \'field list\'','1462310409'); -- (0.00051498413085938 sec) [ERROR - CACHE WRITE] [ 16] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1609] \"SELECT COUNT(I.product_id) as count, MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS C, 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,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE)) >= 0.5 GROUP BY I.product_id ORDER BY Relevance DESC \" - Unknown column \'C.cat_name\' in \'field list\'','1462310409'); -- (0.00062894821166992 sec) [ERROR - NOT CACHED] [17] INSERT INTO `CubeCart_system_error_log` (`message`,`time`) VALUES ('File: [catalogue.class.php] Line: [1609] \"SELECT COUNT(I.product_id) as count, MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE) AS Relevance FROM CubeCart_inventory AS C, 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,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST(\'10ah\' IN BOOLEAN MODE)) >= 0.5 GROUP BY I.product_id ORDER BY Relevance DESC \" - Unknown column \'C.cat_name\' in \'field list\'','1462310409'); -- (0.00062894821166992 sec) [ERROR - CACHE WRITE] Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 3, 2016 Share Posted May 3, 2016 Did you also make this edit in catalogue.class.php: // Score matching string $match = sprintf("MATCH (%s) AGAINST('%s' %s)", implode(',', $indexes), $words, $mode); $match_val = '0.5'; /* EDITED! */ $query = sprintf("SELECT I.*, %2\$s AS Relevance FROM %1\$sCubeCart_category AS C, %1\$sCubeCart_inventory AS I LEFT JOIN (SELECT product_id, MAX(price) as price, MAX(sale_price) as sale_price FROM %1\$sCubeCart_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 `%1\$sCubeCart_category_index` as CI INNER JOIN %1\$sCubeCart_category as C where CI.cat_id = C.cat_id AND C.status = 1) AND I.status = 1 AND (%2\$s) >= %4\$s %3\$s %5\$s %6\$s", $GLOBALS['config']->get('config', 'dbprefix'), $match, $whereString, $match_val, $order_string, $limit); /* This part in $query above: %1\$sCubeCart_category AS C, is NEW! */ if ($search = $GLOBALS['db']->query($query)) { The $query statement has been edited to include the CubeCart_category table. What I see in the queries is:FROM CubeCart_inventory AS C, It needs to be:FROM CubeCart_category AS C, So, you have an edit to fix in two places. Quote Link to comment Share on other sites More sharing options...
Dr. Gonzo Posted May 3, 2016 Author Share Posted May 3, 2016 Ahhhh, stupid me. Ok after the change the search is a little slow but it does return not only a hit on the seo_meta_keywords from a product, but also from category as well. I also notice that it is returning irrelevant items that do not contain the keyword I specified unless I go back to 'like' instead of 'fulltext' at this line: public function searchCatalogue($search_data = null, $page = 1, $per_page = 10, $search_mode = 'like') { $per_page = (!is_numeric($per_page) || $per_page < 1) ? 10 : $per_page; $original_search_data = $search_data; not to mention the search is sped up substantially. But if I do this, it is now not generating hits on the seo_meta_keywords field for categories but still returning hits from seo_meta_keywords for products. Oh, I noticed why in the code. If $search_mode is 'like' then it bypasses that code we added to include the category fields. Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 4, 2016 Share Posted May 4, 2016 The search is a little slow, eh? Look at the red queries again. The ones with MATCH in them. At the end of the query is the amount of time it took to send the query to database, the time it took for the database to assemble a response, and send that response back to CubeCart. I believe the most time consuming portion of CubeCart's page Request/Response cycle is the template rendering engine. Therefore, it is important that you enable the cache in admin, Store Settings, Advanced tab. Returning irrelevant items, eh? That's the sucky part of MySQL's "Relevance" MATCH mode. The MySQl search algorithm details are gory and, if the amount of data to search through is sparse, the results are not what you may expect. Once you've got a thousand products (and now that you've included the category table, once you've got a thousand categories), the MySQl MATCH algorithm works much, much better. Quote Link to comment Share on other sites More sharing options...
Dr. Gonzo Posted May 4, 2016 Author Share Posted May 4, 2016 I do have caching enabled under the advanced tab. I noticed this code here(I remarked the original to add the seo_meta_keywords code update from Dirty Butter's thread on the subject): /*$rlike = ''; if (!empty($search_data['keywords'])) { $searchwords = preg_split( '/[\s,]+/', $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); **** Changed to search product meta keywords $rlike = " AND (I.name RLIKE '".$regexp."' OR I.description RLIKE '".$regexp."' OR I.product_code RLIKE '".$regexp."')"; }*/ $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', '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; Is it possible to alter this SELECT statement here in this code for } else { 'like' to query the category data? If so that would not only increase the responsiveness of the search but also return hits from category seo_meta_keywords. At the moment there is no reference to that table for this part of the code. Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 4, 2016 Share Posted May 4, 2016 I would think so, but figuring out the SQL would be interesting. Quote Link to comment Share on other sites More sharing options...
Dr. Gonzo Posted May 4, 2016 Author Share Posted May 4, 2016 Ya, I took a whack at it by adding CubeCart_category as C, right before CubeCart_inventory AS I LEFT JOIN ( with no luck, the page fails to load and comes back blank. I appear to be back in the same boat though with not being able to generate a proper hit on seo_meta_keywords for categories. Some peculiar things I am noticing while working with the code thus far: I notice I get an error at the top of the debug log telling me that $order is an undefined variable if I use 'like' (and I believe this may be causing the blanks in the query we were seeing earlier) and I notice it's when RLIKE is used. If I get a hit on part of a product name, I get the same product listed over and over again when using 'fulltext' search (almost like a loop). I get the same irrelevant products returned if I search using a known term I specified under a category or product seo_meta_keywords field. I get nothing returned if I use a keyword that is nowhere to be found in any product or category fields (which I would expect) but notice the blank query when using RLIKE: [12] SHOW INDEX FROM `CubeCart_inventory`; -- (0.00047087669372559 sec) [CACHE WRITE] [13] SHOW INDEX FROM `CubeCart_category`; -- (0.00048589706420898 sec) [CACHE WRITE] [14] SHOW VARIABLES LIKE 'ft_min_word_len' -- (0.00055718421936035 sec) [CACHE WRITE] [15] SELECT I.*, MATCH (I.product_code,I.description,I.name,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST('testing4' IN BOOLEAN MODE) AS Relevance FROM CubeCart_category AS C, 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,C.cat_name,C.cat_desc,C.seo_meta_keywords) AGAINST('testing4' IN BOOLEAN MODE)) >= 0.5 ORDER BY Relevance DESC LIMIT 12 OFFSET 0 -- (9.3560028076172 sec) [CACHE WRITE] [16] SHOW INDEX FROM `CubeCart_inventory`; -- (9.3560028076172 sec) [CACHE READ] [17] SHOW INDEX FROM `CubeCart_category`; -- (9.3560028076172 sec) [CACHE READ] [18] SHOW VARIABLES LIKE 'ft_min_word_len' -- (9.3560028076172 sec) [CACHE READ] [19] 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.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) LIMIT 12 OFFSET 0 -- (0.0071730613708496 sec) [CACHE WRITE] [20] 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.name RLIKE '[[::]]') OR (I.description RLIKE '[[::]]') OR (I.product_code RLIKE '[[::]]') OR (I.seo_meta_keywords RLIKE '[[::]]')) -- (0.0065910816192627 sec) [CACHE WRITE] Debug Messages: [0] The words to search are: testing4 [1] The $search_mode is: fulltext [2] The $search_str_len is: 8 [3] The $max_word_len is: 4 [4] The words to search are: testing4 [5] The $search_mode is: like [6] The $search_str_len is: 8 [7] The $max_word_len is: 4 Quote Link to comment Share on other sites More sharing options...
bsmither Posted May 4, 2016 Share Posted May 4, 2016 Notices can be ignored: $order is an undefined variable This helps programmers when writing the code that $order has not been seen before, and thus may be misspelled, etc. CubeCart follows the school of programming where this is of no concern. Quote Link to comment Share on other sites More sharing options...
Dr. Gonzo Posted May 5, 2016 Author Share Posted May 5, 2016 Ok thats good to know for future reference. I am still in the same boat where I cannot return search results from category fields however. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.