2 Column Mysql Date Range Search in PHP

[webdesco]

hello All,

i have a mysql table with the following columns

mysql> SELECT * FROM chalet_availability c;

+----+---------------------+------------+---------------------+---------------------+--------+------+------------------+

| id | created | propertyId | booked_from | booked_to | status | rate | comments |

+----+---------------------+------------+---------------------+---------------------+--------+------+------------------+

| 1 | 2009-09-01 12:40:48 | 1 | 2009-09-20 12:41:06 | 2009-09-26 12:41:10 | 1 | 200 | booking confimed |

| 2 | 2009-09-02 12:41:39 | 2 | 2009-09-18 12:41:59 | 2009-09-25 12:42:06 | 1 | 200 | booking confimed |

+----+---------------------+------------+---------------------+---------------------+--------+------+------------------+

 

i now have a new booking, and I need to query the database to ensure that the requested property is not already booked for the requested dates, but I can not figure out a solution that feels right.

so lets say my request is for property 1 from 2009-09-22 to 2009-09-28

I've got half way, i.e testing to see if there is a record in the database that matches the start date

SELECT * FROM chalet_availability WHERE propertyId = '1' AND '2009-09-22' >= booked_from AND '2009-09-22' <= booked_to

 

If the query returns 0 rows then the start date is available.

but how do i do the end date, can it be done in the same query, or should I do another query on the end date, so if both queries return 0 rows then I know the booking can go ahead. However none of the above covers me for the eventuality that there may be an existing booking that has a start date and end date that fall in between my query dates i.e a weekend booking. Does this mean that I have to query each date of the requested booking i.e 7 night = 7 queries, 14 nights = 14 queries etc.

 

As you can tell I am a newbie, so your advice and guidance on the best way to do this is much appreciated.

 

i'm using

php 5.28

MySQL 5.1.31-community

 

[PFMaBiSmAd]

After you have verified that the requested start/end dates are valid (the requested start date is < the requested end date), all you really need to do is make sure that requested range is outside of any existing booking range. The requested end date should be less than or equal to the existing booked_from (start) date OR the requested start date should be greater than or equal to the existing booked_to (end) date.

 

$req_start = '2009-09-22';
$req_end = '2009-09-28';

SELECT * FROM chalet_availability WHERE propertyId = '1' AND ('$req_end' <= booked_from OR '$req_start' >= booked_to)

[webdesco]

Thanks for your responce PFMaBiSmAd, sorry it's taken so long to reply, I came down with a bad case of tonsillitis the day after I posted the original topic.

 

I tried your query, but It does not retrieve the results I expected.  Just to confuse things I've changed the data

 

mysql> SELECT * FROM chalet_availability c;
+----+---------------------+-----------------+---------------------+---------------------+--------+------+----------+
| id | created             | room_propertyId | booked_from         | booked_to           | status | rate | comments |
+----+---------------------+-----------------+---------------------+---------------------+--------+------+----------+
|  1 | 2009-09-01 00:00:00 |               1 | 2009-10-03 00:00:00 | 2009-10-17 00:00:00 |      2 |  200 | booked   |
|  2 | 2009-09-02 12:41:39 |               1 | 2009-10-24 00:00:00 | 2009-11-07 00:00:00 |      2 |  200 | booked   |
|  3 | 2009-09-03 00:00:00 |               1 | 2009-11-21 00:00:00 | 2009-11-28 00:00:00 |      2 |    2 | booked   |
|  4 | 2009-09-13 00:00:00 |               4 | 2009-10-01 00:00:00 | 2009-11-01 00:00:00 |      4 |  400 | NULL     |
|  8 | 2009-06-09 00:00:00 |               1 | 2009-12-19 00:00:00 | 2010-01-02 00:00:00 |      2 |  200 | booked   |
+----+---------------------+-----------------+---------------------+---------------------+--------+------+----------+
5 rows in set (0.00 sec)

 

Using the above data if I was to check if there is an existing booking from 2009-11-14 to 2009-11-20 I should retrieve 0 rows as there are no existing booking, however using your query below I retrieve 5 rows!

 

