Table in mySQL holds hotel reservations for next few years.
Each row contains id, start_date, end_date and status. the start_date contains a date type which represents the check-in date and the end_date is the checkout date.
I would like to retrieve an array of individual dates so that I can loop through one year calendar and display the dates for next year that are free and which are reserved.
Since the hotel can rent out the room again on the end_date since checkout is early, the end_date itself doesn't need to be included.
example
ID | start_date | end_date | status
1 | 03/15/2014 | 03/18/2014 | Paid
2 | 05/22/2014 | 05/25/2014 | Deposit
3 | 08/12/2014 | 08/13/2014 | Paid
and to array:
$months_arry = array(1 => '03/15/2014', 1 => '03/16/2014', 1 => '03/17/2014',
1 => '05/22/2014', 1 => '05/23/2014', 1 => '05/24/2014', 1 => '08/12/2014');
Since all 12 months of the calendar are displayed vertically on the same page it would be easiest to just check each date against the array to know if it should be green for vacant or red for occupied.
In asp 3.0 on sql server i would have just created a record set rs with "
SELECT start_date, end_date from HotelBookings where start_date >= '2013-12-31'
AND enddate <= '2015-01-01' ORDER BY start_date ASC"
then a .. do until rs.EOF loop .. inside the
loop i would retrieve the start_date and end_date and loop them until the two are equal while adding + 1 to the start_date while building the array of individual dates.
i found this example while looking for answer but it looks ugly and not elegant
<?php
// Make a MySQL Connection
$query = "SELECT * FROM example";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result)){
echo $row['name']. " - ". $row['age'];
echo "<br />";
}
?>