Best sellers by ORDERED times, not by TOTAL QUANTITY SOLD

[here2learn]

The Best Sellers box sorts the products by quantity sold, not by how many times a product was purchased. Since I work with different products, some people buy x_product 100+ at once, while other products they will rarely buy more than 1.

 

In other words, If I sell a product1 3 times totalling a quantity of 1500, and sell product2 90 times totalling a quantity of 90, it's the product1 that will make it to the Best Sellers list. I want it to be product2, that although sold a lower quantity, it was ordered many times more than product1.

 

Help, please?

 

Here is the (rather small) stock file, if someone with understanding could do an "on the fly" change, I would really appreaciate.

 

<?php
/*
 $Id: best_sellers.php 1739 2007-12-20 00:52:16Z hpdl $

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

 Copyright (c) 2003 osCommerce

 Released under the GNU General Public License
*/

 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 {
   $best_sellers_query = tep_db_query("select distinct p.products_id, 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);
 }

 if (tep_db_num_rows($best_sellers_query) >= MIN_DISPLAY_BESTSELLERS) {
?>
<!-- best_sellers //-->
         <tr>
           <td>
<?php
   $info_box_contents = array();
   $info_box_contents[] = array('text' => BOX_HEADING_BESTSELLERS);

   new infoBoxHeading($info_box_contents, false, false);

   $rows = 0;
   $bestsellers_list = '<table border="0" width="100%" cellspacing="0" cellpadding="1">';
   while ($best_sellers = tep_db_fetch_array($best_sellers_query)) {
     $rows++;
     $bestsellers_list .= '<tr><td class="infoBoxContents" valign="top">' . tep_row_number_format($rows) . '.</td><td class="infoBoxContents"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $best_sellers['products_id']) . '">' . $best_sellers['products_name'] . '</a></td></tr>';
   }
   $bestsellers_list .= '</table>';

   $info_box_contents = array();
   $info_box_contents[] = array('text' => $bestsellers_list);

   new infoBox($info_box_contents);
?>
           </td>
         </tr>
<!-- best_sellers_eof //-->
<?php
 }
?>

[burt]

That data is contained in the orders_products table.

 

You will need to join the table to the SQL, and count the number of orders per product. It's not straightforward.

[here2learn]

Yeah, I checked it up yesterday and orders_products table seems the correct place to select the info from. Will it alone be enough? I mean, it contains the three needed data: how many times a products_id was purchased, the name of the product, and the ID.

 

I just need to learn how to count equal values in a column in MySQL.

 

I am quite noob, but until now I was able to do alot by myself. I will try this one, but a hand would be welcome too. It is the last thing I have to do, before putting up my shop online.

[burt]

That table alone is not enough. You need to join the table to the already existing SQL query.

[here2learn]

Hi Burt, I came up with a solution that uses only the orders_products table, but as you know, it ignores the category_id (if set), so it will display the most purchased products regardless of category_id. Here's the stock installation:

 

  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 {

   $best_sellers_query = tep_db_query("select distinct p.products_id, 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);

 }

 

Here's my mod:

 

  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 {

   $best_sellers_query = tep_db_query("
SELECT COUNT(*) as products_id, products_name
   FROM orders_products
   GROUP BY products_name
   HAVING products_id > 1
limit " . MAX_DISPLAY_BESTSELLERS);

 }

 

The second part (after the else statement) will sort the best sellers by most purchased products. The first part is unaltered because it checks if there's a category_id set, and then show only the most purchased (by quantity) of that given category. It is hard for me, but if you or somebody else could help me to alter the first part (where it compares category_id), I would thrown this mod in the Contribution page, since there is no other available.

 

I guess I am halfway done, heheh.