Get dates that are booked using php/mysql

Question

I have a table in mysql that holds a start and end date. These start and end date are for when a hotel is booked. What I need to do is show a calendar that shows the dates that are booked and the dates that are available for the current month. How would I find out what days are booked for the current month? I could loop through each day of the current month but that would mean 30 or 31 queries, is there a better, more optimized way to find out what days are booked for the month so I can color code the days on a calendar?

The table structure is this:

hotelid int(11)

startdate (date)

enddate (date)

Query Request:

SELECT * FROM hotel_book WHERE hotelid = 1 AND DATE_FORMAT(startdate,'%Y-%m') >= '2012-08' AND DATE_FORMAT(enddate,'%Y-%m') <= '2012-08'

Im sure I could just pass it 2012-08-01 and 2012-08-31 so Im not processing the date format on each check. But just for example purposes.

That depends a bit of how the data is structured in your database. As you have not shown your table, it is hard to say. — hakre
– hakre, Commented Aug 5, 2012 at 15:38
the hotel id refers to different hotels correct? so presumably you would have the user first select the hotel and then all start and end dates with that respective hotel id would be displayed in the calendar? — Alex
– Alex, Commented Aug 5, 2012 at 15:41
@Alex Yes I will have the hotelid when querying the table for the current month. — John
– John, Commented Aug 5, 2012 at 15:42
So first things first. Can you just add a query to the question you would do to get all dates for the hotel ID 1 for month august 2012? — hakre
– hakre, Commented Aug 5, 2012 at 15:48

jimhartford · Accepted Answer · 2012-08-05 15:42:35Z

2

Another approach to this would be to allow your calendar to accept start and end dates and render each day that is booked within the calendar logic. If the calendar uses javascript rather than PHP or MySQL impact on your server(s) would be minimal. Either way you end up looping, it just depends on where you want that to happen.

answered Aug 5, 2012 at 15:42

jimhartford

1012 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Alex Over a year ago

sounds like a good idea then you can output the dates into an array and that would be minimally invasive to the loading time. he could then use something like the ajaxavailabilitycalendar.com

John Over a year ago

@SXD So what if I used a datepicker via jquery? Could I grab all start and end dates via jquery/ajax call and put into perhaps a json array and pass to the datepicker? If so how could I do that?

Alex Over a year ago

I'm not sure jquery datepicker has that kindof functionality

jimhartford Over a year ago

If you are just trying to block dates that are already booked you could use the beforeShowDay event... tokenposts.blogspot.com/2011/05/…

John Over a year ago

@SXD The problem is I only have date ranges, so in order to do that I would have to then process all individual dates between the date ranges. Unless there is an easy way to figure this out code wise?

|

Alex · Accepted Answer · 2012-08-05 15:56:00Z

So first get the hotel id. Then do a query to get all start and end dates respective to that hotel id. I suggest you have a second field where you calculate the difference:

If someone has booked 1st to the 3rd then the 2nd is booked as well, so you would have to make a function that calculates the difference then outputs the 'in between' fields. So it would have to first get the start number then add +1 days every time until the end date. So it would end up like array(1,2,3)

Then you can take this information and put it into a booked array for a jquery or ajax calendar (plenty available online). Presumably once a date is booked it cannot be booked again or else you would have duplicate values of 1,2,3 - so I'm guessing the hotel only has one room avail or something.

Jaqen H'gaar · Accepted Answer · 2015-04-14 15:17:12Z

Here is the code to get the dates you want. You'll be able to show them on your calendar regarding to it's logic. Note that i used custom class for database select.

$id=$_POST['id'];
$days=$db->select("reservations","room_id=".$id);
$listofdays=array();
foreach ($days $day) {
$start=$day['start'];
$end=$day['end'];
$liste=array(); //This array will be filled with dates between start and end
$liste=date_range($start, $end, $step = '+1 day', $output_format = 'm-d-Y'     );
foreach ($liste as $key => $list) {
$listofdays[] = $list;

}
}

//date_range function below:

    function date_range($first, $last, $step = '+1 day', $output_format = 'm/d/Y' ) {

$dates = array();
$current = strtotime($first);
$last = strtotime($last);

while( $current <= $last ) {

    $dates[] = date($output_format, $current);
    $current = strtotime($step, $current);
}

return $dates;

};

Collectives™ on Stack Overflow

Get dates that are booked using php/mysql

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

7 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related