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.


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

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:


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


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

best sellers
 

thanks

Made a little mistake, code should be:

require "./bestsellers_month.php";

Corrected above. It's late :)

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

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

Try this:

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.

THis sure would have been a nice mod. Too bad it doesn't work on the latest versions of X-Cart. *sad face*

- Shannon

Hi Jon,

Nice mod. =D> However, I'm sorry for going off-topic for a moment, but I noticed something while visiting the mixtapemp3.com site that I want to ask you about. This site uses tabs to display information, and since my store uses them too, I was wondering how you managed to stop the page from jumping around (making the tabs appear at the top of the page) when clicking on them. Mine always go to the top of the page for some strange reason. How did you stop this from happening?

If somebody is interested you can PM me for a quote for 4.x, but I don't have the time to make it without necessity to do so.

You can prevent your page from jumping to the type by adding this code to your <a href tag:


Prevents the link from doing what it's supposed to.

Thanks Jon, love your work mate! Oh, and btw, worked great too!

yages,

I think the reason why you see that error is because ...

On v4.0.x.. you need


instead of


After I corrected to that, I don't see the error message no more.
My site is still at testing stage, and there is no product sold, so I don't see any change this MOD does to my site just yet.

Let me know if anyone has a working demo site with standard template.

I tried to install this mod but it would not work

Maybe this could be the problem
My home.php looks like this

#
# $Id: home.php,v 1.44.2.2 2005/01/12 07:41:51 svowl Exp $
#

header("Location: ../home.php".(!empty($QUERY_STRING) ? "?".$QUERY_STRING : ""));
?>

where do I put the code
require $xcart_dir."/bestsellers_month.php";

Thanks! This is excellent.

I made one change so it's the last month's bestsellers, not just since the first of the current month:


I removed the month_cat, since I want all categories included. I also checked orders' status (for Processed or Completed in my case):


And finally, I modified skin1/modules/Bestsellers/menu_bestsellers.tpl, rather than changing welcome.tpl.

Just change all instances of $bestsellers to $month_bestsellers.

Best,
Stefan

Re: Best Sellers for the Month Only
 

I changed this for xcart 4.1.3

shopcart/bestsellers_month.php

<?
$start_date = strtotime("-1 month"); # LAST MONTH PRODUCTS
$end_date = strtotime("now"); # TODAY - NOW
$amount_prod_to_show = '6'; # AMOUNT OF PRODUCTS TO SHOW
$query = "SELECT $sql_tbl[products].*, $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].status='C' OR $sql_tbl[orders].status='P') AND $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].forsale='Y' AND $sql_tbl[products].avail>0 GROUP BY $sql_tbl[order_details].productid ORDER BY sold DESC LIMIT $amount_prod_to_show";
$month_sellers = func_query($query);
$smarty->assign("month_sellers",$month_sellers);
?>

In the shopcart/home.php add

require "./bestsellers_month.php";

In the shopcart/skin1/customer/main/welcome.tpl add

{if $month_sellers ne ""}
{include file="modules/Bestsellers/bestsellers_month.tpl" products=$month_sellers featured="Y"}
<br />{/if}

Also add the above if you want it to be displayed in your subcategories to shopcart/skin1/customer/main/subcategories.tpl

I also made in shopcart/skin1/modules/bestsellers/bestsellers_month.tpl file which is:

bestsellers_month.tpl

{if $month_sellers}
{capture name=bestsellers}
<TABLE border="0" cellpadding="0" cellspacing="0">
<tr>
{foreach from=$month_sellers item=bestseller}
<TD width="100" align="center" valign="top">
<div align="center">{if $config.Bestsellers.bestsellers_thumbnails eq "Y"} <a href="product.php?productid={$bestseller.productid }&cat={$cat}&bestseller">{include file="product_thumbnail.tpl" productid=$bestseller.productid image_x=50 product=$bestseller.product alt=$bestseller.fulldescr|replace:"":""}</a>
<br />
{/if}
<b><a href="product.php?productid={$bestseller.productid }&amp;cat={$cat}&amp;bestseller">{$bestseller.prod uct}</a></b><br />
{$lng.lbl_our_price}: {include file="currency.tpl" value=$bestseller.price}<br />
</div></td>
<TD width="10"></TD>
{/foreach}
</tr>
</table>
{/capture}
{include file="dialog.tpl" title="This Months Hottest Sellers!" content=$smarty.capture.bestsellers extra='width="100%"'}
{/if}

You can see the finished product at http://www.theshisha.com/shopcart/home.php?cat=255

It will also display at the top of every categories, to see this go to http://www.theshisha.com/shopcart/home.php?cat=272

Re: Best Sellers for the Month Only
 

Thank you so much for sharing the original, and everyone who posted 4x revisions. I haven't tried this yet, so may come back with questions :) -- but it's very kind of you.

Re: Best Sellers for the Month Only
 

Glad to see people are still benefiting from this, thanks for posting the updates :)

Re: Best Sellers for the Month Only
 

Does anyone know a) if this will work in 4.1.9 and b) how to use it in the side bar instead of on the top of the page(s)?

Re: Best Sellers for the Month Only
 

bueller? bueller?

Re: Best Sellers for the Month Only
 

Hi will this work for latest 4.x ?

Re: Best Sellers for the Month Only
 

you can try this as well.
http://forum.x-cart.com/showthread.php?t=60906&highlight=bestseller

Re: Best Sellers for the Month Only
 

Hi Bill,
Will these work for x cart Pro ? Have you tested and implemented it in your store?

Thanks.

Re: Best Sellers for the Month Only
 

I have not implemented it. I remembered the thread and thought it may be able to help.