Jump to content
  • Checkout
  • Login
  • Get in touch


The e-commerce.

Bestsellers over 30 days


Recommended Posts

I have the query to display the bestsellers over 30 days, or whatever time period you like, instead of displaying all time bestsellers.


And I have implemented this into my bestsellers module, however, my bestsellers isn't in an info box, but rather like the "new procucts" page.


Anyway, I seem to be unable to integrate this working query into the bestsellers infobox. I'm not sure what the "if ($cPath)" is for, and the 'group by' clause in this query seems to be incompatible with the 'tep_db_num_rows' function. If a more experienced OSC developer could take a look at it, I'm sure it would be a quick fix.


Here the query.



 p.products_id, pd.products_name, pd.products_description,    

 p.products_image, p.products_price, p.products_tax_class_id, 

 IF(s.status, s.specials_new_products_price, NULL) as 

 specials_new_products_price, p.products_date_available, 

 m.manufacturers_name, COUNT(*) num_ordered 


 " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on 

 p.manufacturers_id = m.manufacturers_id left join " .     

 TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id =   

 pd.products_id and pd.language_id = '" . $languages_id . "' left join " . 

 TABLE_SPECIALS . " s on p.products_id = s.products_id, orders t0, 

 orders_products t1 


 p.products_id = t1.products_id AND 

 t0.orders_id = t1.orders_id AND t0.orders_status = '3' AND 

 t0.date_purchased > SUBDATE( now(  ) ,  INTERVAL 30  DAY  ) AND 

 p.products_ordered > 0 and products_status = '1' 


 p.products_id, pd.products_name, pd.products_description, 

 p.products_image, p.products_price, p.products_tax_class_id, 

 specials_new_products_price, p.products_date_available, 



 num_ordered DESC, pd.products_name";


Feel free to run it yourself though phpmyadmin to see the results.


Eventually, I envision a configuration parameter that you can set in admin that would determine the length of time that sales count towards the bestsellers.


NOTE: As of Oct 2006, I'm not as active in this forum as I used to be, but I still work with osC quite a bit.

If you have a question about any of my posts here, your best bet is to contact me though either Email or PM in my profile, and I'll be happy to help.

Link to comment
Share on other sites


This topic is now archived and is closed to further replies.

  • Create New...