I'm stuck. I have a functioning query which sums sales of products over a period of time. I need to generate a table that shows six months of the top 10 products sold for each month. months are listed in columns.
Here is what I have:
SELECT cscart_order_details.product_code, SUM(cscart_order_details.amount), SUM(cscart_order_details.price)
FROM cscart_order_details
LEFT JOIN cscart_orders
ON cscart_orders.order_id = cscart_order_details.order_id
LEFT JOIN cscart_products
ON cscart_products.product_code = cscart_order_details.product_code
WHERE (cscart_orders.status = 'P' OR cscart_orders.status = 'C')
AND cscart_orders.timestamp BETWEEN UNIX_TIMESTAMP('2010-07-01 00:00:01')
AND UNIX_TIMESTAMP('2010-08-01 00:00:01')
GROUP BY substring(cscart_order_details.product_code, 1, 9) ORDER BY SUM(cscart_order_details.amount) DESC
LIMIT 0, 10
-------------------------------------
Should I just make a loop of 6, based on a start data and run the same query with months based on the loop counter?
I have to show this table as monthly columns, with the products below. That formatting I can handle, however, how should I store the results in order to build each column?
I'm kinda weak on arrays, but if I have
for($f = 0; $f < 6; $f++)
, can I assign the results to month[$f], to create 6 arrays holding the data for each month?
Thanks for the help; brains freezing up on this one.
