compare two date ranges

[bastienvans]

Hey everyone,

I have been working on this without success.

I am trying to compare a range of dates with another range of dates, and return something if it matches.

In my table, I have 4 columns, "starting", "ending", "season", and "rate".

This part of my code gets every date in between the arrival and departure date from a form.

$start = date('Y-m-d', strtotime($_POST['arrival']));
$end = date('Y-m-d', strtotime($_POST['departure']));
$check = $start;

while($check != $end)
{
echo $check."</br>";
	$check = date ("Y-m-d", strtotime ("+1 day", strtotime($check)));
}

e.g. outputs:

2010-04-06
2010-04-07
2010-04-08
2010-04-09
2010-04-10
2010-04-11

This parts gets the starting date of the season and loops until it reaches the end of season.

$query = "SELECT * FROM `rates` WHERE propertyID='$propertyID' ORDER BY `starting` ASC";
$result = mysql_query($query) or die(mysql_error());


while ($row = mysql_fetch_array($result)) {

$starting = $row['starting'];
$ending = $row['ending'];
$ending = date ("Y-m-d", strtotime ("+1 day", strtotime($ending)));
$checking = $starting;
$rate = $row['rate'];

while($checking != $ending)
{
echo $checking."<br/>";

$checking = date ("Y-m-d", strtotime ("+1 day", strtotime($checking)));
}
}

This outputs:

2010-04-08
2010-04-09
2010-04-10
2010-04-11
2010-04-12
2010-04-13
2010-04-14
2010-04-15

I want to compare the two loops and match every date from the first loop with the 2nd loop.

If they match, i want to output the dates with their corresponding rates.

Any help would greatly be appreciated.

Thanks!

[bradgrafelman]

So you're just wanting all the records in the DB where the two ranges overlap? Sounds like you could do that in SQL:

SELECT col1, col2, etc
FROM rates
WHERE
    (starting <= '$_POST[arrival]' AND ending >= '$_POST[arrival]')
    OR
    (starting >= '$_POST[arrival] AND starting <= '$_POST[departure]')

something like that, anyway?

[bastienvans]

Thanks! that worked like a charm.

another question... what is the easiest way to count each results. I want to know how many results are returned by (starting <= '$POST[arrival]' AND ending >= '$POST[arrival]') and by (starting >= '$POST[arrival] AND starting <= '$POST[departure]')

EDIT:

by using your code, this is the output:

$99/day | June

$69/day | April/May

I need to know how many days will be returned for the season June and how many for the season April/May

[bastienvans]

bump 🙂