Follow us on Twitter X-Cart on Facebook Wiki
Shopping cart software Solutions for online shops and malls

Improved Search Function

 
Reply
   X-Cart forums > X-Cart 4 > Dev Questions
 
Thread Tools Search this Thread
  #1  
Old 09-19-2003, 09:57 PM
 
wallachee wallachee is offline
 

Senior Member
  
Join Date: Feb 2003
Posts: 102
 

Default Improved Search Function

I need some help with my xcart search. Whenever a customer enters a phrase that is more than two words, the search rarely picks up the products unless an exact match is found. What I need is for it to pick up one of the words in the phrase or both words but not together. How can I do this. My current search.php search looks like this

Code:
$search_query = "($sql_tbl[products].product like '%$substring%' or $sql_tbl[products].descr like '%$substring%') and $sql_tbl[categories].category like '$search_category%' and $sql_tbl[products].forsale='Y' and $sql_tbl[categories].avail='Y' $price_condition ";

Any help is appreciated.

-Bradley
Reply With Quote
  #2  
Old 09-19-2003, 11:14 PM
 
funkydunk funkydunk is offline
 

X-Man
  
Join Date: Oct 2002
Location: Cambridge, UK
Posts: 2,210
 

Default

This is quite a detailed mod....doable, but detailed.

What version are you running?
__________________
ex x-cart guru
Reply With Quote
  #3  
Old 09-20-2003, 06:27 AM
 
wallachee wallachee is offline
 

Senior Member
  
Join Date: Feb 2003
Posts: 102
 

Default

I'm running 3.3.5. What type of mod would I be looking at?

-Bradley
Reply With Quote
  #4  
Old 09-20-2003, 08:37 AM
 
jpsowin jpsowin is offline
 

X-Adept
  
Join Date: Sep 2002
Posts: 459
 

Default

Yeah, we really need to do something about this since x-cart isn't. That's one of the things on my list is to modify it so the stupid search actually works well enough to be used on a site. I think we all lose lots of customers to this.
__________________
Joshua Sowin
...taking things one TPL at a time.
Reply With Quote
  #5  
Old 09-20-2003, 09:11 AM
 
wallachee wallachee is offline
 

Senior Member
  
Join Date: Feb 2003
Posts: 102
 

Default

I agree completely, that is why I'd like to get the search fixed on my site. Many customers are complaining that their searches never turn up any results....the search is way too sensitive. The products I sell are referred to by several different names, so I need something that is much less sensitive. If my customers don't see a product when they search for it, then most likely they won't buy. Let's try to get a lot of support on this. I'd even be willing to pay for a mod if it wasn't a ridiculous price.

-Bradley
Reply With Quote
  #6  
Old 09-20-2003, 11:59 AM
 
jpsowin jpsowin is offline
 

X-Adept
  
Join Date: Sep 2002
Posts: 459
 

Default

Integrating this type of feature might do it well:
http://www.daemonprojects.com/phpsnip/view.php?snip=7

Currently all the search terms have to be in order, so this would break the search terms apart and find the search terms even if they are not all together. Would be a start, anyway. Any other ideas?

Some other links:
http://www.phpbuilder.com/columns/clay19990421.php3
http://www.mysql.com/doc/en/Fulltext_Search.html
http://search.mnogo.ru/
http://swish-e.org/
__________________
Joshua Sowin
...taking things one TPL at a time.
Reply With Quote
  #7  
Old 09-21-2003, 12:49 AM
 
funkydunk funkydunk is offline
 

X-Man
  
Join Date: Oct 2002
Location: Cambridge, UK
Posts: 2,210
 

Default

something like:

