Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Bestsellers over 30 days


wizardsandwars

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.

 

SELECT

 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 

FROM  

 " . 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 

WHERE

 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' 

GROUP BY 

 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, 

 m.manufacturers_name 

ORDER BY 

 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

Archived

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

×
×
  • Create New...