best sellers in a time period

[pars]

how do I set the best seller box to show the best sold products in a certain period of time?

 

e.g. best sellers in past 4 weeks.

 

thanks :)

[♥geoffreywalton]

The qty in products ordered is total sold since the shop opened.

 

You could zeroise the qty ordered and then recalculate the qty sold in the last x days and use that.

 

First part is

 

update products set products_ordered = 0

[♥geoffreywalton]

And part 2a is

 

SELECT products_id,SUM(products_quantity) FROM orders_products GROUP BY products_id

[dmnalven]

nevermind

[♥geoffreywalton]

And this is part 2b

 

SELECT op.products_id, SUM( op.products_quantity )

FROM orders_products op, orders o

WHERE o.orders_id = op.orders_id

AND datediff(now(), o.date_purchased) < 28

GROUP BY op.products_id

 

off to dev.mysql.com again

[♥geoffreywalton]

This seems to do it

 

update products set products_ordered = 0

 

UPDATE products p SET products_ordered = ( SELECT SUM( op.products_quantity )

FROM orders_products op, orders o

WHERE p.products_id = op.products_id

AND o.orders_id = op.orders_id

AND datediff( now(), o.date_purchased) < 28

GROUP BY op.products_id )

 

But don't trust me, backup first.

[pars]

thanks for your answer dear "geoffreywalton". that was great. but assume that I'm newbie! :S

would you mind explain for me in more simple words? where should make these changes in my shop and does it have modules to do such a thing?