[Tuner]

OK so this code is modified to do the following
1. function in 3.3.2
2. Substring search searches the following paramaters using funky's code
Param00
Product
Description
Detailed Desription
(ProductCode or sku#)
3.SKU# search searches ProductCode Only and searches it using Funky's code
4. Same as #3 only with the folowing paramater param00
5. Same as #3 only with the folowing paramater Product
6. Same as #3 only with the folowing paramater fulldescr
7. Added ability to search by brand using drop down list(see .tpl code)


You may see this at www.landspeedracing.com/customer/search.php
If anyone want me to mod this for 3.3.x for their uses I would be willing to help if it is not to much work.
Scott

<? 
require "../smarty.php"; 
require "../config.php"; 
@include "./https.php"; 
require "./auth.php"; 
require "../include/categories.php"; 

$tmp=strstr($QUERY_STRING, "XCARTSESSID=");
if (!empty($tmp)) $QUERY_STRING="";

if(!empty($QUERY_STRING)) {

# 
# Permorfm SQL search query 
# 
// LOOK HERE 
// new bit to split search string into separate words 
   #Substring Search
   $con = "";
   $and="AND"; 
   #if($substring and $search_det_description){$substring .= " $search_det_description";}else if(!$substring and $search_det_description){$substring = $search_det_description ;}else if($substring and !$search_det_description){$search_det_description = NULL;} else {$substring = NULL;$search_det_description = NULL;}
   if(!empty($substring)){ 
      $ss = split(" ",$substring); 
      foreach($ss as $s) 
          // nfc - we add support for other fields to search 
         $con[] = "(".$sql_tbl[products].".product like '%".$s."%'"." OR ". 
                  $sql_tbl[products].".fulldescr like '%".$s."%'"." OR ". 
                  $sql_tbl[products].".descr like '%".$s."%'"." OR ". 
		    $sql_tbl[products].".param00 like '%".$s."%'"." OR ". 
                  $sql_tbl[products].".productcode like '%".$s."%'".")"; 
   } 
   
   if(empty($and))$and = "OR"; 
   $substring_query = (!empty($con)) ? " AND (".join(" ".$and." ",$con).") " :""; 
   
   #Title Search
   $cont = "";
   $andt = "AND"; 
   if(!empty($search_product)){ 
      $sst = split(" ",$search_product); 
      foreach($sst as $st) 
         $cont[] = "(".$sql_tbl[products].".product like '%".$st."%'".")"; 
		 
   } 
    
   if(empty($andt))$andt = "OR"; 
   $product_query = (!empty($cont)) ? " AND (".join(" ".$andt." ",$cont).") " :"";
   
   #Descriptions Search
      $cond = "";
   $andd = "AND"; 
   if(!empty($search_det_description)){ 
      $ssd = split(" ",$search_det_description); 
      foreach($ssd as $sd) 
           $cond[] = "(".$sql_tbl[products].".fulldescr like '%".$sd."%'"." OR ". 
                  $sql_tbl[products].".fulldescr like '%".$sd."%'".")"; 
		 
   } 
    
   if(empty($andd))$andd = "OR"; 
   $descr_query = (!empty($cond)) ? " AND (".join(" ".$andd." ",$cond).") " :"";
   
   #Applications Search Param00
   $conp = "";
   $andp = "AND"; 
   if(!empty($search_param00)){ 
      $ssp = split(" ",$search_param00); 
      foreach($ssp as $sp) 
         $conp[] = "(".$sql_tbl[products].".param00 like '%".$sp."%'".")"; 
		 
   } 
    
   if(empty($andp))$andp = "OR"; 
   $param00_query = (!empty($conp)) ? " AND (".join(" ".$andp." ",$conp).") " :"";

// end of new bit 

   $price_condition = $price_search_1?" AND $sql_tbl[pricing].price>='$price_search_1'":""; 
   $price_condition .= $price_search_2?" AND $sql_tbl[pricing].price<='$price_search_2'":""; 

   $price_substring = $price_search_1?"&price_search_1=".urlencode($price_search_1):""; 
   $price_substring .= $price_search_2?"&price_search_2=".urlencode($price_search_2):""; 
   
   $brand_condition = ($search_brand==""?"":" and ($sql_tbl[products].brand regexp '$search_brand') ");

   $sku_condition = ($search_sku != '' ? " and $sql_tbl[products].productcode regexp '$search_sku' ": "");

   if ($price_condition) 
      $sort_by_price = "price"; 
    
   if ($in_category) { 
      $search_category = addslashes(array_pop(func_query_first("select category from $sql_tbl[categories] where categoryid='$in_category'"))); 
      $search_categories = func_query("select categoryid from $sql_tbl[categories] where $sql_tbl[categories].category like '$search_category%'"); 
      if(is_array($search_categories)) { 
         $category_condition=" in ( "; 
         foreach($search_categories as $k=>$v) 
            $category_condition .= "'$v[categoryid]', "; 
         $category_condition = ereg_replace(", $", ")", $category_condition); 
         $category_condition=" ($sql_tbl[products].categoryid $category_condition or $sql_tbl[products].categoryid1 $category_condition or $sql_tbl[products].categoryid2 $category_condition or $sql_tbl[products].categoryid3 $category_condition) "; 
      } 
   } 
   else 
      $category_condition = "1"; 
    
   $membership_condition = " AND ($sql_tbl[categories].membership='". $user_account['membership']."' OR $sql_tbl[categories].membership='') "; 

   if ($store_language != $config["default_customer_language"] && $substring) { 
        // LOOK HERE 
   // nfc - always using default language so this case will never happen 
   } 
   else { 
      $search_query_count = "select count(*) from $sql_tbl[products], $sql_tbl[pricing], $sql_tbl[categories] where $sql_tbl[pricing].productid=$sql_tbl[products].productid AND $sql_tbl[pricing].quantity=1 AND $sql_tbl[products].categoryid=$sql_tbl[categories].categoryid $membership_condition AND ($sql_tbl[pricing].membership='". $user_account['membership']."' or $sql_tbl[pricing].membership='') AND $category_condition AND $sql_tbl[products].forsale='Y' AND $sql_tbl[categories].avail='Y' $brand_condition $sku_condition $price_condition $param00_query $descr_query $product_query $substring_query Group By $sql_tbl[products].productcode"; 
   } 
    
   $search_query = "$category_condition and $sql_tbl[categories].avail='Y' and $sql_tbl[products].forsale='Y' $brand_condition $sku_condition $price_condition $param00_query $descr_query $product_query $substring_query "; 

    
   $total_products_in_search =  count(func_query($search_query_count)); 

# 
# Navigation code 
# 
   $objects_per_page = $config["General"]["products_per_page"]; 

   $total_nav_pages = ceil($total_products_in_search/$objects_per_page)+1; 
   require "../include/navigation.php"; 

   $smarty->assign("products",func_search_products($search_query, $user_account['membership'],$first_page,$total_products_in_search, 0, $sort_by_price)); 

   $smarty->assign("navigation_script","search.php?substring=".urlencode($substring)."&search_brand=".$search_brand."&search_sku=".$search_sku."&search_param00=".urlencode($search_param00)."&search_product=".urlencode($search_product)."&search_det_description=".urlencode($search_det_description)."&in_category=".$in_category.$price_substring); 

   $HTTP_GET_VARS["substring"] = stripslashes($HTTP_GET_VARS["substring"]); 

   $smarty->assign("main","search"); 

} 
else { 
	$brands = func_query("select brand from $sql_tbl[products] where brand <> '' GROUP BY brand");
	$smarty->assign("brands", $brands);
	$smarty->assign("main","advanced_search");
} 

$smarty->display("customer/home.tpl"); 
?> 

advanced_search.tpl

{* $Id: advanced_search.tpl,v 1.3 2002/10/08 12:01:17 alfiya Exp $ *}
{include file="location.tpl" last_location="Advanced search"}
{capture name=adv_search}
<table border=0>
<form action="search.php" name="productsearchbyprice_form">
<tr>
<td>Product Title:</td>
<td>
<input type="text" name="search_product" size="30" value="{$smarty.get.search_product}">
</td>
</tr>
<tr>
<td>SKU#:</td>
<td>
<input type="text" name="search_sku" size="30" value="{$smarty.get.search_sku}">
</td>
</tr>
<tr>
<td nowrap>Product Description:</td>
<td>
<input type="text" name="search_det_description" size="30" value="{$smarty.get.search_det_description}">
</td>
</tr>
<tr>
<td>Brand:</td>
<td>
<select name=search_brand>
<option value="">None</option>
{section name=i loop=$brands}
<option value='{$brands[i].0}'>{$brands[i].0}</option>
{/section}
</select>
</td>
</tr>
<tr><td>Price Range({$config.General.currency_symbol}):</td>
<td><input type="text" name="price_search_1" size="6" value="{$smarty.get.price_search_1|escape}"> - <input type="text" name="price_search_2" size="6" value="{$smarty.get.price_search_2|escape}"></td></tr>
<tr><td>Category:</td>
<td>
<select name="in_category">
<option value="">All</option>
{section name=cat_num loop=$categories}
<option value="{ $categories[cat_num].categoryid}" {if $smarty.get.in_category eq $categories[cat_num].categoryid or $cat eq $categories[cat_num].categoryid}selected{/if}>{$categories[cat_num].category|escape}</option>
{/section}
</select>
</td>
</tr>
<tr>
<td nowrap>Vehicle Application:
&nbsp
&nbsp</td>
<td>
<input type="text" name="search_param00" size="30" value="{$smarty.get.search_param00}">

Example:(Honda Civic Si B16)
&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp&nbspDon't Include Year!</td>
</tr>
<tr><td></td></tr>
<tr><th>{include file="buttons/search.tpl"}</th></tr>
</form>
</table>
{/capture}
{include file="dialog.tpl" title="Advanced search" content=$smarty.capture.adv_search extra="width=100%"}

The only bug I have found is when one searches using the general seach on the main page It takes them to the advanced search and does not display any result however once this happens if you submit the seach again from the same spot all goes well I think I know what the problem is just have not had time to fix it.
Scott