sorting data by week.

[blueman378]

Hi there guys,

 

Well,

I am givin an array in a format something like below,

 

my issue is i must sort it in two differnt formats,

 

one is monday-sunday groupings

 

the other is simply 7 day groupings

 

eg from 2009-12-01

till 2009-12-07

 

and end up with an array like

$sales[2009-12-01 2009-12-07][quantity] = 15

$sales[2009-12-01 2009-12-07][sales] = 2500

 

$sales[2009-12-01 2009-12-07][quantity] = 15

$sales[2009-12-01 2009-12-07][sales] = 2500

 

so for each sales that fits in each group, i must increment quantity by 1, and also add the order_amount to sales.

 

please set me on the right track and i will work the rest.

 

regards,

Matt

 

   (
            [order_date] => 2009-12-01 14:49:49
            [order_amount] => 19.50
        )

    [1] => Array
        ( 
            [order_date] => 2009-12-08 15:20:51
            [order_amount] => 16.00
        )

    [2] => Array
        (
            [order_date] => 2009-12-08 15:00:48
            [order_amount] => 16.00
        )

    [3] => Array
        (
            [order_date] => 2009-12-08 15:32:25
            [order_amount] => 25.75
        )

    [4] => Array
        (
            [order_date] => 2009-12-08 15:38:05
            [order_amount] => 23.00
        )

......................continued.......................

    [149] => Array
        (
            [order_date] => 2009-12-21 17:57:37
            [order_amount] => 16.00
        )

    [150] => Array
        (
            [order_date] => 2009-12-21 18:00:41
            [order_amount] => 8.00
        )

    [151] => Array
        (
            [order_date] => 2009-12-21 18:13:01
            [order_amount] => 16.00
        )

    [152] => Array
        (
            [order_date] => 2009-12-21 18:20:49
            [order_amount] => 16.00
        )

    [153] => Array
        (
            [order_date] => 2009-12-21 18:43:43
            [order_amount] => 16.00
        )

    [154] => Array
        (
            [order_date] => 2009-12-21 19:45:38
            [order_amount] => 53.50
        )

    [155] => Array
        (
            [order_date] => 2009-12-22 05:02:53
            [order_amount] => 16.00
        )

    [156] => Array
        (
            [order_date] => 2009-12-22 17:51:41
            [order_amount] => 16.00
        )

    [157] => Array
        (
            [order_date] => 2009-12-22 18:09:15
            [order_amount] => 16.00
        )

    [158] => Array
        (
            [order_date] => 2009-12-22 18:13:03
            [order_amount] => 8.00
        )

    [159] => Array
        (
            [order_date] => 2009-12-28 12:12:40
            [order_amount] => 16.00
        )

)

[PFMaBiSmAd]

If this data is in a database, it is usually much simpler to group the data the way you want in in the query and use aggregate group functions to get a count and sum of the items within each group.

[blueman378]

Hi PFMaBiSmAd,

 

Thanks for your reply,

 

i've been looking into this but im not to quite to sure how i would go about it.

 

Am i

 

a) creating the date ranges and grabbing all the data that is between those two dates and summing those,

then running it on the next seven day period?

 

eg

 

function groupByWeek($date,$date2)
{
    $dates = Array();

    $date1 = new DateTime($date);
    
    //isBetween is defined somewhere else
    //Start date, End date, Date to check
    while(isBetween($date,$date2,$date)
    {
        $dateTemp = $date->modify('+6 days');
        $range = "$date1."-".$dateTemp";

        $query = "SELECT order_amount, SUM(order_amount) FROM order_mian order_date WHERE order_date BETWEEN '$date' AND '$date2'"; 
        $result = mysql_query($query);

        $row =  mysql_fetch_array($result);

        $dates[$range]['sum'] = $row['SUM(order_amount)'];
        $date1 = $date->modify('+7 days');
    }

    return $dates;
}

 

i dont even know if that will work, but am i doing that,

or can i do something like

 

$query = "SELECT order_amount, FROM order_main GROUP BY[ order_date WHERE order_date BETWEEN '$date' AND '$date2']";

 

the latter would be the best i believe.

[blueman378]

ok, im not sure if i should get this topic moved to the sql section or not now,

 

but im almost there,

 

my query is

 

SELECT Week(order_date), Min(order_date), Max(order_date), Sum(order_amount), count(order_amount)
FROM order_main
WHERE location_id='6'
AND order_date BETWEEN '2010-01-01' AND '2010-01-26'
GROUP BY week(order_date);

 

now the only thing i have left to do is,

where is has "Min(order_date), Max(order_date)"

 

i need to wrap them in something like

 

Monday(Min(order_date)), Sunday(Max(order_date))

 

ie, i need to get the date of the monday of the week of the date contained in Min(order_date)

and the sunday for Max(order_date)

 

are there functions to do this or will i have to do this php side?