Best selling list for a defines period - is it possible?

[CaroleAs]

I like the Best seller sidebox feature, but i think that it can be somewhat misleading.

I did remove the free products from the list with your help, which is somewhat better. However, i wonder if this list is not also biaised by how long a product has been in the store. For example, older products, that have been listed for 2 years, will likely have been sold more often than those uploaded in the last 6 months, but the Best Selling list does not take this into consideration.

Is there a way to limit the list of Best Sellers to the sales within the last 6 months? or a year? or 3 months? or something like that?

[Ajeh]

You could customize the best_sellers.php sidebox with your overrides and build in a time limit ...

I would suggest you look at how the New Products builds the time for the SELECT statements to ensure you are using an efficient SELECT utilizing the date ...

[CaroleAs]

You could customize the best_sellers.php sidebox with your overrides and build in a time limit ...

I would suggest you look at how the New Products builds the time for the SELECT statements to ensure you are using an efficient SELECT utilizing the date ...

Could you direct me to instructions on HOW to do such customization? and what you mean by "how New Products builds the time"? i don't know where to look for that.

[Ajeh]

You can look at how the whats_new.php sidebox manages the SELECT statement for the date and the function that it uses to build the statement ...

It would take a bit for me to sit down and write the code for this ...

How many days do you want to use for the Best Sellers?

How many days do you use currently for New Products?

[CaroleAs]

Currently, the new products are listed from the last month i think, but it must be by default as i dont remember changing anything (didn't even know it COULD be changed).

I am not sure what period i would like to use for the Best Sellers as i would like to see how the list will change. It was easy to see the change when i removed the free products as it was ignoring a product altogether, but for a period... i would like to see and decide on the period that makes the most sense to the customers.

[Ajeh]

You can use your templates and overrides for the best_sellers.php sidebox with:

<?php
/**
 * best_sellers sidebox - displays selected number of (usu top ten) best selling products
 *
 * @package templateSystem
 * @copyright Copyright 2003-2005 Zen Cart Development Team
 * @copyright Portions Copyright 2003 osCommerce
 * @license http://www.zen-cart.com/license/2_0.txt GNU Public License V2.0
 * @version $Id: best_sellers.php 2718 2005-12-28 06:42:39Z drbyte $
 */

// test if box should display
  $show_best_sellers= false;

  if (isset($_GET['products_id'])) {
    if (isset($_SESSION['customer_id'])) {
      $check_query = "select count(*) as count
                      from " . TABLE_CUSTOMERS_INFO . "
                      where customers_info_id = '" . (int)$_SESSION['customer_id'] . "'
                      and global_product_notifications = '1'";

      $check = $db->Execute($check_query);

      if ($check->fields['count'] > 0) {
        $show_best_sellers= true;
      }
    }
  } else {
    $show_best_sellers= true;
  }

  if ($show_best_sellers == true) {

// build date range for best seller products limit
  function zen_get_best_seller_date_range($time_limit = false) {
    // set default if no date set
    if ($time_limit == false) {
      $time_limit = 60;
    }
    // 120 days; 24 hours; 60 mins; 60secs
    $date_range = time() - ($time_limit * 24 * 60 * 60);
    $zc_new_date = date('Ymd', $date_range);
    $best_seller_range = ' and p.products_date_added >=' . $zc_new_date;

    return $best_seller_range;
  }

    // limit best sellers to products added in last 60 days
    $display_limit = zen_get_best_seller_date_range(60);
    if (isset($current_category_id) && ($current_category_id > 0)) {
      $best_sellers_query = "select distinct p.products_id, pd.products_name, p.products_ordered
                             from " . 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 pd.language_id = '" . (int)$_SESSION['languages_id'] . "'
                             and p.products_id = p2c.products_id
                             and p2c.categories_id = c.categories_id
                             and '" . (int)$current_category_id . "' in (c.categories_id, c.parent_id) " . $display_limit . "
                             order by p.products_ordered desc, pd.products_name
                             limit " . MAX_DISPLAY_BESTSELLERS;

      $best_sellers = $db->Execute($best_sellers_query);

    } else {
      $best_sellers_query = "select distinct p.products_id, pd.products_name, p.products_ordered
                             from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd
                             where p.products_status = '1'
                             and p.products_ordered > 0
                             and p.products_id = pd.products_id
                             and pd.language_id = '" . (int)$_SESSION['languages_id'] . "' " . $display_limit . "
                             order by p.products_ordered desc, pd.products_name
                             limit " . MAX_DISPLAY_BESTSELLERS;

      $best_sellers = $db->Execute($best_sellers_query);
    }

    if ($best_sellers->RecordCount() >= MIN_DISPLAY_BESTSELLERS) {
      $title =  BOX_HEADING_BESTSELLERS;
      $box_id =  'bestsellers';
      $rows = 0;
      while (!$best_sellers->EOF) {
        $rows++;
        $bestsellers_list[$rows]['id'] = $best_sellers->fields['products_id'];
        $bestsellers_list[$rows]['name']  = $best_sellers->fields['products_name'];
        $best_sellers->MoveNext();
      }

      $title_link = false;
      require($template->get_template_dir('tpl_best_sellers.php',DIR_WS_TEMPLATE, $current_page_base,'sideboxes'). '/tpl_best_sellers.php');
      $title =  BOX_HEADING_BESTSELLERS;
      require($template->get_template_dir($column_box_default, DIR_WS_TEMPLATE, $current_page_base,'common') . '/' . $column_box_default);
    }
  }
?>

[CaroleAs]

Wow.... Thanks for your time and expertise. I will use that as soon as possible and let you know how it works. :)

[CaroleAs]

This seems to work great but reading the code (at least what i can decipher), i see that it refers to the products ADDED to the strore within that period which is another neat way to look at it. However, is it still possible to include products SOLD in the last 6 months instead of ADDED in the last 6 months?

[Ajeh]

That could be written, but it will take quite a bit of customization, as now you are looking at the orders and orders_products tables rather than the just products table to select the Products from Orders based on the date_purchased ...

[CaroleAs]

Well, it is ok. Maybe if you have time, later down the road, maybe others could need it. For now, i am quite happy with the Best Sellers among the products posted in the last 6 months. It will still give a different list to the visitors than the same old list of old products.

Thanks for your time.