X-Cart forums - Best Sellers for the Month Only

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

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

- - Best Sellers for the Month Only (https://forum.x-cart.com/showthread.php?t=11840)

Best Sellers for the Month Only

I've never much cared for the Best Sellers list of X-Cart.

I wrote up this query to grab the best sellers for a category on a monthly basis.

Code:

<?

#-----------------------------------------

# Monthly Best Sellers

# http://www.websitecm.com/

#-----------------------------------------



$start_date = strtotime(date("Y-m-01"));

$end_date = strtotime(date("Y-m-t"));



// The category to pull products from

$month_cat = '20';



// The number of products to show

$month_limit = '4';



$query = "SELECT $sql_tbl[products].product,$sql_tbl[products].distribution, $sql_tbl[products].list_price, $sql_tbl[products].vat, $sql_tbl[products].product_type,$sql_tbl[products].min_amount,$sql_tbl[products].productid,$sql_tbl[products].avail,$sql_tbl[products].categoryid,$sql_tbl[products].forsale,$sql_tbl[pricing].*,$sql_tbl[order_details].productid,$sql_tbl[order_details].orderid,$sql_tbl[orders].orderid,$sql_tbl[orders].date, count($sql_tbl[order_details].productid) as sold FROM $sql_tbl[pricing],$sql_tbl[products],$sql_tbl[orders],$sql_tbl[order_details] WHERE $sql_tbl[orders].date > '$start_date' AND $sql_tbl[orders].date < '$end_date' AND $sql_tbl[order_details].orderid=$sql_tbl[orders].orderid AND $sql_tbl[products].productid=$sql_tbl[order_details].productid AND $sql_tbl[pricing].productid=$sql_tbl[products].productid AND $sql_tbl[products].categoryid='$month_cat' AND $sql_tbl[products].forsale='Y' AND $sql_tbl[products].avail>0 GROUP BY $sql_tbl[order_details].productid ORDER BY sold DESC LIMIT $month_limit";



$month_sellers = func_query($query);



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



?>

You can then use it on the template using something like:

Code:

{if $month_sellers ne ""}






{capture name=dialog}

{include file="customer/main/products.tpl" products=$month_sellers featured="Y"}

{/capture}

{include file="dialog.tpl" title="This Months Hottest Sellers!" content=$smarty.capture.dialog extra="width=100%"}

{/if}

You can see it in action at: http://www.mixtapemp3.com ;)

Monthly best sellers list

How do I install this code. Do I have to create a template. You step by step instructions of installing this would be greatly appreciated.

Create a file called bestsellers_month.php and save it in the same folder as home.php

Put this code in it:

Code:

<? 

#----------------------------------------- 

# Monthly Best Sellers 

# http://www.websitecm.com/ 

#----------------------------------------- 



$start_date = strtotime(date("Y-m-01")); 

$end_date = strtotime(date("Y-m-t")); 



// The category to pull products from 

$month_cat = '20'; 



// The number of products to show 

$month_limit = '4'; 



$query = "SELECT $sql_tbl[products].product,$sql_tbl[products].distribution, $sql_tbl[products].list_price, $sql_tbl[products].vat, $sql_tbl[products].product_type,$sql_tbl[products].min_amount,$sql_tbl[products].productid,$sql_tbl[products].avail,$sql_tbl[products].categoryid,$sql_tbl[products].forsale,$sql_tbl[pricing].*,$sql_tbl[order_details].productid,$sql_tbl[order_details].orderid,$sql_tbl[orders].orderid,$sql_tbl[orders].date, count($sql_tbl[order_details].productid) as sold FROM $sql_tbl[pricing],$sql_tbl[products],$sql_tbl[orders],$sql_tbl[order_details] WHERE $sql_tbl[orders].date > '$start_date' AND $sql_tbl[orders].date < '$end_date' AND $sql_tbl[order_details].orderid=$sql_tbl[orders].orderid AND $sql_tbl[products].productid=$sql_tbl[order_details].productid AND $sql_tbl[pricing].productid=$sql_tbl[products].productid AND $sql_tbl[products].categoryid='$month_cat' AND $sql_tbl[products].forsale='Y' AND $sql_tbl[products].avail>0 GROUP BY $sql_tbl[order_details].productid ORDER BY sold DESC LIMIT $month_limit"; 



$month_sellers = func_query($query); 



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



?>

Open up your home.php and somewhere after the include of auth.php put:

Code:

require "./bestsellers_month.php";

Then open up skin1/customer/main/welcome.tpl and at the top put:

Code:

{if $month_sellers ne ""} 



 

{capture name=dialog} 

{include file="customer/main/products.tpl" products=$month_sellers featured="Y"} 

{/capture} 

{include file="dialog.tpl" title="This Months Hottest Sellers!" content=$smarty.capture.dialog extra="width=100%"} 

{/if}

Made a little mistake, code should be:

require "./bestsellers_month.php";

Corrected above. It's late :)

Tried installing your codes as per your instructions and this error occurred
Can you help

INVALID SQL: 1054 : Unknown column 'xcart_products.vat' in 'field list'
SQL QUERY FAILURE: SELECT xcart_products.product,xcart_products.distribution , xcart_products.list_price, xcart_products.vat, xcart_products.product_type,xcart_products.min_amo unt,xcart_products.productid,xcart_products.avail, xcart_products.categoryid,xcart_products.forsale,x cart_pricing.*,xcart_order_details.productid,xcart _order_details.orderid,xcart_orders.orderid,xcart_ orders.date, count(xcart_order_details.productid) as sold FROM xcart_pricing,xcart_products,xcart_orders,xcart_or der_details WHERE xcart_orders.date > '1107234000' AND xcart_orders.date < '1109566800' AND xcart_order_details.orderid=xcart_orders.orderid AND xcart_products.productid=xcart_order_details.produ ctid AND xcart_pricing.productid=xcart_products.productid AND xcart_products.categoryid='20' AND xcart_products.forsale='Y' AND xcart_products.avail>0 GROUP BY xcart_order_details.productid ORDER BY sold DESC LIMIT 4

Code in 4.x is likely different in some areas.

Try this:

Code:

<? 

#----------------------------------------- 

# Monthly Best Sellers 

# http://www.websitecm.com/ 

#----------------------------------------- 



$start_date = strtotime(date("Y-m-01")); 

$end_date = strtotime(date("Y-m-t")); 



// The category to pull products from 

$month_cat = '20'; 



// The number of products to show 

$month_limit = '4'; 



$query = "SELECT $sql_tbl[products].product,$sql_tbl[products].distribution, $sql_tbl[products].list_price,$sql_tbl[products].product_type,$sql_tbl[products].min_amount,$sql_tbl[products].productid,$sql_tbl[products].avail,$sql_tbl[products].categoryid,$sql_tbl[products].forsale,$sql_tbl[pricing].*,$sql_tbl[order_details].productid,$sql_tbl[order_details].orderid,$sql_tbl[orders].orderid,$sql_tbl[orders].date, count($sql_tbl[order_details].productid) as sold FROM $sql_tbl[pricing],$sql_tbl[products],$sql_tbl[orders],$sql_tbl[order_details] WHERE $sql_tbl[orders].date > '$start_date' AND $sql_tbl[orders].date < '$end_date' AND $sql_tbl[order_details].orderid=$sql_tbl[orders].orderid AND $sql_tbl[products].productid=$sql_tbl[order_details].productid AND $sql_tbl[pricing].productid=$sql_tbl[products].productid AND $sql_tbl[products].categoryid='$month_cat' AND $sql_tbl[products].forsale='Y' AND $sql_tbl[products].avail>0 GROUP BY $sql_tbl[order_details].productid ORDER BY sold DESC LIMIT $month_limit"; 



$month_sellers = func_query($query); 



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



?>

best sellers for month

Tried to install your code again and the following error occurred

INVALID SQL: 1054 : Unknown column 'xcart_products.categoryid' in 'field list'
SQL QUERY FAILURE: SELECT xcart_products.product,xcart_products.distribution , xcart_products.list_price,xcart_products.product_t ype,xcart_products.min_amount,xcart_products.produ ctid,xcart_products.avail,xcart_products.categoryi d,xcart_products.forsale,xcart_pricing.*,xcart_ord er_details.productid,xcart_order_details.orderid,x cart_orders.orderid,xcart_orders.date, count(xcart_order_details.productid) as sold FROM xcart_pricing,xcart_products,xcart_orders,xcart_or der_details WHERE xcart_orders.date > '1109653200' AND xcart_orders.date < '1112245200' AND xcart_order_details.orderid=xcart_orders.orderid AND xcart_products.productid=xcart_order_details.produ ctid AND xcart_pricing.productid=xcart_products.productid AND xcart_products.categoryid='20' AND xcart_products.forsale='Y' AND xcart_products.avail>0 GROUP BY xcart_order_details.productid ORDER BY sold DESC LIMIT 4

Unfortunately I can't really provide step by step instructions for this, as I don't use version 4.0 and the code may or may not work for 4.x with minor revisions. You can try just changing the code where table fields error (with the actual table name) or by removing it from the query string if that fails, but it won't be guaranteed to work.