Welcome to WebmasterWorld Guest from

Forum Moderators: open

stuck on 6 month product sales report form mysql db


12:19 am on Nov 6, 2010 (gmt 0)

5+ Year Member

Hi all, I need some help.
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.
9:52 pm on Nov 16, 2010 (gmt 0)

10+ Year Member

I think you should just do 6 queries. 1 for each month. as for storing them. You should probably do them in a multidimensional array.
so it would be like
So if blue widgets was your 4th best seller in October your array entry might look like
data_array['October']['4th']='Blue Widget';

or you could do it as numbers
data_array[10][4]='Blue Widget';

if you assigned month[$f]. that wouldn't create 6 arrays that would create 1 array with 6 entries like so:
month[1]='first iteration';
month[2]='second iteration';
3:01 am on Nov 17, 2010 (gmt 0)

5+ Year Member

Thanks so much for the feedback. I'm just getting used to arrays and might feel less intimidated by trying this, now.

The only thing that I can't really figure out is how to access a particular piece of data in a query based on a particular field, without having to loop through it and compare results.
I just constructed this structure to turn on or off a select elements option tag:
{foreach from=$product.option_inventory item="amount" key="options"}
{assign var=qty value=":"|explode:$amount.options}
{if $qty[1] == $vr.variant_id}
{if $amount.amount > 0}
<option value="{$vr.variant_id}">
Where, what I really wanted was more of a sql select option, like:
{assign var=qty value=":"|explode:$amount.options}
$amount = "SELECT amount FROM array WHERE options = $qty[1]";

is there a way to set up an array to have data accessed similarly.

Thanks so much!
2:46 pm on Nov 17, 2010 (gmt 0)

10+ Year Member

What language are you using?
6:47 pm on Nov 17, 2010 (gmt 0)

5+ Year Member

sorry, this is smarty
10:58 pm on Nov 17, 2010 (gmt 0)

5+ Year Member

actually it could be php or smarty

Featured Threads

Hot Threads This Week

Hot Threads This Month