Limit new products or bestsellers by date Options Rating

[marco23]

The current query in the contribution basically selects the BESTSELLER LIST by selecting the orders submitted in the last X days - where x is setup in the admin site.

 

It then links to the product to look at the ordered field to and orders based on this field

>>>> order by p.products_ordered desc

 

This means it looks at how many items of this product were sold since the shop went live or since this number was reset to 0. This means that a single Item sold in teh last x day might still push this item to the top of the list. Due to this, I found the contribution not doing what I wanted it to do. I wanted it to show the best sold items in teh last X days only.

 

I modified the queries to do just this. It required going to the order and summing up the sold items for the selected ordered;

 

This is the code I am now working with and I think others would benefit from it. I am not a programmer - so it might be ready for optimalization;

 

<< includes/boxes/best_seller.php Around line 12

 

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

$best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name, sum( op.products_quantity ) AS total_sold

from " . TABLE_PRODUCTS . " p, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c, " . TABLE_CATEGORIES . " c, " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op

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) and op.orders_id = o.orders_id and op.products_id = p.products_id and TO_DAYS(NOW()) - TO_DAYS(o.date_purchased) < '" . BESTSELLER_DAYS . "'

GROUP BY op.products_id

order by total_sold desc, pd.products_name limit " . MAX_DISPLAY_BESTSELLERS);

 

} else {

 

$best_sellers_query = tep_db_query("select distinct p.products_id, pd.products_name, sum( op.products_quantity ) AS total_sold

from " . TABLE_PRODUCTS . " p, " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op, " . 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 . "' and op.orders_id = o.orders_id and op.products_id = p.products_id and TO_DAYS(NOW()) - TO_DAYS(o.date_purchased) < '" . BESTSELLER_DAYS . "'

GROUP BY op.products_id

order by total_sold desc, pd.products_name limit ". MAX_DISPLAY_BESTSELLERS);

});

 

 

This might be a valuable contribution to the existing contribution