Code Check: Two or More Search Terms Builds Bad Queries

[bhsmither]

For RLIKE and LIKE search modes looking for founda foundb, the respective queries contain:

RLIKE '\\bfounda\\b\\bfoundb\\b'
LIKE '%founda%%foundb%'

Note that the word boundary assertion /b does not consume any characters in the string. Thus, a query such as:

SELECT 'pixel permutations'
RLIKE '\\bpixel\\b\\bpermutations\\b'

will not match because the space is not accounted for.

But this will match, so long as the searched terms are in the same order:

SELECT 'pixel packing permutations'
RLIKE '\\bpixel\\b.*\\bpermutations\\b'

[bhsmither]

This is my experiment in class.catalogue.php:

Line 2048, from:

$like = '';

To:

$like = array();

Line 2079, from:

$regexp .= $like_prefix.$searchArray[$i].$like_postfix;

To:

$regexp = $like_prefix.$searchArray[$i].$like_postfix;

Line 2080, from:

$regexp_desc .= $like_prefix.htmlentities(html_entity_decode($searchArray[$i], ENT_COMPAT, 'UTF-8'), ENT_QUOTES, 'UTF-8', false).$like_postfix;

To:

$regexp_desc = $like_prefix.htmlentities(html_entity_decode($searchArray[$i], ENT_COMPAT, 'UTF-8'), ENT_QUOTES, 'UTF-8', false).$like_postfix;

Line 2082, from:

}

To:

// } This line will be removed after experimenting

Line 2087-2090, from:

  $like = " AND (I.name ".$like_keyword." '".$regexp."' OR I.description ".$like_keyword." '".$regexp_desc."' OR I.product_code ".$like_keyword." '".$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.$like;

To:

  $like[$i] = "(I.name ".$like_keyword." '".$regexp."' OR I.description ".$like_keyword." '".$regexp_desc."' OR I.product_code ".$like_keyword." '".$regexp."')";
  } // This line will become permanent after experimenting
}
$likeString = ' AND ('.implode(' OR ',$like).')';
$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.$likeString;

[abrookbanks]

I changed the formatting to make it easier to read. Thanks for this.

[abrookbanks]

This works flawlessly for me. Well done.