homepage Welcome to WebmasterWorld Guest from 23.20.63.27
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
stuck on 6 month product sales report form mysql db
mysql
sukebe




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

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.

 

Sarah Atkinson




msg:4231200
 9:52 pm on Nov 16, 2010 (gmt 0)

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
data_array['month']['selling_level'];
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';

sukebe




msg:4231256
 3:01 am on Nov 17, 2010 (gmt 0)

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}">
{$vr.variant_name}
</option>
{/if}{/if}
{/foreach}
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!

Sarah Atkinson




msg:4231439
 2:46 pm on Nov 17, 2010 (gmt 0)

What language are you using?

sukebe




msg:4231560
 6:47 pm on Nov 17, 2010 (gmt 0)

sorry, this is smarty

sukebe




msg:4231680
 10:58 pm on Nov 17, 2010 (gmt 0)

actually it could be php or smarty

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved