X-Cart forums

[BCSE]

The code I gave in this thread was written for 3.5.x So it looks like in 4.0.x you need to add an AND right after:

$search_query ="

So that it looks like this now:

 $search_query = "AND $sql_tbl[products].forsale='Y' and $sql_tbl[products].avail>0 and $sql_tbl[products].sales_stats > $threshold AND $sql_tbl[categories].avail='Y' and price>0 group by $sql_tbl[products].productid order by sales_stats DESC, views_stats DESC limit ".$config["Modules"]["number_of_bestsellers"]; 

I haven't tested this, so just let me know if it doesn't work.

Thanks!

Carrie

[bardolino]

I made the change, but still got the error when i click on a categorie:

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 'GROUP BY xcart_products.productid ORDER BY xcart_products_categories.orderby' at line 1
SQL QUERY FAILURE: SELECT xcart_products.productid, xcart_products.product, xcart_products.productcode, xcart_products.avail, MIN(xcart_pricing.price) AS price, IF (xcart_classes.classid IS NULL,'','Y') as is_product_options, IF(xcart_variants.variantid IS NULL,'','Y') as is_variant FROM xcart_products, xcart_categories, xcart_products_categories, xcart_pricing LEFT JOIN xcart_classes ON xcart_classes.productid = xcart_products.productid LEFT JOIN xcart_variants ON xcart_variants.productid = xcart_products.productid LEFT JOIN xcart_products_lng ON xcart_products.productid=xcart_products_lng.produc tid WHERE xcart_products.productid=xcart_products_categories .productid AND xcart_products_categories.categoryid=xcart_categor ies.categoryid AND xcart_products.productid=xcart_pricing.productid AND xcart_pricing.quantity=1 AND (xcart_pricing.membership='' OR xcart_pricing.membership='') AND (xcart_categories.membership='' OR xcart_categories.membership='') AND xcart_products.forsale='Y' AND xcart_pricing.variantid = 0 AND xcart_products.product_type <> 'C' AND xcart_products.product_type <> 'B' AND xcart_products.forsale='Y' and xcart_products.avail>0 and xcart_products.sales_stats > 0 AND xcart_categories.avail='Y' and price>0 group by xcart_products.productid order by sales_stats DESC, views_stats DESC limit 5 GROUP BY xcart_products.productid ORDER BY xcart_products_categories.orderby

I don't get this error when go to the search page, but the bestsellers also don't show up there...

[HWT]

ditto on the SQL error.

And a little more info on the error:

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 'GROUP BY xcart_products.productid ORDER BY xcart_products_categ
Backtrace   :
/homepages/35/d117449933/htdocs/include/func.php:110
/homepages/35/d117449933/htdocs/include/func.php:70
/homepages/35/d117449933/htdocs/include/func.php:154
/homepages/35/d117449933/htdocs/include/func.php:1985
/homepages/35/d117449933/htdocs/modules/Bestsellers/bestsellers.php:96
/homepages/35/d117449933/htdocs/home.php:59

[BCSE]

That was a different sql error. Try this line instead. Again this is untested. I may have a few minutes at the end of the week to test this. Right now I'm swamped.

$search_query = "AND $sql_tbl[products].forsale='Y' and $sql_tbl[products].avail>0 and $sql_tbl[products].sales_stats > $threshold AND $sql_tbl[categories].avail='Y' and price>0;

Thanks!

Carrie

[HWT]

Thanks for taking the time Carrie,

Unforturnately still get the SQL error:

Date        : 03-Aug-2005 11:48:56
Site        : 
Script      : /home.php
Remote IP   : 
Logged as   : 
SQL query   : SELECT xcart_products.productid, xcart_products.product, xcart_products.productcode, xcart_products.avail, MIN(xcart_pricing.price) AS price, IF (xcart_classes.classid IS NULL,'','Y') as is_product_options, IF(xcart_variants.variantid IS NULL,'','Y') as is_variant FROM xcart_products, xcart_categories, xcart_products_categories, xcart_pricing LEFT JOIN xcart_classes ON xcart_classes.productid = xcart_products.productid LEFT JOIN xcart_variants ON xcart_variants.productid = xcart_products.productid WHERE xcart_products.productid=xcart_products_categories.productid AND xcart_products_categories.categoryid=xcart_categories.categoryid AND xcart_products.productid=xcart_pricing.productid AND xcart_pricing.quantity=1 AND (xcart_pricing.membership='' OR xcart_pricing.membership='')  AND (xcart_categories.membership='' OR xcart_categories.membership='') AND xcart_products.forsale='Y'  AND (xcart_pricing.variantid = 0 OR (xcart_variants.variantid = xcart_pricing.variantid)) AND xcart_products.product_type <> 'C' AND xcart_products.product_type <> 'B' AND xcart_products.forsale='Y' and xcart_products.avail>0 and xcart_products.sales_stats > 0 AND xcart_categories.avail='Y' and price>0; group by xcart_products.productid order by sales_stats DESC, views_stats DESC limit 8 GROUP BY xcart_products.productid ORDER BY xcart_products_categories.orderby
Error code  : 1064
Description :

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 '; group by xcart_products.productid order by sales_stats DESC, 
Backtrace   :
/homepages/35/d117449933/htdocs/include/func.php:110
/homepages/35/d117449933/htdocs/include/func.php:70
/homepages/35/d117449933/htdocs/include/func.php:154
/homepages/35/d117449933/htdocs/include/func.php:1985
/homepages/35/d117449933/htdocs/modules/Bestsellers/bestsellers.php:96
/homepages/35/d117449933/htdocs/home.php:59

