Search the Community

Hello coder buddies, I have made this table with all the dates of the years going upto 2063, to keep it simple, it contains 3 columns which have been pre-popluated, example as follows... Actual table AutoIncNo | BookingDate | Status The calendar is in 2 parts. 1) Calendar to select a date 2) A list menu to select the amount of nights they wish to stay. So I take the original date (the one they select) and rearrange the format to suit the table... PHP Code: $CalendarDate = str_replace("/", "-", "$CalendarDate"); $QueryDate = date("Y-m-d", strtotime($CalendarDate)); Connect to the database... PHP Code: include_once('../connect/connectdatabase.php'); Run the first query to check if the dates they require are available. $QueryDate is the date they select $NightsForQuery is the amount of nights they want to stay PHP Code: $CalendarQuery = mysql_query("SELECT * FROM BookingsCalendar WHERE BookingDate='$QueryDate' LIMIT 1"); while($row = mysql_fetch_array($CalendarQuery)) {$AutoInc = $row["AutoIncNo"];} $AutoInc2 = $AutoInc + $NightsForQuery - 2; $SelectDates = mysql_query("SELECT * FROM BookingsCalendar WHERE AutoIncNo BETWEEN $AutoInc AND $AutoInc2"); while($row = mysql_fetch_array($SelectDates)) { $AutoIncNo = $row["AutoIncNo"]; $BookingDate = $row["BookingDate"]; $Status = $row["Status"]; if ($Status == 'booked') { $LastBookedDate = $BookingDate; $LastAutoIncNo = $AutoIncNo; $Handle = 1; } } // End - while($row = mysql_fetch_array($SelectDates)) { if ($Handle !== 1) {echo 'DATES AVAILABLE >> WRITE BOOKING CODE';} So if the handle is not equal to 1 its fine and they can book, but, if the dates arn't available (i.e, $Handle == 1) I need to check the closest available dates either side (before and after) the date they wanted where the Status is 'available' for the amount of nights they wish to stay... So I set out to establish the first available date in either direction and thts where I got stuck. Looking at it I'm sure you could run a while loop to find the next available block inside the code above, but not sure how. PHP Code: $FirstDateQuery = mysql_query("SELECT * FROM BookingsCalendar WHERE Status='available' AND AutoIncNo < $LastAutoIncNo ORDER BY AutoIncNo DESC LIMIT $NightsForQuery"); while($row = mysql_fetch_array($FirstDateQuery)) { $AutoIncNo = $row["AutoIncNo"]; $BookingDate = $row["BookingDate"]; $Status = $row["Status"]; echo $BookingDate . ' '; } Which works, but, it selects the previous 4 rows individually. So for example, if someone tries to book from 2013.06.01 but cant because its 'booked' for the next 4 days, the above script runs and brings up 2013.05.31 - 2013.05.30 - 2013.05.29 - 2013.05.28 as a result. But if one of those dates are booked it will skip it and give me the next one (selecting as it is the next 4 that meet the condition 'available') So if say 2013.05.29 was booked it would show 2013.05.31 - 2013.05.30 - 2013.05.28 - 2013.05.27 missing out the day which is booked. Now the thing is that we need the next 4 rows together (undivided/continuous/without breaks in the dates) which are 'available'. If you have a better more efficient way or can adapt what is already here, that would be grand... My brain hurts lol. Thank you, L-Plate