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, Aug 05 '12 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, Aug 05 '12 at 15:41
@Alex Yes I will have the hotelid when querying the table for the current month. — John, Aug 05 '12 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, Aug 05 '12 at 15:48
So now this is comming closer to some useful question. With such a query, why would you do one query per each day you loop through? I ask because you have written that as problem description in your question text, but looking at the query it looks like it works per month and not per day. — hakre, Aug 05 '12 at 16:03
@hakre Perhaps I misunderstood what you asked, I thought you just asked for just a simple query to get all records for a hotel for the current month. Originally I would get the days of the month using date('t') and then loop through each date and query the db with a between query for each date I have in date('t'). So if there were 31 days in the month I would get 2012-01-08 then query the db, then query the next day using 2012-02-08 and the next until I reached 31. — John, Aug 05 '12 at 16:06

score 2 · Accepted Answer · answered Aug 05 '12 at 15:42

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 05 '12 at 15:42

jimhartford

101
2

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 http://www.ajaxavailabilitycalendar.com/ – Alex Aug 05 '12 at 15:44
@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? – John Aug 05 '12 at 15:44
I'm not sure jquery datepicker has that kindof functionality – Alex Aug 05 '12 at 15:45
If you are just trying to block dates that are already booked you could use the beforeShowDay event... http://tokenposts.blogspot.com/2011/05/jquery-datepicker-disable-specific.html – jimhartford Aug 05 '12 at 15:51
@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? – John Aug 05 '12 at 15:55
@SXD Ok looks like I found code to find dates inbetween 2 dates, so I will try that and see if this works. Thanks! – John Aug 05 '12 at 16:01
Yeah, looking at the example you would need some logic to populate your array of dates based on the start and end date, something like this: http://stackoverflow.com/questions/4413590/javascript-get-array-of-dates-between-2-dates – jimhartford Aug 05 '12 at 16:04

score 0 · Answer 2 · answered Aug 05 '12 at 15:56

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.

score 0 · Answer 3 · answered Apr 14 '15 at 15:17

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;

};

Get dates that are booked using php/mysql

3 Answers3