mysql> SELECT * FROM chalet_availability WHERE '2009-11-20' <= booked_from OR '2009-11-14' >= booked_to;
+----+---------------------+-----------------+---------------------+---------------------+--------+------+----------+
| id | created             | room_propertyId | booked_from         | booked_to           | status | rate | comments |
+----+---------------------+-----------------+---------------------+---------------------+--------+------+----------+
|  1 | 2009-09-01 00:00:00 |               1 | 2009-10-03 00:00:00 | 2009-10-17 00:00:00 |      2 |  200 | booked   |
|  2 | 2009-09-02 12:41:39 |               1 | 2009-10-24 00:00:00 | 2009-11-07 00:00:00 |      2 |  200 | booked   |
|  3 | 2009-09-03 00:00:00 |               1 | 2009-11-21 00:00:00 | 2009-11-28 00:00:00 |      2 |    2 | booked   |
|  4 | 2009-09-13 00:00:00 |               4 | 2009-10-01 00:00:00 | 2009-11-01 00:00:00 |      4 |  400 | NULL     |
|  8 | 2009-06-09 00:00:00 |               1 | 2009-12-19 00:00:00 | 2010-01-02 00:00:00 |      2 |  200 | booked   |
+----+---------------------+-----------------+---------------------+---------------------+--------+------+----------+
5 rows in set (0.00 sec)

any ideas on why this is so?

[PFMaBiSmAd]

The query checks if the requested range is available (open/free) for any property, not if the requested range is booked.

[webdesco]

I'm trying to figure out how this can be implimented, and the only way that I can think of is to firs count the total number of rows in the chalet_availability table then do your query; if the number of rows from your query equals the total number of rows in the table then the date is available, otherwise it is not.  Is this how you would do it?

 

I've been trying to come up with a query that gives me a list of properties that are not available for the period, then i could use the resulting array to unset another array that contains all the properties.  The end result being an array of properties that are available.

I'm close with the following query that detects if the start or end dates overlap an existing booking, however it fails to catch a booking that is between the dates.

SELECT DISTINCT room_propertyId FROM chalet_availability where (status != 1) AND (('2009-11-07' > booked_from AND '2009-11-07' < booked_to) OR ('2009-11-21' > booked_from AND '2009-11-21' < booked_to) )

[webdesco]

Okay, the following query does it all, but I can't help thinking that there is an easier way of doing it!

 

SELECT DISTINCT room_propertyId FROM chalet_availability where (status != 1) AND ((('2009-11-7' > booked_from AND '2009-11-7' < booked_to) OR ('2009-11-21' > booked_from AND '2009-11-21' < booked_to)) OR ((booked_from > '2009-11-7' AND booked_from < '2009-11-21') OR (booked_to > '2009-11-7' AND booked_to < '2009-11-21')))

 

Results:  if there is no conflict (i.e the dates are available) then zero rows are returned, if there are existing bookings, then the id of the property that is booked will be returned.

comments would be appreciated from the pro's

[webdesco]

The query checks if the requested range is available (open/free) for any property, not if the requested range is booked.

I'm not sure whether I made my objective clear, I'm looking for a way to generate a list of properties that are available for a certain date period, I don't think your query can achieve this.

[fenway]

Well, if you have the date range, you simply need to check in any db record overlaps.

[webdesco]

Well, if you have the date range, you simply need to check in any db record overlaps.

I'm not sure what you mean

[fenway]

Well, there are 4 options:

 

1) both start/end are are before your date range

2) both start/end are are after your date range

3) start is before, end is during/after

4) start is during, end is during/after

 

1 & 2 are good... 3 & 4 are overlaps.

[crirus]

Hello

 

I am trying to do something similar, but I want to be able to select even partial booked, form your query above if a day or more within your search interval is booked you receive property as booked. Any clue how to make it available also when only partial booked?

 

thanks

Cristian

[fenway]

Wow, you resurrected a really old thread...

[crirus]

Had a reason

 

I also figured out how. I have properties rented

First I rule out properties that will never match a search based on all other search rules.

 

On remaining ones I select all the bookings that intersect my search days.

In PHP I build an array with all days booked

$booked[]='yyyy-mm-dd';

 

I build another array with search interval

 

Finally I check if any day in the search interval is not in the $booked array. If one is not found I break the loop and go to next property.

 

Will see how this behave on really large database and booking table.