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

SoccerBoss
Category:
Programming
Price: $10 USD

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) Daniel C.
(997)
School: Purdue University
PREMIUM TUTOR
Studypool has helped 1,244,100 students
Ask your homework questions. Receive quality answers!

Type your question here (or upload an image)

1824 tutors are online

Related Programming questions

05/14/2013
05/14/2013
05/13/2013
05/13/2013
05/13/2013

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