X-Cart forums

[DataViking]

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

[krishmandal]

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:

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?

[jordan0]

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:

<? 

# 
# $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:

<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

[jordan0]

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:

<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

[turtle]

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.

} 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:

} 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

<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]

[jordan0]

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.

$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:

<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

[laureon]

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.

// 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:

<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.

[adpboss]

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.

[funkydunk]

you could change the line in the files that is:

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

to

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.

[adpboss]

Thanks FD. Once again, you save the day.