wizardsandwars Posted May 21, 2003 Posted May 21, 2003 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.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.