Code:
<? /*****************************************************************************\ +-----------------------------------------------------------------------------+ | X-Cart | | Copyright (c) 2001-2003 Ruslan R. Fazliev <rrf@rrf.ru> | | All rights reserved. | +-----------------------------------------------------------------------------+ | PLEASE READ THE FULL TEXT OF SOFTWARE LICENSE AGREEMENT IN THE "COPYRIGHT" | | FILE PROVIDED WITH THIS DISTRIBUTION. THE AGREEMENT TEXT IS ALSO AVAILABLE | | AT THE FOLLOWING URL: http://www.x-cart.com/license.php | | | | THIS AGREEMENT EXPRESSES THE TERMS AND CONDITIONS ON WHICH YOU MAY USE | | THIS SOFTWARE PROGRAM AND ASSOCIATED DOCUMENTATION THAT RUSLAN R. | | FAZLIEV (hereinafter referred to as "THE AUTHOR") IS FURNISHING OR MAKING | | AVAILABLE TO YOU WITH THIS AGREEMENT (COLLECTIVELY, THE "SOFTWARE"). | | PLEASE REVIEW THE TERMS AND CONDITIONS OF THIS LICENSE AGREEMENT | | CAREFULLY BEFORE INSTALLING OR USING THE SOFTWARE. BY INSTALLING, | | COPYING OR OTHERWISE USING THE SOFTWARE, YOU AND YOUR COMPANY | | (COLLECTIVELY, "YOU") ARE ACCEPTING AND AGREEING TO THE TERMS OF THIS | | LICENSE AGREEMENT. IF YOU ARE NOT WILLING TO BE BOUND BY THIS | | AGREEMENT, DO NOT INSTALL OR USE THE SOFTWARE. VARIOUS COPYRIGHTS AND | | OTHER INTELLECTUAL PROPERTY RIGHTS PROTECT THE SOFTWARE. THIS | | AGREEMENT IS A LICENSE AGREEMENT THAT GIVES YOU LIMITED RIGHTS TO USE | | THE SOFTWARE AND NOT AN AGREEMENT FOR SALE OR FOR TRANSFER OF TITLE.| | THE AUTHOR RETAINS ALL RIGHTS NOT EXPRESSLY GRANTED BY THIS AGREEMENT. | | | | The Initial Developer of the Original Code is Ruslan R. Fazliev | | Portions created by Ruslan R. Fazliev are Copyright (C) 2001-2003 | | Ruslan R. Fazliev. All Rights Reserved. | +-----------------------------------------------------------------------------+ \*****************************************************************************/ # # $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)) { # # Perform SQL search query # // new bit to split search string into separate words $con = ""; $substr = ""; if(!empty($substring)) { $ss = split(" ",$substring); foreach($ss as $s)$con[] = $sql_tbl[products].".product like '%".$s."%'"; $substr .= "&substring=".urlencode($substring); } if(empty($and))$and = "or"; $substr .= "&and=".$and; $substring_query = (!empty($con)) ? " (".join(" ".$and." ",$con).") " : " 1 "; // 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) { // $substring_query = "AND (($sql_tbl[products].product like '%$substring%' or $sql_tbl[products].descr like '%$substring%') OR ($sql_tbl[products_lng].code='$store_language' AND ($sql_tbl[products_lng].product LIKE '%$substring%' OR $sql_tbl[products_lng].descr LIKE '%$substring%' OR $sql_tbl[products_lng].full_descr LIKE '%$substring%')))"; $search_query_count = "select count(*) from $sql_tbl[pricing], $sql_tbl[categories], $sql_tbl[products] LEFT JOIN $sql_tbl[products_lng] ON $sql_tbl[products].productid=$sql_tbl[products_lng].productid 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 $substring_query group by $sql_tbl[products].productid"; } else { // $substring_query = "AND ($sql_tbl[products].product like '%$substring%' OR $sql_tbl[products].descr like '%$substring%')"; $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 ($sql_tbl[products].product like '%$substring%' or $sql_tbl[products].descr like '%$substring%') AND $category_condition and $sql_tbl[products].forsale='Y' and $sql_tbl[categories].avail='Y' $price_condition $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 $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".$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"); ?>

Please note that has not been tested
__________________
ex x-cart guru
Reply With Quote
  #8  
Old 09-21-2003, 01:56 PM
 
jpsowin jpsowin is offline
 

X-Adept
  
Join Date: Sep 2002
Posts: 459
 

Default

Hmm.. it looks right, but still got an error:
test is the first word, and test2 is the second

Quote:
INVALID SQL: 1064 : You have an error in your SQL syntax near INVALID SQL: 1064 : You have an error in your SQL syntax near '(xcart_products.product like '%test%' or xcart_products.product like '%test1%') ' at line 1
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.categor yid AND (xcart_categories.membership='' OR xcart_categories.membership='') and (xcart_pricing.membership='' or xcart_pricing.membership='') and (xcart_products.product like '%test test1%' or xcart_products.descr like '%test test1%') AND 1 and xcart_products.forsale='Y' and xcart_categories.avail='Y' (xcart_products.product like '%test%' or xcart_products.product like '%test1%') group by xcart_products.productid

