Calculate number of weeks based on dates using PHP

I would like to be able to output a timeline containing the number of weeks from a series of dates contained in a table. So for example, let's say I've got the dates 12/9/10 (Thursday), 12/13/10 (Monday), 12/15/10 (Wednesday), and 12/21/10 (Tuesday), each in a record in a MySQL table.

What I would want outputted is something that calculates the number of weeks based on those dates, so like:

Week 1: 12/9/10 Week 2: 12/13/10, 12/15/10 Week 3: 12/21/10

I know how to get the week number in an given year (so like today we're in week 49) but since I could have ANY date range, I'm calculating the weeks, not the week of the year.

I could simply convert weeks of the year to a count and display them sequentially (for if the dates start in week 49 and go through week 52, week 49 = 1, week 50 = 2, etc.) but if I have dates that span over 2 years (like 12/25/10 through 1/2/11) that's problematic.

Any help would be greatly appreciated! I don't need the MySQL code--just the date string conversion. I've been spinning my wheels on this one!

UPDATE: Just thought I'd share the code that finally solved this. This isn't my final solution as the data still needs to be massaged, but I got what I wanted and it's data I can now work with. Thanks to everyone who posted an answer.

<?php 
header("Content-type: text/html; charset=utf-8");
require_once('includes/connections/know_db.php');
?>

<?php

//First let's get all the years any given project will span...
mysql_select_db($database_know_db, $know_db);
$query_GetYears = sprintf("SELECT DISTINCT(YEAR(target_date)) as project_years FROM project_items WHERE projects_id = 136 AND target_date IS NOT NULL ORDER BY project_years ASC");
$GetYears = mysql_query($query_GetYears, $know_db) or die(mysql_error());

//A function allowing us to extract the week of the year from the next query, and then convert its value into an integer.
function ConvertToWeek($target_date) {
    $week = date('W', strtotime($target_date));
    $week_int = intval($week);
    return $week_int;
}

//Now let's loop through our years, and get project item data (via MySQL) for each...
while ($row_GetYears = mysql_fetch_assoc($GetYears)) {
    echo $row_GetYears['project_years']."<br />";

    mysql_select_db($database_know_db, $know_db);
    $query_GetItems = sprintf("SELECT DISTINCT(target_date) FROM project_items WHERE projects_id = 136 AND target_date IS NOT NULL AND YEAR(target_date) = '".$row_GetYears['project_years']."' ORDER BY target_date ASC");
    $GetItems = mysql_query($query_GetItems, $know_db) or die(mysql_error());

    //Loop through the results of our project items, convert them to week numbers from our function, then toss them into an array.
    while ($row_GetItems = mysql_fetch_assoc($GetItems)) {
        $weeks[] = ConvertToWeek($row_GetItems['target_date']);
        //Array_unique 开发者_JAVA技巧essentially removes duplicate numbers...
        $result = array_unique($weeks); 
    }

    // get the first value in the $weeks array, then to be safe, convert its value to an integer.
    $start_week = array_shift(array_values($weeks));
    $start_week_no = intval($start_week);

    // get the last value in the $weeks array (will use this later to find differences in weeks between overlapping years).
    $end_week = array_pop(array_values($weeks));

        echo 'Start week: '.$start_week_no."<br />";
        echo 'End week: '.$end_week."<br />";

    //Output our weeks for the purposes of display.
    foreach ($result as $week_count) {
        echo ltrim($week_count, "0").'<br />';
    }

    /*Now let's find the weeks in the sequence where weeks are not represented (missing).
    By doing this, we can get a number of for each week we don't have where datasets will be empty.
    */

    // construct a new array:1,2....max(given array).
    $result2 = range($start_week_no,max($result));                                                    

    // use array_diff to get the missing weeks 
    $missing = array_diff($result2,$result);

    //Output our missing weeks for the purposes of display.
    foreach ($missing as $missing_weeks) {
            echo $missing_weeks.' (missing)<br />';
    }

    /*
    Before we close our original while loop--the one that loops through each year, we need to unset our arrays so they are empty upon 
    each new loop.
    */
    unset($weeks);
    unset($result);

//End our original while loop.
}
?>

Take the start date and get a current week number from it (a base if you will). I would probably recommend multiplying it by the year or something so you know when years overlap. Then, take all successors and do the same (week * year) and subtract the base from it.

Since the request involves complicated stuff, so will the answer.

• Sort the dates ASC (obviously)
• Run each through a loop
  • Obtain a variable year for that date: $year = date('Y',strtotime($date);)
  • Obtain a variable week for that date: $week = date('W',strtotime($date);)
  • Create a $yearWeek value using these values: $yearWeek = $year.$week;
  • Push that date into an array array_push($weekArray,$yearWeek,$date);
• Done with the loop, by this point, I suppose...
• Take that array and sort it sort($weekArray);
• Run your results in a new loop, you know, like a foreach
  • For each unique $yearWeek, add to your $weekCounter, like $weekCounter++
  • Echo out your array using the variable you counted in $weekCounter

Don't know if this works, might experiment on it later, but posted this just to give you an idea what you could do. This isn't a complete solution, but it might be worth working from as a starting point.

From your example dates, it looks like you're taking a regular calendar week, so you're not meaning the week to "start" on your first date.

Not a PHP expert but you could do this:

• Calculate the day of the week of the first date (based on whatever your start of week is -- Sunday, Monday, etc.).
• Calculate the difference of all later dates from the first date, in days.
• Add in the day offset of the first date to all of the later dates.
• Divide all of these by 7, discard the remainders, and there's your answer.

This is what I came up with as a function to find the number of weeks that have past between any two dates.

public function weeks_past(){
    $start_date = strtotime('01/07/2012');
    $end_date = strtotime('01/05/2013');

    $start_week = date('W', $start_date);
    $end_week = date('W', $end_date); 

    $start_year = date('Y', $start_date);
    $end_year = date('Y', $end_date);
    $years = $end_year-$start_year;

    if($years == 0){
        $weeks_past = $end_week-$start_week+1;
    }
    if($years == 1){
        $weeks_past = (52-$start_week+1)+$end_week;
    }
    if($years > 1){
        $weeks_past = (52-$start_week+1)+$end_week+($years*52);
    }

    return $weeks_past;
}

This would output 53 weeks have past since 01/07/2012

I don't need the MySQL code--just the date string conversion

date('W', strtotime('12/21/10'))

That will return the week for that date.

You could try something like this:

SELECT (YEAR(date_field) * 100 + WEEK(date_field)) - (SELECT MIN(YEAR(date_field) * 100 + WEEK(date_field)) - 1 FROM table) AS week_number FROM table;

This query creates an integer number from the year and week number. The year is multiplied by 100 to add room for the week number. This way you'll get unique numbers for each week regardless of the year. E.g. the number for today (week 49) would be 201049. The query the subtracts the minimum number from this number to get the week number starting from 0. Note the - 1 at the end of the subquery. It makes the week numbers start from 1.

With this query structure, you don't have to take extra steps to work around projects starting at year's end and ending at the beginning.