output every month, year between 2 dates

martincrumlish

Hi,

I have a page where a user can select a date range to run a report on a DB for. The date is displayed in dropdown boxes with 2 options - year and month. There is one set for the start date and one for the second date.

This data is then passed to my report page which has the mySQL query. The data will be output in a table with figures broken up by month. What I need to do is to iterate through each month and year between the 2 dates passed from the first page.

ie: If a user selects for date 1: 2003-09 and for date 2: 2004-02 I need some code that will loop through each date from 2003-09 to 2004-02 like this:

$date = "2003-09";
.....
$date = "2003-10";
.....
$date = "2003-11";
.....
$date = "2003-12";
.....
$date = "2004-01";
.....
$date = "2004-02";

Any help for this is appreciated.

Thanks,
Martin

Weedpacket

You can find out how many months to list there are by subtracting the ending month from the starting month (adding 12 and subtracting 1 from the year if the result is negative). Then add 12 times the number of years between the end and the beginning. Then add one to that.

Now that you've got that, [man]strtotime[/man]('7 months', $foo) will give you a timestamp for seven months from $foo. To turn the selected date into a timestamp that can be used as the second argument to strtotime, [man]mktime[/man] will do the job.

martincrumlish

any chance you could giv eme some code (or pseudo code) for that? I am not too sure of it.

Here's what I have (but it isn't working properly)

	$d = $d1 - $d2;
	$y = $y2 - $y1;
	if($d < 0){
		$d = $d + 12;
		$y = $y - 1;
	}
	if($y > 0){
		$d = $d * $y +1;
	}
	echo "d1 - ".$d1." d2 - ".$d2."<br>";
	echo "y1 - ".$y1." y2 - ".$y2."<br>";
	echo "y - ".$y." d - ".$d;

$d1 and $d2 are the months, $y1 and $y2 are the years.
thanks

martincrumlish

ok...I made a mess of the code for what you said the first time so I have coded it again and got this:

	$d = $d2 - $d1 + 1;
	$y = $y2 - $y1;

if($d < 0){
	$d = $d + 12;
}

if($y > 0){
	$d = $d + (12 * $y);
}

If i enter date 1 as 2003-01 and date 2 as 2004-12 I get a value of 24, which is correct as there is 24 months in the date range of 2 dates given (inclusive of those dates)

ok....now that I have the number of months, how can I use this to display each month?

What I am trying to do is provide a variable to my query like this:
SELECT * FROM table WHERE thedate = '$date'

and I want to do this query for each date in the date range. The reaosn for this si to output a table with a value for each date of monthly intervals in a table of data.

maybe I am going about this in an inefficient and overly complicated way....any suggestions on how to do this another way would be welcome either. Maybe there is a way i could do it in the query?

Thanks,
Martin

Weedpacket

$year1 and $month1 are the start
$year2 and $month2 are the end
(I presume you've already checked that the end is after the start)

$months = $month2 - $month1;
$years = $year2 - $year1;
if($months<0)
{ $months+=12;
  $years-=1;
}
$months += $years*12;
$months += 1;

That will tell you how many months there are from $year1-$month1 to $year2-$month2 inclusive.

As for the loop; as I suggested, use [man]mktime[/man] to turn the starting year-month pair into a timestamp and, in a loop, [man]strtotime[/man] to add 1, 2, 3, ... months to that. Then you'd use [man]date[/man] to turn the timestamp back into a year-month pair.

xblue

maybe I am going about this in an inefficient and overly complicated way....any suggestions on how to do this another way would be welcome either. Maybe there is a way i could do it in the query?

I think there might be a way if I get you right.

So, if you want to output your data like

12-2003
record from 12-2003
next record from 12-2003

01-2004
record from 01-2004
next record from 01-2004
next record from 01-2004

02-2004
record from 01-2004

you could run separate queries for each month, but I wouldn't recommend it.

instead, consider
- getting all records within the selected period ordered by their timestamps, and selecting month/year (separately or together, doesn't matter much.)
- when looping through them:
- - whenever you stumble across a new month/year, print the new "headline".

hope this makes sense (and I didn't misunderstand you).

martincrumlish

Thanks for the replies weedpacket and xblue.

I was playing around with your way of doing it xblue before but I want to try it with the dates and different queries first.

weedpacket, I now have this code:

$months = $d2 - $d1;
$years = $y2 - $y1;
if($months<0)
{ $months+=12;
  $years-=1;
}
$months += $years*12;
$months += 1;

echo $months."<p>";

echo date("Y-m", mktime(0, 0, 0, $d1, 1, $y1))."<p>";
$adate = strtotime("next ".$months." Months");
echo date("Y-m", $adate);

Your code gets the number of months and then mine turns this into a timestamp and adds the number of months found in $months. It then outputs this date again in readbale format.

The next step is to loop through each of these dates.....i am not sure how to have a condition for this in a while loop. Any help is appreciated again.

Thanks,
Martin