Best Sellers WITH OUT Advanced Statistics

lachild · #1 01-31-2007, 02:27 PM

Heres a quick mod to enable Best Sellers without slowing down your site by turning on Advanced Statistics. Instead it uses the power of MySQL to do the research for you.

This script will also add how many times this product has been purchased for the last thirty days as well. You can of course simply remove this feature in the new template file if you don't want this displayed.

Please note this has only been tested on version 4.1

To begin create a new file "/modules/Bestsellers/newbestsellers.php"

Paste in the following code into the new file:

Code:

<?php
/////////////////////////
///  Bestsellers Mod  ///
/// By: Westin Shafer ///
/////////////////////////

//Set Dates
$today = mktime(0, 0, 0, date("m"), date("d"), date("Y"));
$lastmonth = mktime(0, 0, 0, date("m")-1, date("d"), date("Y"));

// Get Best Sellers for last 30 days
// We get 20 just in case there is a problem with one of the entries.
// Get Best Sellers for last 30 days 
$getbestSellers = func_query("SELECT xcart_order_details.productid, COUNT( * ) AS count
								FROM xcart_order_details
								LEFT JOIN xcart_orders ON ( xcart_order_details.orderid = xcart_orders.orderid )
								WHERE xcart_order_details.productid NOT LIKE '0'
								AND xcart_orders.status NOT LIKE 'F'
								AND xcart_orders.status NOT LIKE 'I'
								AND xcart_orders.status NOT LIKE 'D'
								AND xcart_orders.status NOT LIKE 'X'
								AND xcart_orders.date
								BETWEEN '$lastmonth' AND '$today'
								GROUP BY xcart_order_details.productid
								ORDER BY count DESC 
								LIMIT 20 ");
///  Failsafe in case products have been deleted
$count = 0;
foreach ($getbestSellers as $value){
	$bestsellers["$value[productid]"]['title'] = func_query_first_cell("SELECT product FROM xcart_products
													WHERE productid LIKE $value[productid]");
	$bestsellers["$value[productid]"]['units']	= $value['count'];

										
	if ($bestsellers["$value[productid]"]['title'] != '')
		$count++;
		
	else
		unset($bestsellers["$value[productid]"]);
		
	if ($count == 10)
		break;
	}

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

Next create a new file "/skin1/modules/Bestsellers/newbestsellers.tpl"

Paste in the following code and customize to your liking:

Code:

{capture name=bestsellers}
<table width="100%">
<tr>
	<td align="center">Name</td>
	<td align="center">Units Sold</td>
</tr>
{foreach from=$bestsellers item=best key=key}
<tr>
	<td><a href="product.php?productid={$key}">{$best.title}</a></td>
	<td><a href="product.php?productid={$key}">{$best.units}</a></td>
</tr>
{/foreach}
</table>
{/capture}
{include file="dialog.tpl" title=$lng.lbl_bestsellers content=$smarty.capture.bestsellers extra='width="100%"'}

Next find the following in home.php

Code:

if ($active_modules["Bestsellers"])
	include $xcart_dir."/modules/Bestsellers/bestsellers.php";

Replace with:

Code:

require $xcart_dir."/modules/Bestsellers/newbestsellers.php";

Finally add the following anywhere on your homepage where you want these displayed.

Code:

{include file="modules/Bestsellers/newbestsellers.tpl"}

Enjoy

Edit: Changed MySQL call to exclude failed transactions.

stevekem · #2 02-22-2007, 03:04 PM

Hello,

I tried the code and it does work for me on 4.1.3 on category's and welcome page, but the new bestsellers menu that I copied your code into seems to disappear on the product details page. Do you happen to know why? Thanks!

lachild · #3 02-25-2007, 08:20 PM

Sorry for the long delay. I actually didn't notice the question the first time through..

We only use best sellers on the home page but I did find the code your looking for to fix this on the products page.

locate this in product.php:

Code:

if ($active_modules["Bestsellers"])
	include $xcart_dir."/modules/Bestsellers/bestsellers.php";

replace with

Code:

require $xcart_dir."/modules/Bestsellers/newbestsellers.php";

Also if this is being displayed on the shopping cart then you will need to edit cart.php and add...

Code:

require $xcart_dir."/modules/Bestsellers/newbestsellers.php";

...somewhere to the end of the file before func_display (I believe).

stevekem · #4 03-01-2007, 06:01 PM

Thanks for letting me know. I appreciate it.

- Steve

GMADMIN · #5 08-27-2008, 09:49 AM

Can you tell me: does this present the number of orders with a certain item, or the actual number of units sold in the period? Thanks.

starwest · #6 10-30-2008, 11:46 AM

Can anyone who has tried this mod comment on the performance versus using the stock best sellers module (with advanced statistics)?

Thanks!

Jerrad · #7 12-10-2008, 10:18 AM

Does anybody know how to include thumbnails with the bestsellers?

Thanks!

erik · #8 05-13-2009, 08:04 AM

Can this script be modified to exclude categories from best sellers?

lash · #9 11-05-2009, 06:42 AM

It works fine in 4.1.9 and doesn't slow down the site since you don't need to turn on the advanced statistics. The difference compared the default bestsellers is that this one just show the main bestsellers. X-cart can show bestsellers in different categories. If I go to category B it will show bestseller from that category. This one won't do that and perhaps you don't want it to either...

starwest · #10 11-05-2009, 09:02 AM

Quote:

Originally Posted by lash

It works fine in 4.1.9 and doesn't slow down the site since you don't need to turn on the advanced statistics. The difference compared the default bestsellers is that this one just show the main bestsellers. X-cart can show bestsellers in different categories. If I go to category B it will show bestseller from that category. This one won't do that and perhaps you don't want it to either...

That sums it up pretty well. Good performance, especially versus the default functionality that uses advanced stats, but not much in the way of features. It would be nice to see a mod that provided similar performance and the ability to configure additional features, such as choosing to show best-sellers by category.