No replies to this topic

[wizardsandwars]

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.