Maybe the [i] needs to be added to the %$substring[i]% like the example?
__________________
Joshua Sowin
...taking things one TPL at a time.
Reply With Quote
  #9  
Old 09-21-2003, 07:19 PM
 
wallachee wallachee is offline
 

Senior Member
  
Join Date: Feb 2003
Posts: 102
 

Default

Thanks for the quick work on this. The code gives me the following error in 3.3.5

"Parse error: parse error in /xcart2/search.php on line 61"

I get this when I copy and paste only the new section into my code. Copying the code completely gives me a parse error on line 54...Any other tips?

Thanks again,
Bradley
Reply With Quote
  #10  
Old 09-23-2003, 06:42 PM
 
nfc5382 nfc5382 is offline
 

X-Adept
  
Join Date: Nov 2002
Posts: 477
 

Default

this works in 3.4.2 (based off from funkydunk's fix above)...

adds support for searching fulldescription, desc, productid, product

here it is: (look near the "LOOK HERE"s)


Code:
<? /*****************************************************************************\ +-----------------------------------------------------------------------------+ | X-Cart | | Copyright (c) 2001-2003 Ruslan R. Fazliev <rrf@rrf.ru> | | All rights reserved. | +-----------------------------------------------------------------------------+ | PLEASE READ THE FULL TEXT OF SOFTWARE LICENSE AGREEMENT IN THE "COPYRIGHT" | | FILE PROVIDED WITH THIS DISTRIBUTION. THE AGREEMENT TEXT IS ALSO AVAILABLE | | AT THE FOLLOWING URL: http://www.x-cart.com/license.php | | | | THIS AGREEMENT EXPRESSES THE TERMS AND CONDITIONS ON WHICH YOU MAY USE | | THIS SOFTWARE PROGRAM AND ASSOCIATED DOCUMENTATION THAT RUSLAN R. | | FAZLIEV (hereinafter referred to as "THE AUTHOR") IS FURNISHING OR MAKING | | AVAILABLE TO YOU WITH THIS AGREEMENT (COLLECTIVELY, THE "SOFTWARE"). | | PLEASE REVIEW THE TERMS AND CONDITIONS OF THIS LICENSE AGREEMENT | | CAREFULLY BEFORE INSTALLING OR USING THE SOFTWARE. BY INSTALLING, | | COPYING OR OTHERWISE USING THE SOFTWARE, YOU AND YOUR COMPANY | | (COLLECTIVELY, "YOU") ARE ACCEPTING AND AGREEING TO THE TERMS OF THIS | | LICENSE AGREEMENT. IF YOU ARE NOT WILLING TO BE BOUND BY THIS | | AGREEMENT, DO NOT INSTALL OR USE THE SOFTWARE. VARIOUS COPYRIGHTS AND | | OTHER INTELLECTUAL PROPERTY RIGHTS PROTECT THE SOFTWARE. THIS | | AGREEMENT IS A LICENSE AGREEMENT THAT GIVES YOU LIMITED RIGHTS TO USE | | THE SOFTWARE AND NOT AN AGREEMENT FOR SALE OR FOR TRANSFER OF TITLE.| | THE AUTHOR RETAINS ALL RIGHTS NOT EXPRESSLY GRANTED BY THIS AGREEMENT. | | | | The Initial Developer of the Original Code is Ruslan R. Fazliev | | Portions created by Ruslan R. Fazliev are Copyright (C) 2001-2003 | | Ruslan R. Fazliev. All Rights Reserved. | +-----------------------------------------------------------------------------+ \*****************************************************************************/ # # $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(!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].".productid like '%".$s."%'".")"; //$substr .= "&substring=".urlencode($substring); } if(empty($and))$and = "OR"; $substring_query = (!empty($con)) ? " AND (".join(" ".$and." ",$con).") " :" 1 "; // 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 $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 $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".$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"); ?>

thanks funkydunk!!![/b]
Reply With Quote
Reply
   X-Cart forums > X-Cart 4 > Dev Questions


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump


All times are GMT -8. The time now is 12:44 PM.

   

 
X-Cart forums © 2001-2020