SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best way to accomplish this...?

    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.

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Sometime I find sketching out a unit of the proposed data helps out when figuring out this kind of thing.

    Say for example...
    PHP Code:
    <?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?
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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;

    PHP Code:
    function datefromweek ($y$w$o) { 
        
    $days = ($w 1) * $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 Code:
    <?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 => )
    )
    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

  5. #5
    SitePoint Addict
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Anyone have any ideas?


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •