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

date posted 4th March 2008 12:46


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

If I've understood this right, you may only need the order items table,
and the query should be fairly simple. Something like:

select product,variant, sum(quantity) as sum_for_variant from
order_items
group by product,variant
order by sum_for_variant limit X;

If product, variant, are numeric you might want to add something to look
up the name and description of the product and variant. "limit X"
selects the top X counts but is a mysql specific dialect.

Graham





> 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
>
--
Graham Stark, Virtual Worlds, http://www.virtual-worlds.biz
Phone (+44) 01908 618239 Mobile (+44) 07952633185 Skype graham_k_stark