X-Cart: shopping cart software

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)

wallachee 09-19-2003 09:57 PM

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

funkydunk 09-19-2003 11:14 PM

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

What version are you running?

wallachee 09-20-2003 06:27 AM

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

-Bradley

jpsowin 09-20-2003 08:37 AM

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.

wallachee 09-20-2003 09:11 AM

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

jpsowin 09-20-2003 11:59 AM

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/

funkydunk 09-21-2003 12:49 AM

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

jpsowin 09-21-2003 01:56 PM

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?

wallachee 09-21-2003 07:19 PM

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

nfc5382 09-23-2003 06:42 PM

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]


All times are GMT -8. The time now is 05:47 PM.

Powered by vBulletin Version 3.5.4
Copyright ©2000 - 2022, Jelsoft Enterprises Ltd.