X-Cart forums - Improved Search Function

X-Cart forums (https://forum.x-cart.com/index.php)

- Dev Questions (https://forum.x-cart.com/forumdisplay.php?f=20)

- - Improved Search Function (https://forum.x-cart.com/showthread.php?t=4415)

Quote:

Originally Posted by wallachee

Have you been able to work on the code I supplied for 3.5.3?

-Bradley

are you sure 3.5.3 or 3.5.1

Has anyone tried the code for 3.4.8 that many people testify works for them?

I tried using it, and it works, except I found out recently that clicking "advanced search" and trying to do a price search yields the following error:

Code:

INVALID SQL: 1064 : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '1 group by xcart_products.productid' at line 2



SQL QUERY FAILURE: select count(*) from xcart_products, xcart_pricing, xcart_categories where xcart_pricing.productid=xcart_products.productid and xcart_pricing.quantity=1 and xcart_products.categoryid=xcart_categories.categoryid AND (xcart_categories.membership='' OR xcart_categories.membership='') and (xcart_pricing.membership='' or xcart_pricing.membership='') AND 1 and xcart_products.forsale='Y' and xcart_categories.avail='Y' AND xcart_pricing.price>='150' AND xcart_pricing.price<='160' 1 group by xcart_products.productid

Help, anyone?

modifying my .tpl to form a complex search query

The problem :: In the beads section of my store, I need to give users a way to easily find the beads they are looking for. I carry a lot of different kinds of beads, so using the plain old X-Cart category set-up will leave the customers swimming through pages and pages of beads they don't care about.

The solution? :: I have decided that the best way to do this is to add a set of 3 pull-down menus to the header bar .tpl: size, color, and material; accompanied by a "go" button. The user can then select, for example, "Small Blue Glass" beads and browse a nice specific set of results. Included in the menus will be selections for "All sizes", "All colors", and "All materials".

** Update: I have figured it out! Thanks to Tuner's search.php contribution, I was able to simply copy and slightly modify a few bits and make it work for me. Hopefully someone else will be in my situation one day, and for them i will post my code below:

search.php:

Code:

<? 



# 

# $Id: search.php,v 1.44.2.1 2003/06/02 11:57:44 svowl Exp $ 

# 



require "../smarty.php"; 

require "../config.php"; 

@include "./https.php"; 

require "./auth.php"; 

require "../include/categories.php"; 



$tmp=strstr($QUERY_STRING, "$XCART_SESSION_NAME="); 

if (!empty($tmp)) 

 $QUERY_STRING=ereg_replace("$XCART_SESSION_NAME=([0-9a-zA-Z]*)", "", $QUERY_STRING); 



if(!empty($QUERY_STRING)) { 



# 

# Permorfm SQL search query 

# 

// LOOK HERE 

// new bit to split search string into separate words 

 $con = ""; $substr = ""; 

 $and="AND"; 

 

if(!$substring) { $substring=" "; }

 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].".descr like '%".$s."%'"." OR ". 

         $sql_tbl[products].".fulldescr like '%".$s."%'"." OR ".

         $sql_tbl[products].".param01 like '%".$s."%'"." OR ".

         $sql_tbl[products].".param02 like '%".$s."%'"." OR ".

         $sql_tbl[products].".param03 like '%".$s."%'"." OR ".

         $sql_tbl[products].".productid like '%".$s."%'".")"; 

    //$substr .= "&substring=".urlencode($substring); 

 } 

  

 if(empty($and))$and = "OR"; 

 $substring_query = (!empty($con)) ? " AND (".join(" ".$and." ",$con).") " :" 1 "; 







#Bead Size Search 

 $conp = ""; 

 $andp = "AND"; 

 if(!empty($search_bead_size)){ 

   $ssp = split(" ",$search_bead_size); 

   foreach($ssp as $sp) 

     $conp[] = "(".$sql_tbl[products].".param01 like '%".$sp."%'"." OR ". 

         $sql_tbl[products].".param01 like '%".$sp."%'".")"; 

   

 }

 

 if(empty($andp))$andp = "OR"; 

 $param01_query = (!empty($conp)) ? " AND (".join(" ".$andp." ",$conp).") " :"";

 

 

 #Bead Color Search 

 $conq = ""; 

 $andq = "AND"; 

 if(!empty($search_bead_color)){ 

   $ssq = split(" ",$search_bead_color); 

   foreach($ssq as $sq) 

     $conq[] = "(".$sql_tbl[products].".param02 like '%".$sq."%'"." OR ". 

         $sql_tbl[products].".param02 like '%".$sq."%'".")"; 

   

 }

 

  if(empty($andq))$andq = "OR"; 

 $param02_query = (!empty($conq)) ? " AND (".join(" ".$andq." ",$conq).") " :"";

 

 

  #Bead Material Search 

 $conr = ""; 

 $andr = "AND"; 

 if(!empty($search_bead_material)){ 

   $ssr = split(" ",$search_bead_material); 

   foreach($ssr as $sr) 

     $conr[] = "(".$sql_tbl[products].".param03 like '%".$sr."%'"." OR ". 

         $sql_tbl[products].".param03 like '%".$sr."%'".")"; 

   

 }

 

  if(empty($andr))$andr = "OR"; 

 $param03_query = (!empty($conr)) ? " AND (".join(" ".$andr." ",$conr).") " :"";

 





// 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):""; 



 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 { 

// LOOK HERE 

// nfc - removed $substring_query =..... 

  $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' $price_condition $param01_query $param02_query $param03_query $substring_query group by $sql_tbl[products].productid"; 

 } 

  

 $search_query = "$category_condition and $sql_tbl[categories].avail='Y' and $sql_tbl[products].forsale='Y' $price_condition $param01_query $param02_query $param03_query $substring_query "; 



  

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



# 

# Navigation code 

# 

 $objects_per_page = $config["Appearance"]["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)."&in_category=$in_category"."&search_param01=".urlencode($search_param01)."&search_param02=".urlencode($search_param02)."&search_param03=".urlencode($search_param03).$price_substring); 



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



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



} 

else { 

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

} 



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

?>

I then added the following to my advanced_search.tpl and the other templates where I wanted the user to be able to search the beads category:

Code:

<table>

<form action="search.php" name="productsearchbeads_form">

<tr><td>Beads</td>

<td>

<select name="search_bead_color">

<option value="">All Colors</option>

<option value="blu">Blue</option>

<option value="red">Red</option>

<option value="grn">Green</option>

</select>

<select name="search_bead_size">

<option value="">All Sizes</option>

<option value="s">Small</option>

<option value="m">Medium</option>

<option value="l">Large</option>

</select>

<select name="search_bead_material">

<option value="">All Materials</option>

<option value="wood">Wood</option>

<option value="plastic">Plastic</option>

<option value="glass">Glass</option>

</select>

</td>

</tr>

<tr><td></td></tr>

<tr><th>{include file="buttons/search.tpl"}</th></tr>

</form>

</table>

Now i know that .tpl code is not a particularly smart way of going about things, but I don't know enough about smarty to get it to automatically list the param01, 02, and 03 values. I'm guessing that would be done with a {section} command, like the category menu uses in advanced_search.tpl... Anyone?

- Jordan

brand searching question

Hello again

I am trying to implement Tuner's brand searching modifications and I have everything working except the code in advanced_search.tpl that generates the Brand pull-down menu:

Code:

<select name=search_brand> 

<option value="">None</option> 

{section name=i loop=$brands} 

<option value='{$brands[i].0}'>{$brands[i].0}</option> 

{/section} 

</select>

This code just creates a menu with the choices of "" and "None". When I create my own test menu with hard-coded option values, the brand search works fine, so I'm pretty sure this is where the problem lies. Is there one more bit of some other file I have to modify to get this going? I have been entering the values into the brand column of the products table by hand, through a MySQL DB editor program.

Thanks

You can place this code in the inv_update.php to add the brand field to update. This way you can bulk change your Brand field using a CSV list for all your products.

Code:

} elseif ($what == "brand") {        

db_query("UPDATE $sql_tbl[products] SET brand='$columns[1]' WHERE productcode='$columns[0]' $provider_condition");

To add an update for an extra field it would be something like this,
Define the field name in the code:

Code:

} elseif ($what == "name of extra field") {        

db_query("UPDATE $sql_tbl[extra_fields] SET name of extra field='$columns[1]' WHERE productcode='$columns[0]' $provider_condition");

Then add these to the dropdown list in the inv_update.tpl

Code:

<TD><SELECT name=what>

<OPTION value="p" selected>Pricing</OPTION>

<OPTION value="brand" selected>Brand</OPTION>

<OPTION value="name of extra field" selected>name of extra field</OPTION>

 <OPTION value="q" selected>Quantity</OPTION>

</SELECT></TD>

[/code]

THANK YOU for that information! I have been searching around for a way to add fields to the inventory update function for a while now.
However, I'm not sure if it addresses my problem above.
The brand information is already in the xcart database Б─⌠ I put it there using a phpMyAdmin-style solution. I found this bit of code at the end of search.php which came from Tuner's mod.

Code:

$brands = func_query("select brand from $sql_tbl[products] where brand <> '' GROUP BY brand"); 

 $smarty->assign("brands",$brands);

I am guessing that this creates the list of brands for the pull-down menu in my advanced_search.tpl. In the advanced_search.tpl I have the following code to create the menu:

Code:

<select name=search_brand> 

<option value="">None</option> 

{section name=i loop=$brands} 

<option value='{$brands[i].0}'>{$brands[i].0}</option> 

{/section} 

</select>

However, this doesn't seem to be working. I have 3 different brands throughout my entire store, and the menu contains only 3 blank choices in addition to "None". Somehow the {section} is getting the right number of brands, but not the actual values. What am I missing?
Thanks
-Jordan

Hi,

I have implemented a shop by brand feature, find below details. I am sure there will be some people more familiar with the software, php, smarty and mysql and might have used better code etc. but this works for me:

Creating the initial database query and defining the array in for smarty.

Code:

// database query 

$query = "SELECT brand FROM $sql_tbl[products] WHERE forsale='Y' GROUP BY brand ORDER BY brand"; 

// assign query to smarty. 

$brand = func_query($query); 

$smarty->assign("brand",$brand);

Create a template page to populate a jump menu e.g. brands.tpl including the following code:

Code:

<FORM name=brand action=search.php method=get

<select onChange=javascript:document.brand.submit() name=brand>

<option selected>SHOP BY BRAND</option>

{section name=brand loop=$brand}  

 <option value="{ $brand[brand].brand }">{ $brand[brand].brand|escape }</option>        

{/section} 

</select>

</form>

hope this helps people :)

N.B - the 'search.php' page will need to be modified in order to enable the filter of the database according to the 'brand' passed in the query string.

I implemented this mod, but now searches return way too many results and the best result is frequently buried several result pages deep.

Anyone else find this?

I am thinking to go back to the standard X-Cart Search php file. From an end user standpoint, if I go to a site and search for dinner forks, I want dinner forks to be in the top 5 results, not on page 3 of 100 results.

you could change the line in the files that is:

Code:

if(empty($and))$and = "OR"; 

$substring_query = (!empty($con)) ? " AND (".join(" ".$and." ",$con).") " :" 1 ";

Code:

if(empty($and))$and = "AND"; 

$substring_query = (!empty($con)) ? " AND (".join(" ".$and." ",$con).") " :" 1 ";

That will mean that both words have to be present to return the results and will work out far more accurate for your purposes.

Thanks FD. Once again, you save the day.