[BCSE]

Sorry I had a typo. Been an intense week. or is it

Try this:

$search_query = "AND $sql_tbl[products].forsale='Y' and $sql_tbl[products].avail>0 and $sql_tbl[products].sales_stats > $threshold AND $sql_tbl[categories].avail='Y' and price>0";

Carrie

[TTHTG]

Great mod! trying to get this to work on 4.0.18
It works but if you are viewing a product and click on one of the bestsellers that is in a different category, the category location does not update.

exsample..you are viewing

baby > diapers > product A

and click on bestseller product B in catagory
electronics > stereo > Product B
the location would show

baby > diapers > product B

how can I fix this?

<?php
/*****************************************************************************\
+-----------------------------------------------------------------------------+
| X-Cart                                                                      |
| Copyright (c) 2001-2006 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-2006           |
| Ruslan R. Fazliev. All Rights Reserved.                                     |
+-----------------------------------------------------------------------------+
\*****************************************************************************/

#
# $Id: bestsellers.php,v 1.24.2.4 2006/01/16 14:58:31 mclap Exp $
#
# Bestsellers
#

if (!defined('XCART_SESSION_START')) { header("Location: ../../"); die("Access denied"); }

if (!is_numeric($config["Modules"]["number_of_bestsellers"]))
	$config["Modules"]["number_of_bestsellers"] = 0;

#
# Get products data for current category and store it into $products array
#
$avail_condition = "";
if ($config["General"]["unlimited_products"] == "N" && $config["General"]["disable_outofstock_products"] == "Y")
	$avail_condition = " AND $sql_tbl[products].avail>0 ";

$cat = intval($cat);

if ($cat) {

	$category_data = func_query_first("SELECT categoryid_path, threshold_bestsellers FROM $sql_tbl[categories] USE INDEX (PRIMARY) WHERE categoryid='$cat'");
	$result = func_query("SELECT categoryid, threshold_bestsellers FROM $sql_tbl[categories] USE INDEX (pam) WHERE categoryid_path LIKE '$category_data[categoryid_path]/%' AND avail='Y'");

	$threshold = (int)$category_data["threshold_bestsellers"];

	$cat_ids = array();
	
	if (is_array($result)) {
		foreach($result as $k=>$v) {
			$cat_ids[] = $v["categoryid"];
			if ($v["threshold_bestsellers"]>0 && $threshold > $v["threshold_bestsellers"])
				$threshold = $v["threshold_bestsellers"];
		}
	}
	else
		$cat_ids[] = $cat;

	$str = " AND $sql_tbl[products_categories].categoryid IN (".implode(",", $cat_ids).")";

	if ($threshold)
		$threshold -=1;
	
	$search_query = $str." AND $sql_tbl[products].views_stats>0 AND $sql_tbl[products].sales_stats > $threshold";
}
else {
	$threshold = 0;
	$search_query = " AND $sql_tbl[products].sales_stats>$threshold";
}

$order_by = "$sql_tbl[products].sales_stats DESC, $sql_tbl[products].views_stats DESC";

#
# Search the bestsellers
#
$bestsellers = func_search_products($search_query, @$user_account["membership"], $order_by, $config["Modules"]["number_of_bestsellers"]);

# Mod from BCSE
# Show bestseller on all pages
#
if(empty($bestsellers)) 
{ 
$threshold = 0; 
   $search_query = " AND $sql_tbl[products].forsale='Y' and $sql_tbl[products].avail>0 and $sql_tbl[products].sales_stats > $threshold AND $sql_tbl[categories].avail='Y' and price>0"; 
   $bestsellers = func_search_products($search_query, @$user_account["membership"], $order_by, $config["Modules"]["number_of_bestsellers"]); 
} 
#
#
#

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

?>

[alru111]

@TTHTG
you have an error in the code, everything looks fine on my XC
I am using the latest version

Thanks for mod

[sharingsunshine]

Thanks for making this mod available. However, I find that if a category is empty it is showing all the products for the store, even though I have under Modules Options - the number of products in bestsellers list set to 10.

Is there something else that needs to be done? I have over 600 products and the list is very long since it is ignoring the limit I set in the Admin section.

Any help will be greatly appreciated.

Randal

[ShishaPipeUK]

I changed this to work on x-cart 4.1.3 - You can see an example at http://www.theshisha.com/shopcart/home.php?cat=273

The end of my Bestsellers is:

# SELECT RANDOM ITEMS IF STATS ARE TURNED OFF WHICH BESTSELLERS MODULE NEEDS TO WORK
if(empty($bestsellers))
{
# SEARCH PRODUCTS FOR SALE, AVAILABLE, CATEGORIES AVAILABLE, PRICE GREATER THAN 0
$price_product = 10; # PRODUCT PRICE MUST BE GREATER THAN 10
$search_query = " AND $sql_tbl[products].forsale='Y' AND $sql_tbl[products].avail>0 AND $sql_tbl[categories].avail='Y' AND price>$price_product";
$bestsellers = func_search_products($search_query, @$user_account["membershipid"], $order_by, $config["Bestsellers"]["number_of_bestsellers"]);
}
#

This wants to go before:

$smarty->assign("bestsellers",$bestsellers);
?>

You might want to also look at the best sellers for the month thread at http://forum.x-cart.com/showpost.php?p=146486&postcount=18