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

Best Sellers for the Month Only

 
Reply
   X-Cart forums > X-Cart 4 > Dev Questions
 
Thread Tools Search this Thread
  #1  
Old 01-31-2005, 12:30 AM
  Jon's Avatar 
Jon Jon is offline
 

X-Guru
  
Join Date: Oct 2002
Location: Vancouver, Canada
Posts: 4,200
 

Default 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}
Reply With Quote
  #2  
Old 01-31-2005, 12:46 AM
  Jon's Avatar 
Jon Jon is offline
 

X-Guru
  
Join Date: Oct 2002
Location: Vancouver, Canada
Posts: 4,200
 

Default

You can see it in action at: http://www.mixtapemp3.com
Reply With Quote
  #3  
Old 02-27-2005, 11:36 PM
 
yages yages is offline
 

eXpert
  
Join Date: Sep 2004
Location: Australia
Posts: 332
 

Default 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.
__________________
yages
x-cart version 4.5.5
Reply With Quote
  #4  
Old 02-27-2005, 11:49 PM
  Jon's Avatar 
Jon Jon is offline
 

X-Guru
  
Join Date: Oct 2002
Location: Vancouver, Canada
Posts: 4,200
 

Default

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}
Reply With Quote
  #5  
Old 02-27-2005, 11:53 PM
 
yages yages is offline
 

eXpert
  
Join Date: Sep 2004
Location: Australia
Posts: 332
 

Default best sellers

thanks
__________________
yages
x-cart version 4.5.5
Reply With Quote
  #6  
Old 02-28-2005, 12:04 AM
  Jon's Avatar 
Jon Jon is offline
 

X-Guru
  
Join Date: Oct 2002
Location: Vancouver, Canada
Posts: 4,200
 

Default

Made a little mistake, code should be:

require "./bestsellers_month.php";

Corrected above. It's late
Reply With Quote
  #7  
Old 02-28-2005, 12:18 AM
 
yages yages is offline
 

eXpert
  
Join Date: Sep 2004
Location: Australia
Posts: 332
 

Default best sellers

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
__________________
yages
x-cart version 4.5.5
Reply With Quote
  #8  
Old 02-28-2005, 12:23 AM
  Jon's Avatar 
Jon Jon is offline
 

X-Guru
  
Join Date: Oct 2002
Location: Vancouver, Canada
Posts: 4,200
 

Default

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); ?>
Reply With Quote
  #9  
Old 03-05-2005, 05:59 PM
 
yages yages is offline
 

eXpert
  
Join Date: Sep 2004
Location: Australia
Posts: 332
 

Default 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
__________________
yages
x-cart version 4.5.5
Reply With Quote
  #10  
Old 03-08-2005, 02:14 PM
  Jon's Avatar 
Jon Jon is offline
 

X-Guru
  
Join Date: Oct 2002
Location: Vancouver, Canada
Posts: 4,200
 

Default

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.
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 01:00 AM.

   

 
X-Cart forums © 2001-2020