Booking Dates Availability Ranges in PHP/MySQL

0
votes

I have two fields, check_in and nights.

Nights are inclusive of the check_in date. So if a customer books 1 night, he would then check_out the following day and the room would then be available for booking (unless already booked by somebody else).

Here is an example of a couple records within my table,

customer          room            check_in            nights
cus1              test room       2013-01-23          2
cus2              test room       2013-01-25          1
cus3              test room       2013-01-26          4

cus3 has booked a room, they will check into the room on 2013-01-26 and they will stay for 4 nights. this means the room will be unavailable for booking during this time.

So how would I check whether the room was available?

the room should only be available for check_in on 2013-01-30.

All help is appreciated :)

4
phpmysqldatebetween
Do you want to check when the room will next be available (e.g., from the current date)? Or do you want to check whether the room is available for a given date? - Stephen Booher
@StephenBooher I would like to pass a given date and if possible, return true if the room is available and false if the room is not available. - verheesj

4 Answers

0
votes

When you get the data for the customer row, use this to get the check out date:

$check_out = date('Y-m-d', strtotime($row['check_in'].' + '.$row['nights'].' days'));
0
votes

something like this,

SELECT room, 
       CASE WHEN 'dateHere' BETWEEN check_in AND check_out
           THEN 'UNAVAILABLE'
           ELSE 'AVAILABLE'
       END Status
FROM
  (
      SELECT  room, 
              check_in, 
              DATE_ADD(check_in, INTERVAL nights day) check_out
      FROM    booking
  ) s
0
votes

There’s a great site with a list of MySQL statements for common scenarios. Checking whether a room is available within a given period is one of them: http://www.artfulsoftware.com/infotree/queries.php#576

0
votes

When you select your check in date and nights occupied you can do something like this (dummy code):

    $startDate='2013-01-26';//for example
    $to_time=date("Y-m-d", strtotime("$startDate + 4 day"));
    echo $to_time;//prints 2013-01-30