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 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.
}
?>

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.

This is completely the line of thinking I was looking for--makes total sense--thank you! The only part I'm not sure is how would I get the unique $yearWeek in the for loop? Is there a function I can use to select unique values out of a loop? Am I just brainfarting here?
–
Dave AlbertDec 9 '10 at 21:30

I should clarify that by unique, I should really say distinct. So I might have several results starting with: 201039 but I would really only want to reflect one instance of this in the counter.
–
Dave AlbertDec 9 '10 at 21:32

In the loop, perform a check if(in_array($weekArray,$yearWeek)). If not in the array add it, else move onto the next date.
–
BryanDec 9 '10 at 21:47

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.

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.