SQL Again!

[jmarcv]

Well, that stinks. It SHOULD work, but.....

Heres a workaround:

 ...where concat(' ',products_name,' ') REGEXP '[^a-zA-Z]+red[^a-zA-Z]+';

[andychev]

That works like a dream. I should really share with everyone exactly what has been acheived.

The problem that has just been solved is one that im not aware through searching the internet, oscommerce forums, updates, bugs etc has been solved before. Apologies if i have missed a solution by someone.

It is a problem that many people have faced on oscommerce whereby the search results returned are incorrect as the algorithm also matches partital words, ie red in coloured or rings in strings. There was also a problem with AND and OR and "" not working as they should and as described in the search help. This was corrected in an earlier fix but this fix didnt correct the problem of partial words which the following code now does.

In catalog/advanced_search_result.php look for the following around line 350

if (isset($search_keywords) && (sizeof($search_keywords) > 0)) {
$where_str .= " and (";
for ($i=0, $n=sizeof($search_keywords); $i<$n; $i++ ) {
switch ($search_keywords[$i]) {
case '(':
case ')':
case 'and':
case 'or':
$where_str .= " " . $search_keywords[$i] . " ";
break;
default:
$keyword = tep_db_prepare_input($search_keywords[$i]);
$where_str .= "(pd.products_name like '%" . tep_db_input($keyword) . "%' or p.products_model like '%" . tep_db_input($keyword) . "%' or m.manufacturers_name like '%" . tep_db_input($keyword) . "%'";
if (isset($HTTP_GET_VARS['search_in_description']) && ($HTTP_GET_VARS['search_in_description'] == '1')) $where_str .= " or pd.products_description like '%" . tep_db_input($keyword) . "%'";
$where_str .= ')';
break;
}
}
$where_str .= " )";
}


Replace the code with the following:

if ($HTTP_GET_VARS['keywords'])
{
if (tep_parse_search_string( StripSlashes($HTTP_GET_VARS['keywords']), $search_keywords))
{
// MODIFIED 1-30-2002 BY M.BROWNING
// MODIFIED 6-02-2007 BY JMARCV of GLOWHOST and ANDYCHEV of LEWCY
// COMPLETE REWRITE OF SEARCH COMPILATION ALGORITHM. IT WAS TOTALLY BROKEN.
// REGARDLESS OF WHICH CONDITION (AND / OR) WAS SET AS THE DEFAULT CONCATENATION
// OPERATOR. In Prior algorithm, if one word appeared in Product Name, and another
// in Product Model, then it met the condition. WHAT was REALLY MEANT TO HAPPEN WAS
// that ALL keywords must appear in Name or all in Description (etc) for the
// criteria to be satisfied.
// Modified 6-2-07 to update algorithm so that partial words are not matched
$strProdNameSearch = '';
$strProdModelSearch = '';
$strProdDescSearch = '';
$strMfgSearch = '';
for ($i=0; $i < sizeof($search_keywords); $i++)
{
switch ($search_keywords[$i])
{
case '(':
case ')':
case 'and':
case 'or':
{
$strProdNameSearch .= " $search_keywords[$i] ";
$strProdModelSearch .= " $search_keywords[$i] ";
$strProdDescSearch .= " $search_keywords[$i] ";
$strMfgSearch .= " $search_keywords[$i] ";
break;
}
default:
{
$strProdNameSearch .= " concat(' ',pd.products_name,' ') REGEXP '[^a-zA-Z]+$search_keywords[$i][^a-zA-Z]+' ";
$strProdModelSearch .= " concat(' ',p.products_model,' ') REGEXP '[^a-zA-Z]+$search_keywords[$i][^a-zA-Z]+' ";
$strProdDescSearch .= " concat(' ',m.manufacturers_name,' ') REGEXP '[^a-zA-Z]+$search_keywords[$i][^a-zA-Z]+' ";
$strMfgSearch .= " concat(' ',pd.products_description,' ') REGEXP '[^a-zA-Z]+$search_keywords[$i][^a-zA-Z]+' ";
break;
}
}
}
$where_str .= " AND ( ($strProdNameSearch) OR ($strProdModelSearch)"
. " OR ($strProdDescSearch) OR ($strMfgSearch) ) ";
}
}

[jmarcv]

Hmmmmm, did I release that to the public domain?
Not to complicate issues, but I thought you might be interested.
REGEXP has an 'or' function (but not an 'and' - bummer -). You can separate each search item with a | (pipe) symbol.

So rather than looping through the array and creating multiple REGEXP you can create it in one shot outside of your array loop like so:

 $strProdNameSearch = " concat(' ',pd.products_name,' ') REGEXP '[^a-zA-Z]+".join('[^a-zA-Z]+|[^a-zA-Z]+',$search_keywords)."[^a-zA-Z]+'  "; 
  

This will enhance the speed on searches with numerous search items.

Also of note, while I took the easy way out:
\b allows you to perform a "whole words only" search using a regular expression in the form of \bword\b.

So, I think

 REGEXP '[^a-zA-Z]+$search_keywords[$i][^a-zA-Z]+' 
  

could also be written as

 REGEXP '\b$search_keywords[$i]\b'
 
$strProdNameSearch = " concat(' ',pd.products_name,' ') REGEXP '\b+".join('\b|\b',$search_keywords)."\b'  "; 
  

[andychev]

Hmmmmm, did I release that to the public domain?

Maybe this thread should be password'd and access given after a small paypal donation which will of course will go to a worthy cause