retrieving records form a db one week at a time

[alexhob]

I am trying to return the number of records from a db by weeks. I have the mysql query working fine but i currently have it in a php loop that uses unix timestamps to check a counter time against the current unix timestamp.

However my loop is not incrementing properly and finishes several weeks before it is meant to, ie the number of seconds in the counter variable becomes greater than the current unix timestamp before it should.

Can anyone spot the problem or suggest a different way of achieving my aim?

Thanks.

//used in the PHP loop
$sevenDays = 606024*7;

//used by MySQL
$mysqlStartDate = "2004-04-05";

//this always stays at 7 as it is used as our base count for a week
$mysqlStartInc = 7;

//it is used with $inc and $mysqlStartInc to go from 7 -14 -21- 28 etc
$mysqlDateInc =0;

//used to increment $mysqlDateInc
$inc = 1;

//used to hold value in seconds of start date incremented one week at a time
$counterDate = mktime (0, 0, 0, 04, 05, 2004);

while ($counterDate < time()){

$query = "SELECT COUNT(winID) AS total
		from winners 
		WHERE dateClaimed > DATE_ADD('$mysqlStartDate', INTERVAL $mysqlDateInc DAY)
		AND dateClaimed < DATE_ADD('$mysqlStartDate', INTERVAL $mysqlDateInc+7 DAY)"; 

echo $query;

$row = $db ->dbFetchArray($query);

echo "num = " . $row['total'] . "<br>";

//make this 7 otherwise it will be false as 0 x n will always be 0
if ($mysqlDateInc == 0) $mysqlDateInc = 7;

//alter the date for the query
$mysqlDateInc = $mysqlStartInc * $inc;

//increase our var to hold the number of seconds in $inc weeks	
$dayInc =  $sevenDays * $inc;

//increment our startdate with value for number of seconds in $inc weeks	
$counterDate += $dayInc;

//increment our counter so that the date range changes for the mysql query
$inc++;


echo "day increment is $dayInc<br>";
echo "counter time is $counterDate<br>";

//clear $dayInc
$dayInc = 0;

}