BestSellers Box Enhancement

[michael_s]

This is a fusion between Random bestsellers 0.2 @ http://www.oscommerce.com/community/contributions,3304 to display random bestsellers using the last version of this modification. All credits to its creators.

File includes random_bestsellers.sql and Read me file with modified text.

Note: This is my first cont and you can see it live @ http://vivipro.org

Enjoy!!

##########
--------------------------------------------------
-- Enhanced Bestsellers Box Plus Random Bestsellers 0.2 --------------------------------------------------
Adapted By vivipro.com / All credit goes to is original developers.
about:

This is a small contribution, wich changes the database query for best sellers. After this modification the displayed
best sellers are randomly selected. I needed this feature, because I have several best sellers wich cannot displayed at once.
I wanted not only the 5 top sellers, but 5 randomly from the first 20 best sellers. You can change the number of selected
best sellers ("Select Best Sellers"), and the number of displayed best sellers ("Best Sellers") in the admin-section of
your osCommerce installation.

Requirements:
- MySQL 4.1 or above
- osCommerce 2.2 MS2

Installation:

----- OPEN ---------------------------------
includes/boxes/best_sellers.php

----- FIND ---------------------------------
if (isset($current_category_id) && ($current_category_id > 0)) {
$best_sellers_query = tep_db_query("select distinct p.products_id, p.products_image,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 {
$best_sellers_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name 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)$languages_id . "' order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);
}

----- REPLACE WITH -------------------------
$best_sellers_query = tep_db_query("SELECT *, RAND() AS rand_id FROM (select distinct p.products_id, p.products_image,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.") AS a ORDER BY rand_id LIMIT " . MAX_DISPLAY_BESTSELLERS);
} else {
$best_sellers_query = tep_db_query("SELECT *, RAND() AS rand_id FROM (select distinct p.products_id, p.products_image, pd.products_name 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)$languages_id . "' order by p.products_ordered desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS.") AS a ORDER BY rand_id LIMIT " . MAX_DISPLAY_BESTSELLERS);

}


----- IMPORT INTO DATABASE -----------------
random_bestsellers.sql

More...