Community Add-Ons

fixed the problem with multilanguage shops:
demo: www.artesanias.de

search in boxes/besteller2.php

in line 31-36:
$bestseller_products_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name, p.products_price, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price from (" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p) left join " . TABLE_SPECIALS . " s on (p.products_id = s.products_id) left join " . TABLE_BESTSELLER . " b on (p.products_id = b.products_id) and p.products_id = pd.products_id where p.products_status = '1' and b.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_BESTSELLER_PRODUCTS);
}
else {
$info_box_contents[] = array('align' => 'left', 'text' => sprintf(TABLE_HEADING_BESTSELLER_PRODUCTS_CATEGORY, $cat_name));
$bestseller_products_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name, p.products_price, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price from (" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p) left join " . TABLE_SPECIALS . " s on (p.products_id = s.products_id) left join " . TABLE_BESTSELLER . " b on (p.products_id = b.products_id) and p.products_id = pd.products_id where p.products_status = '1' and b.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_BESTSELLER_PRODUCTS);
}

replace with:

$bestseller_products_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name, p.products_price, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price from (" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p) left join " . TABLE_SPECIALS . " s on (p.products_id = s.products_id) left join " . TABLE_BESTSELLER . " b on (p.products_id = b.products_id) and p.products_id = pd.products_id where language_id = '" . (int)$languages_id . "' and p.products_status = '1' and b.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_BESTSELLER_PRODUCTS);
}
else {
$info_box_contents[] = array('align' => 'left', 'text' => sprintf(TABLE_HEADING_BESTSELLER_PRODUCTS_CATEGORY, $cat_name));
$bestseller_products_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name, p.products_price, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price from (" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p) left join " . TABLE_SPECIALS . " s on (p.products_id = s.products_id) left join " . TABLE_BESTSELLER . " b on (p.products_id = b.products_id) and p.products_id = pd.products_id where language_id = '" . (int)$languages_id . "' and p.products_status = '1' and b.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_BESTSELLER_PRODUCTS);
}

Fixed the problem that the constribution didn't work with mysql 5++, only with version 4 and lower....

You can see working constribution with MySQL 5.0.21 on www.artesanias.de/catalog
HF! :)

It was a Left Join Bug, look here: http://bugs.mysql.com/bug.php?id=13551

-------------------------
Manual Installation
-------------------------

Find in catalogincludesboxesbestseller2.php :

$bestseller_products_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name, p.products_price, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price from (" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p) left join " . TABLE_SPECIALS . " s on (p.products_id = s.products_id) left join " . TABLE_BESTSELLER . " b on (p.products_id = b.products_id) and p.products_id = pd.products_id where p.products_status = '1' and b.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_BESTSELLER_PRODUCTS);
}
else {
$info_box_contents[] = array('align' => 'left', 'text' => sprintf(TABLE_HEADING_BESTSELLER_PRODUCTS_CATEGORY, $cat_name));
$bestseller_products_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name, p.products_price, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id left join " . TABLE_BESTSELLER . " b on p.products_id = b.products_id and p.products_id = pd.products_id where p.products_status = '1' and b.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_BESTSELLER_PRODUCTS);

and replace with:

// left join bug
$bestseller_products_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name, p.products_price, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price from (" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p) left join " . TABLE_SPECIALS . " s on (p.products_id = s.products_id) left join " . TABLE_BESTSELLER . " b on (p.products_id = b.products_id) and p.products_id = pd.products_id where p.products_status = '1' and b.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_BESTSELLER_PRODUCTS);
}
else {
$info_box_contents[] = array('align' => 'left', 'text' => sprintf(TABLE_HEADING_BESTSELLER_PRODUCTS_CATEGORY, $cat_name));
$bestseller_products_query = tep_db_query("select distinct p.products_id, p.products_image, pd.products_name, p.products_price, p.products_tax_class_id, s.status as specstat, s.specials_new_products_price from (" . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p) left join " . TABLE_SPECIALS . " s on (p.products_id = s.products_id) left join " . TABLE_BESTSELLER . " b on (p.products_id = b.products_id) and p.products_id = pd.products_id where p.products_status = '1' and b.status = '1' order by rand($mtm) DESC limit " . MAX_DISPLAY_BESTSELLER_PRODUCTS);



--------------------------
Automatic installation:
--------------------------


replace the catalogincludesboxesbestseller2.php

(I used version from 24 Jul 2006 so if you got an other version of this php dont replace it and do it manually as described above)



File is attached. HF :)

German translation and enable, disable box in the OSC Configuration

I added all files to the attached zip file.


Changed Files:
--------------
adminincludeslanguagesgermanbestseller2.php
adminincludeslanguagesenglishbestseller2.php
includeslanguagesgermanbestseller2.php
includescolumn_right.php


Changes:
--------
- German Translation

- Add Enhanced BestSeller box with admin ToConfig.sql
defines two new Constants in the Configuration table:
SHOW_BESTSELLERS_BOX_ORIGINAL
SHOW_BESTSELLERS_BOX_ENHANCED

includescolumn_right.php now takes care of those constants:

// tom 001 Start
if ( SHOW_BESTSELLERS_BOX_ORIGINAL == 'true' ) {
include(DIR_WS_BOXES . 'best_sellers.php');
}
if ( SHOW_BESTSELLERS_BOX_ENHANCED == 'true' ) {
// Enhanced BestSeller box with admin
include(DIR_WS_BOXES . 'bestseller2.php');
}
// tom 001 End


Installation:
-------------

1 - Execute "Add Enhanced BestSeller box with admin ToConfig.sql"

2 - Merge the other files as necessary


24.7.2006
Thomas Schittli

If you want to display the images in the info box as thumbnails and have ''On the Fly' Auto Thumbnailer using GD Library' installed follow the instructions in the enclosed file.

Just one simple code change in bestseller2.php

Andy

Solution to the problem described below is:

edit /includes/classes/split_page_results.php
find
$this->sql_query .= " limit " . $offset . ", " . $this->number_of_rows_per_page;
(line about 66)
insert before it
if($offset <0 ) $offset = 0;

the same in admin//includes/classes/split_page_results.php
do the same, but this time it's line about 38

Installed as mentioned in the Readme. Link in Admin panel gives me the following error

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-20, 20' at line 1

select p.products_id, pd.products_name, s.bestseller_id, s.bestseller_date_added, s.bestseller_last_modified, s.expires_date, s.date_status_change, s.status from products p, bestseller s, products_description pd where p.products_id = pd.products_id and pd.language_id = '1' and p.products_id = s.products_id order by pd.products_name limit -20, 20

Ok heres a version for use with the STS Template System.

If you haven't got STS then don't use this version.

Just an update to the installation instructions to take care of a couple of inconsistances with filename defines.

Would like the package to also include instructions for adding to an STS template system site. If I have the time I will work on it and add it here

Forum posts for this contribution can be found here http://forums.oscommerce.com/index.php?showtopic=126500

I had a request to append the new readme file to the contrib, so I did.
later,
Amitai

Cleaned up the installation file a little bit to prevent confusion.
Previous readme bug fix included.

Fixed a little Bug in the Readme.txt
Link in Admin Controll doesn't work

I forgot to load one file:
includes/classes/boxes.php

thank you Marc for pointing that out.

Best Sellers- This contribution (V1.4) includes all the files and instructions that I used in my own installation to get things up and running in V2.2 MS2. I have incorporated other contibutions for this mod, and also don't take any responsibility if this doesnt work!
I you install this contrib your best seller box will:
-show thumb nails of the product.
-name of the product
-price of the product
-scroller for IE users else will display regular best_seller box
can be viewed at: www.theartoutlet.com

---
WHAT IS IT?
---
The purpose of this contribution is to replace the best seller Infobox that appears on the left or right column, to show selected products in the bestseller box, instead of actualy purchased products. The best sellers products can be chosen in the admin.

However, this mod could be improved by displaying the best sellers choosen for particular cPaths,like the original box does.

It's been written in such a way that the box will *not* show up if there are no bestsellers chosen.