Sort Products in Categories by Best Sellers

[homewetbar]

Hello,

 

I am wanting an alternative way to sort products, instead of sort by price or by name how about sort by best sellers or sort by name but the first three listings be best sellers followed by sort by name? Since the best sellers are the ones people like most it should help capture interest to improve the odds of someone browsing further, and making that important sale....

 

 

I know it can be done, osCommerce is pretty flexible, so here is what I was thinking:

 

1. Add another field to the products table that stores sales in the last 30 days we'll call it sales_30

2. Run a cron job at midnight everyday that updates this field with the total sales for the last 30 days for each product. (that way each product's sales do not have to be added up on every visit to the category, they will up updated only once a day)

3. Query and sort by the sales_30 field when the category is shown.

 

 

What do you all think, is my logic correct? My main problem is I do not know anything about cron jobs. Can anyone offer help on how to setup a cron job to do this?

 

Thanks for your help in developing this!

[homewetbar]

Ideas anyone?

[choosealogin]

Most of the work is done by taking this category specific query from the bestseller box code.

 

if (isset($current_category_id) && ($current_category_id > 0)) {

$best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name 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)$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) order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);

} else {

 

I didn't give it any great deal of thought...but it's an idea :huh:

 

Ideas anyone?

[homewetbar]

Most of the work is done by taking this category specific query from the bestseller box

 

 

Yeah I started there but it needs to group them and count the specific times so it needs to be modified I think, here is what I've come up with so far:

 

<?php
/*
 $Id: cron_update_bestsellers.php,v 1.0 2006/06/10 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 [url=http://www.oscommerce.com]http://www.oscommerce.com[/url]

 Copyright ? 2003 osCommerce

 Released under the GNU General Public License
*/

 require('includes/application_top.php');

// Setting days to display for ranking < 30
 $best_sellers_query = tep_db_query("select distinct p.products_id, count(op.products_quantity) as sales_count from " . TABLE_PRODUCTS . " p, " . TABLE_ORDERS . " o, "  . TABLE_ORDERS_PRODUCTS . " op where op.orders_id = o.orders_id and op.products_id = p.products_id and TO_DAYS(NOW()) - TO_DAYS(o.date_purchased) < 30 group by p.products_id order by p.products_id");

$rows = 0;
while ($best_sellers = tep_db_fetch_array($best_sellers_query)) {
  $rows++;
  echo $best_sellers['products_id'] . $best_sellers['sales_count'];
?>

 

I'm trying to just output the sales for the last 30 days for each product to start with and then I'll try to input it into the database.

 

I'll post updates as I figure out more, also I'd love more suggestions

[homewetbar]

Ok so here is what I've come up with, let me know what you think.

 

1. Created a new field in the products table using MYSQL named 'products_recent_sales' type INT size 5

 

2. Here is my cron_update_bestsellers.php put in my catlog directory, this will need to be run everynight to update sales for yesterday and the last 30 days:

<?php
/*
 $Id: cron_update_bestsellers.php,v 1.0 2006/06/10 hpdl Exp $

 osCommerce, Open Source E-Commerce Solutions
 http://www.oscommerce.com

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

 require('includes/application_top.php');

// Setting days to display for ranking < 30 counting number of rows so if someone purchases multiple of the same item on one order only one wil be counted. You cna also use sum() instead of count() on the query below if you would prefer.
 $best_sellers_query = tep_db_query("select distinct p.products_id, count(op.products_quantity) as sales_count from " . TABLE_PRODUCTS . " p, " . TABLE_ORDERS . " o, "  . TABLE_ORDERS_PRODUCTS . " op where op.orders_id = o.orders_id and op.products_id = p.products_id and TO_DAYS(NOW()) - TO_DAYS(o.date_purchased) < 30 group by p.products_id order by products_id");

while ($best_sellers = tep_db_fetch_array($best_sellers_query)) {
  	  tep_db_query("UPDATE " . TABLE_PRODUCTS . " SET products_recent_sales = '" . $best_sellers['sales_count'] . "' WHERE products_id = '" . $best_sellers['products_id'] . "'");
}

?>

 

3. I setup a cron job to call my cron_update_bestsellers.php at 3am everyday:

 

/usr/bin/php /home/public_html/cron_update_bestsellers.php

 

You will just need to change the /home/public_html/ part to match your server and catalog directory. (Hint try looking in Admin -> Tools -> Server Info look for the variable ' DOCUMENT_ROOT ' that should be the path you need + your catalog directory.)

 

4. Now we just need to modify the categories listing index.php to display items based on 'products_recent_sales' and we should be done.

 

 

Let me know what you guys think and if you have a better way of doing this I'd love to hear it! :thumbsup:

[homewetbar]

Now to create the drop down follow the instructions in this forum:

http://www.oscommerce.com/forums/index.php?showtopic=131083

[homewetbar]

Now to create the drop down follow the instructions in this forum:

http://www.oscommerce.com/forums/index.php?showtopic=131083

 

 

Humm having a little more trouble than I thought adding a new item to the sort list, ideas anyone?

[homewetbar]

This will give you a nice little drop down to sort by price and name:

 

  // Additional Products Sort
 echo '<td align="center" class="main">' . tep_draw_form('sort', FILENAME_DEFAULT, 'get') . '<b>Sort by:</b> ';

 if (isset($HTTP_GET_VARS['manufacturers_id'])) {
	 echo tep_draw_hidden_field('manufacturers_id', $HTTP_GET_VARS['manufacturers_id']);
   } else {
	 echo tep_draw_hidden_field('cPath', $cPath);
}

 $sort_list = array(  '2a' => 'Sort by Products Name A to Z',
					'2d' => 'Sort by Products Name Z to A',
					'3a' => 'Sort by Price Low to High',
					'3d' => 'Sort by Price High to Low');
 foreach($sort_list as $id=>$text) {
  $sort_range[] = array('id' => $id, 'text' => $text);
 }

 echo tep_draw_pull_down_menu('sort', $sort_range, (isset($HTTP_GET_VARS['sort']) ? $HTTP_GET_VARS['sort'] : ''), 'onchange="this.form.submit()"');
 echo tep_draw_hidden_field('filter_id', (isset($HTTP_GET_VARS['filter_id']) ? $HTTP_GET_VARS['filter_id'] : ''));
 echo '</form></td>' . "\n";
// End Additional Products Sort

 

Then I used steps 1 & 2 of this forum to add my new field to sort by to the db after adding the previous code and set it to '8' in configuration : http://www.oscommerce.com/forums/index.php?sho...85entry180285

 

I also added

'8a' => 'Best Sellers',

 

after

'2a' => 'Products Name A to Z'

 

in index.php but nothing.

 

Ideas anyone????

[homewetbar]

Figured it out, I had to set the best sellers column to 5 it wouldn't work any other way.

 

Created a contrib for everyone to benefit:

http://www.oscommerce.com/community/contributions,4312