[RESOLVED] Comparing Date Ranges

davidosullivan

Hey there,

hope you can help me,

(btw I do seem to find that everywhere I go people stop helping me after the first post. Am I doing something wrong? I am always very grateful. Is there some forum etiquette I should know about? If so please tell me tell me so I can just do whatever it is I am supposed too, any offence or whatever is sincerely not intended😉

Anyway,

I am trying to make a MySql query that compares one date range to another to see if any dates are the same. I can find info on how to see if one date is in a range but am I right in thinking that doing a Sql query for each date in a range via an individual 'for loop' would be very inefficient?

I have a $datefrom and a $dateto that are entered by the customer for a property ('id') and have a table of bookings where 'id' corresponds to 'property_id' and where rows may or may not have bookings for which ever property. If there are bookings there will be entries called 'booked_from' and 'booked_to' and I need to see if any dates from the customer entered range conflicts with any of these entered bookings.

Please, hope someone can help.

David

adavis

David,

I'm having a little problem being able to see the db table in my mind's eye, but I think what you want to do is a BETWEEN. I am assuming that the user enter date and the db date are in the same format, preferably yyyy-mm-dd.

If you are trying to select a property_id where $datefrom and $dateto are not in the booked range, maybe something like:

SELECT * FROM bookings
WHERE booked_from NOT BETWEEN '$datefrom' AND '$dateto'
AND booked_to NOT BETWEEN '$datefrom' AND '$dateto'

Is that the direction you're trying to go. I just couldn't tell if you were trying to include or exclude properties by the user entered date range.

davidosullivan

Yeah the table is in the right format. It doesn't matter too much I don't think if the query is written as BETWEEN or NOT BETWEEN as I'll b e looking at the result and if any dates are in between then the customer chosen dates will be un available.

Looking at your suggestion I can see that it would make sure that the booked_from and booked_to dates themselves do not occurn between the customer selected dates, The problem I am having is making a query like this make sure that no dates BETWEWEN booked_from and booked_to are in the customer entered range aswell.

Have you any ideas how to do that, is it some kind of multi-dimensional query I need or something?

adavis

I did a little test, creating a table with id, booked_from, and booked_to fields and then ran the query I sent you plugging in several different dates. All that was returned were results where not in the user selected date range:

CREATE TABLE `test` (
`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`booked_from` DATE NOT NULL ,
`booked_to` DATE NOT NULL 
) 

INSERT INTO `test` ( `id` , `booked_from` , `booked_to` ) 
VALUES (NULL , '2006-05-03', '2006-05-07'),
       (NULL , '2006-05-02', '2006-05-08'),
       (NULL , '2006-05-09', '2006-05-11'),
       (NULL , '2006-05-05', '2006-05-15'),
       (NULL , '2006-05-11', '2006-05-22'),
       (NULL , '2006-05-13', '2006-05-21'),
       (NULL , '2006-05-17', '2006-05-24'),
       (NULL , '2006-05-13', '2006-05-20');

Data:
Rows: 8 
id  booked_from  booked_to  

1   2006-05-03   2006-05-07 
2   2006-05-02   2006-05-08 
3   2006-05-09   2006-05-11 
4   2006-05-05   2006-05-15 
5   2006-05-11   2006-05-22 
6   2006-05-13   2006-05-21 
7   2006-05-17   2006-05-24 
8   2006-05-13   2006-05-20 

$datefrom = '2006-05-10';
$dateto   = '2006-05-15';

SELECT *
FROM   test
WHERE  booked_from NOT BETWEEN '$datefrom' AND '$dateto'
AND    booked_to   NOT BETWEEN '$datefrom' AND '$dateto'

Results:

Rows: 3 
id  booked_from  booked_to  

1   2006-05-03   2006-05-07 
2   2006-05-02   2006-05-08 
7   2006-05-17   2006-05-24

As you can see, these are the only dates that are not within the user entered date range.

I'm curious as to how you have your tables set up. It may be that you are not providing yourself with enough information.

Each property has an id number, right? And when a property is booked, it is written to a rental table which contains at least, a unique id, the property id, the booked_from date, and the booked_to date. Am I right so far?

So the user comes to your website and wants to book property A31, for instance. The user selects the property of interest and the dates the family wants to go to the beach or whatever. You want a query to say YES the property is available during those dates, or NO it's already booked by some other lucky family. Am I right so far? If that's all you want to do, then the query above will work, except that you also have to search on the property_id number, so it would be something like:

SELECT *
FROM   test
WHERE property_id = '$propertyID'
AND   booked_from NOT BETWEEN '$datefrom' AND '$dateto'
AND   booked_to   NOT BETWEEN '$datefrom' AND '$dateto'

You have to have a unique record id and a property id in the table. Each property will hopefully be rented by many different familys, so that the property_id would be in the table many times.

Now if you're wanting to throw a calendar up that showes the user the dates that the property's rental availability, say putting an X or something on the booked dates, then that's a bit different.

I just bought a book that might help you figure out how your tables should be. It's "Beginning MySQL Database Design and Optimization, From Novice to Professional" by Jon Stephens and Chad Russel, pub. Apress. It will help you to normalize your tables and not duplicate any information.

I'll be glad to help you more, but first I have to figure out exactly what you're trying to accomplish and what your tables look like. You can PM me if you like. Then add the solution when you get it the way you want it...

Thanks,
Alisa

davidosullivan

Many thanks to Adavis for help and ideas in sorting this.

Eventually sorted it out using a while loop as follows:-

<?php
$datefrom=//entered customer data
$dateto=//entered customer data
$sql = "SELECT pr.price_low, pr.price_lowfrom, pr.price_lowto, pr.price_base, pr.price_high, pr.price_highfrom, pr.price_highto
FROM properties p
LEFT JOIN prices pr
ON p.id=pr.property_id
WHERE p.id=1 ";
$result = mysql_query ($sql) or die (mysql_error());
$row = mysql_fetch_assoc($result);
if ($row =='')
{
echo "there has been an error in calculating the price for this trip. Please call us";
}
else
foreach ($row as $key => $value) {
$$key = $value;
}
$date_value=$datefrom;
$days_low=0;
$days_base=0;
$days_high=0;
$safety_counter=1;
while ($date_value <= $dateto){
if (($date_value >= $price_lowfrom) and ($date_value <= $price_lowto)) {
$days_low++;
}
else if (($date_value >= $price_highfrom) and ($date_value <= $price_highto)) {
$days_high++;
}
else {
$days_base++;
}
$date_value=date("Y-m-d",strtotime("$date_value +1 day"));
}
echo "<br><br><h1>the costs are</h1><br>";
$total_days=($days_low + $days_high + $days_base);
if ($total_days=='0'){
echo "there has been an error in calculating the price for this trip. Please call us";
$price_err="yes";
}else{
if ($days_low != '0') {
$ppnl=$price_low / 7;
$days_lowcost=round (($days_low $ppnl),2);
echo $days_low . " nights at £" . round($ppnl,2) . " per low season night= £" . $days_lowcost . "<BR>";
}
if ($days_high !='0') {
$ppnh=$price_high / 7;
$days_highcost=round(($days_high $ppnh),2);
echo $days_high . " nights at £" . round($ppnh,2) . " per high season night= £" . $days_highcost . "<BR>";
}
if ($days_base !='0') {
$ppnb=$price_base / 7;
$days_basecost=round(($days_base * $ppnb),2);
echo $days_base . " nights at £" . round($ppnb,2) . " basic per night price= £" . $days_basecost . "<BR>";
}
$total_cost=($days_lowcost + $days_highcost + $days_basecost);
echo "The total cost of your " . $total_days . " night trip will be £" . $total_cost;
}
?>

Thanks again to all those who helped me out with that.

All the best

david