Topic: Advanced Search

This may have already been done, but I wanted to implement an advanced search that would search for all of the keywords in whatever order they appear as individual words, not just simply as a phrase.

For example, currently, if you entered "red ball", you would only get matches that matched that exact phrase.  An item that had both those two words in a field, but maybe as "ball red" or "the ball was red" would not appear.  The following code adds searches for all items that contain both words, in whatever order they appear.

In browse.ihtml, find this group of lines (under the KEYWORD section of the IF statement...):

     $q  = "(product.product_name LIKE '%$keyword%' OR ";
     $q .= "product.product_sku LIKE '%$keyword%' OR ";
     $q .= "product.product_s_desc LIKE '%$keyword%' OR ";
     $q .= "category.category_name LIKE '%$keyword%' OR ";
     $q .= "product.product_desc LIKE '%$keyword%'";
     $q .= ") ";

and change them to these lines:

$keyword_list = explode(" ", $keyword);
$keyword_count = count($keyword_list);
$field_list[0] = "product.product_name";
$field_list[1] = "product.product_sku";
$field_list[2] = "product.product_s_desc";
$field_list[3] = "product.product_desc";
$field_list[4] = "category.category_name";
$field_count = count($field_list);

$q = "(";
for($i = 0; $i < $field_count; $i++) {
  if ($i) $q .= " OR ";
  $q .= "(";
  for($j = 0; $j < $keyword_count; $j++) {
    if ($j) $q .= " AND ";
      $q .= $field_list[$i] . " LIKE '%" . $keyword_list[$j] . "%'";
      }
    $q .= ")";
    }
  $q .= ")";
  echo "q = " . $q . "<p>";

the first section sets up the lists of keywords to search for and the database fields to search in.  its set up so that you can add/remove fields from the list without having to document how many there are when you're done.  the second section creates the nested LIKE sql calls.

if "ball color red big found" were entered in the search field, it would generate this code:

((product.product_name LIKE '%ball%' AND product.product_name LIKE '%color%' AND product.product_name LIKE '%red%' AND product.product_name LIKE '%big%' AND product.product_name LIKE '%found%') OR (product.product_sku LIKE '%ball%' AND product.product_sku LIKE '%color%' AND product.product_sku LIKE '%red%' AND product.product_sku LIKE '%big%' AND product.product_sku LIKE '%found%') OR (product.product_s_desc LIKE '%ball%' AND product.product_s_desc LIKE '%color%' AND product.product_s_desc LIKE '%red%' AND product.product_s_desc LIKE '%big%' AND product.product_s_desc LIKE '%found%') OR (product.product_desc LIKE '%ball%' AND product.product_desc LIKE '%color%' AND product.product_desc LIKE '%red%' AND product.product_desc LIKE '%big%' AND product.product_desc LIKE '%found%') OR (category.category_name LIKE '%ball%' AND category.category_name LIKE '%color%' AND category.category_name LIKE '%red%' AND category.category_name LIKE '%big%' AND category.category_name LIKE '%found%')) ......

for a 5 keyword search on a database containing over 940 products, my system took less than a second to refresh the page with  one item found.