Best way to accomplish this...?

[tdob]

Hi.

I want to generate a table header row containing the week for the previous 8 weeks.

Then, for a list of about 50 items I need to display the data for these weeks.

For example:

Item - Week1 - Week2 - Week3 - Week4 - Week5 - Week6 - Week7 - Week8
Item1
Item2
Item3
Item4
Item5
Item6

I can easily output the results in a table, but have never had to output to a table like this, where each column heading corresponds to a certain week. Not all of the items will have data for every week, so if I just dump the output it will not line up properly. When mySQL produces the results, I will have a new row for every week/item number combination. So I am not sure how to manage this?

Hopefully I explained my situation well enough. I am sure this is something people do frequently, and was curious if anyone had a simple way to accomplish this, they way I am thinking would be pretty ugly.

[AnthonySterling]

Sometime I find sketching out a unit of the proposed data helps out when figuring out this kind of thing.

Say for example...

<?php
$aArray = array(
    array(
        'week_one'        =>    'data',
        'week_two'        =>    'data',
        'week_three'    =>    'data',
        'week_four'        =>    'data',
        'week_five'        =>    'data',
        'week_six'        =>    'data',
        'week_seven'    =>    'data',
        'week_eight'    =>    'data',
    ),
    array(
        'week_one'        =>    'data',
        'week_two'        =>    'data',
        'week_three'    =>    null,
        'week_four'        =>    'data',
        'week_five'        =>    'data',
        'week_six'        =>    null,
        'week_seven'    =>    'data',
        'week_eight'    =>    'data',
    )
);
?>

Doing what you ask with that data structure would be a doddle, can you get the database to return this?

[tdob]

Thanks for the reply.

I suppose I am not sure how to get the data in the format that I need.

The first task is getting the previous 8 weeks. I need the date of the first day of the week for the previous 8 weeks. For example 2009-07-20, 2009-07-13, 2009-07-06, etc...

The second task is getting an array for each item containing a key of the dates as mentioned above and a value of whatever the value is for that week.

Im not really sure how to go about this.

[tdob]

Ok, so to add some more detail to this.

I am currently using this code in order to get the start date of the last 8 weeks;

function datefromweek ($y, $w, $o) { 
    $days = ($w - 1) * 7 + $o; 

    $firstdayofyear = getdate(mktime(0,0,0,1,1,$y)); 
    if ($firstdayofyear["wday"] == 0) $firstdayofyear["wday"] += 7; 
# in getdate, Sunday is 0 instead of 7 
    $firstmonday = getdate(mktime(0,0,0,1,1-$firstdayofyear["wday"]+1,$y)); 
    $calcdate = getdate(mktime(0,0,0,$firstmonday["mon"], $firstmonday["mday"]+$days,$firstmonday["year"])); 

    $date = $calcdate["year"] . "-" . $calcdate["mon"] . "-" . $calcdate["mday"];
    return($date); 
} 


for ($i=1; $i<9; $i++) {
$year=date('Y');
$week=date('W'); 
$week_array[]=datefromweek($year, $week, $offset);
$offset-=7;
} 

This gives me my desired dates. The array looks like this:
Array ( [0] => 2009-7-20 [1] => 2009-7-13 [2] => 2009-7-6 [3] => 2009-6-29 [4] => 2009-6-22 [5] => 2009-6-15 [6] => 2009-6-8 [7] => 2009-6-1 [8] => 2009-5-25 [9] => 2009-5-18 [10] => 2009-5-11 [11] => 2009-5-4 )

So now, how would I organize my data, so I can fit the values into the appropiate date, for the appropiate item?

Essentially, what I would need is something like...

<?php
$DATA = array (
  ITEM 1 (
     2009-7-20 => 55
     2009-7-13 => 12
     2009-7-06 => 167.1 )
  ITEM 2 (
     2009-7-20 => 11
     2009-7-13 => 67
     2009-7-06 => 4 )
)
etc...
?>

I am not sure how to get it formatted like this, as my output from the database after I perform some calculations looks like this on a row-by-row basis:

Item 3 --- 2009-06-29 --- 11.1
Item 5 --- 2009-06-29 --- 9000.1
Item 1 --- 2009-06-29 --- 12.5
Item 2 --- 2009-06-29 --- 357.1
Item 6 --- 2009-07-06 --- 208.3
Item 3 --- 2009-07-06 --- 11250.3
Item 1 --- 2009-07-06 --- 167.1

[tdob]

Anyone have any ideas?