SQL Again!

[andychev]

I Managed a server that did a lot of REGEXP on datasets of up to 10,000 records withing a query set that had to be examined, with some 200-800 queries per second, so unless your even within 20% of that even on a shared server, I don't think it will be noticable.

I dont think it will come close to that.

The search is carried out in 4 fields in just under 550 records. It searches product name, manufaturer, description and model. It does this for each term that is entered into the search box either the exact term if it is in "" or by using AND or OR. I guess the only problem i could see is if someone maliciously decided to put a huge search term in

200-800 queries per second!! Blimey! Although thinking about it if you take all the queries into account on the site and the access database that links into the sql database i reckon i must be one at least 1 query per second! Whoop Whoop!

[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