FN-FORUM: Help/guidance with a bit of tricky php/MySQL

date posted 4th March 2008 11:36

Morning all!

Hoping someone can help out with this - a little tricky!

The background:
I have an online shop, which has products (eg car) and product
variants (eg red car, blue car, etc...). Some products have variants,
and some products don't.
When an order is placed I have an Orders table which stores the userid
of customer ordering, order date, etc.. Then, for each product without
variant, and for each product variant, I add an entry to Order_items
which stores the productid (if product doesn't have variant), or
productid AND variant (if it does), along with the quantity for that
order.

What I want to do:
Be able to generate a report of the top 10,20, or x products or
product variants which are selling.

How to do it:
The lame way of doing it would be to go through the products and (if
applicable) their variants, and for each, querying Order_items,
returning all the entries, and totalling up their quantity fields, and
storing this into somekind of array. I am not very good with joins,
which I am guessing may also be needed. I can think of doing this
otherwise by storing the results in a table, but with over 4,000
orders and 18,000 order items, I need to be thinking about a very
efficient algorithm.

Over to you, anyone with any ideas to point me in the right direction?

Many thanks,

Duncan