MYSQL / PHP Help - Selecting next available consecutive dates from a table

timer Asked: May 14th, 2013

Question description

Hello there,

I have posted this in various forums but have either had no response or people just not getting what I'm trying to achive.....

Need help! 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... 

$previousDate = mysql_query("SELECT * FROM calendar WHERE autoInc < $requestedDate AND status='available' ORDER BY autoInc DESC"); If you need more please let

The story so far... 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...

$CalendarDate = str_replace("/", "-", "$CalendarDate"); $QueryDate = date("Y-m-d", strtotime($CalendarDate));

Connect to the database...

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

$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.

if ($Handle == 1) { $FirstDateQuery = mysql_query("SELECT * FROM BookingsCalendar WHERE AutoIncNo < $LastAutoIncNo AND Status='available' ORDER BY AutoIncNo DESC LIMIT 1");

while($row = mysql_fetch_array($FirstDateQuery)) {
$AutoIncNo = $row["AutoIncNo"];
$BookingDate = $row["BookingDate"];
$Status = $row["Status"];
} // End - while($row = mysql_fetch_array($SelectDates)) {

$FirstBookedDate = $BookingDate;

echo $FirstBookedDate . ' - ' . $LastBookedDate; } // End - if ($Handle == 1) {

If you have a better more efficient way or can adapt what is already here, that would be grand... My brain hurts lol.

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'.

Any ideas would be very helpful

Tutor Answer

(Top Tutor) Studypool Tutor
School: Boston College
Studypool has helped 1,244,100 students
flag Report DMCA
Similar Questions
Hot Questions
Related Tags

Brown University

1271 Tutors

California Institute of Technology

2131 Tutors

Carnegie Mellon University

982 Tutors

Columbia University

1256 Tutors

Dartmouth University

2113 Tutors

Emory University

2279 Tutors

Harvard University

599 Tutors

Massachusetts Institute of Technology

2319 Tutors

New York University

1645 Tutors

Notre Dam University

1911 Tutors

Oklahoma University

2122 Tutors

Pennsylvania State University

932 Tutors

Princeton University

1211 Tutors

Stanford University

983 Tutors

University of California

1282 Tutors

Oxford University

123 Tutors

Yale University

2325 Tutors