Best sellers - timed

[Connectage]

Hi all

I have a requirement to create a best sellers sidebox, but only for an arbitrary amount of time (say the last 3 months). Having had a quick look at the code for the current best-sellers sidebox, that seems to be for the length of the store operation, as I can't find a setting to change that.

So my questions are
a) Have I missed it, and there really is a time for the best-sellers?
b) Has this been done before, and my searching needs refining?

I don't want to re-invent the wheel, and while coding the sql and php required for this isn't hard I'd rather adapt an existing sidebox of this type (if it exists.

Thanks!

Ben

[Connectage]

In case this is of use to others, here is the SQL you need.

SELECT zop.products_id, count(*) as total, MAX(zo.date_purchased) as LastPurchased
  					FROM ' . TABLE_ORDERS . ' zo, ' . TABLE_ORDERS_PRODUCTS . ' zop, '  					
  					. TABLE_PRODUCTS . ' p 
  					WHERE zo.orders_id = zop.orders_id ' .  					
  					' AND p.products_id = zop.products_id
  					AND p.products_status = "1"
  					AND zo.date_purchased > ( STR_TO_DATE("' . BEN_BESTSELLERS_TIMED_START . '","%Y-%m-%d") ) 
  					AND zo.date_purchased < ( STR_TO_DATE("' . BEN_BESTSELLERS_TIMED_FINISH . '","%Y-%m-%d") )
  					GROUP BY zop.products_id
  					ORDER BY total DESC, LastPurchased DESC 
  					LIMIT ' . MAX_DISPLAY_BESTSELLERS;

And then, once you've got that list of best-sellers for the time period, you then go away and do a normal SQL search to return the product information.

$lsBooks = implode(',', $laBooks);
    if(!$lsBooks) { $lsBooks = '-1'; }
         	
  	$lsSelect = ' SELECT distinct p.products_id, pd.products_name, p.products_ordered
      				, pd.products_description, p.products_image, p.master_categories_id ';  	
  	$lsFrom = ' FROM ' . TABLE_PRODUCTS . ' p, ' . TABLE_PRODUCTS_DESCRIPTION .' pd ';                      
  	$lsWhere =  " where p.products_status = '1'                     
                     AND p.products_id = pd.products_id
                     AND pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                     AND p.products_id IN ($lsBooks)";

The two definitions (BEN_BESTSELLERS_TIMED_START, etc) have been added to the admin on the site I've used this on. The rest of the sidebox was simple compared to getting the SQL right, so I thought I'd post this in case it helps someone else.

[blarney]

I am trying to create a similar situation (ZC v1.3.8a) but where the best sellers automatically pulls only the records from the most recent 30 days. I know just enough PHP to be dangerous but figure with some help, maybe this can get sorted out and benefit others as well. I"m working in the /includes/modules/[customtemplate]/best_sellers_reloaded.php (same code as in the best seller sidebox). Here's what I have:

Near the top of the page I defined my one month time like this:

$lastmonth = mktime(0, 0, 0, date("m"), date("d")-30, date("y"));

I modified the query as follows:

$best_sellers_query = "select distinct p.products_id, p.products_image, pd.products_name, p.products_ordered
from " . TABLE_ORDERS . ' o, ' . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, "
. TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c
where p.products_status = '1'
and p.products_ordered > 0
and p.products_id = pd.products_id
and p2c.products_id = p.products_id
and o.date_purchased => date('m/d/y', $lastmonth)
and p2c.categories_id = 2
and pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
and p.products_quantity > 0
order by p.products_ordered desc, pd.products_name
limit " . MAX_DISPLAY_SEARCH_RESULTS_BEST_SELLERS;
}

I assume my problem is in the "date('m/d/y', $lastmonth)" but not sure how it should be. I tried Y-m-d H:i:s instead of m/d/y thinking it might recognize it but no joy. Tried with and w/o quotes too. An extra set of more experienced eyes would be appreciated. Thanks.

Jim

[blarney]

There was a typo in the string where I inverted the >= symbol. Supposed to be:

and o.date_purchased >= date('m/d/y', $lastmonth)

I also tried using

and o.date_purchased >= $lastmonth

without formating the $lastmonth variable but that was also unsuccessful.

Any suggestions? Thanks.

Jim