MYSQL / PHP Help - Selecting next available dates from a table either side of original booking -
i have made table dates of years going upto 2063, keep simple, contains 3 columns have been pre-popluated, example follows...
autoinc | date | status 1 | 2013-05-05 | available 2 | 2013-05-06 | available 3 | 2013-05-07 | booked 4 | 2013-05-08 | booked 5 | 2013-05-09 | booked 6 | 2013-05-10 | available 7 | 2013-05-11 | booked 8 | 2013-05-12 | available now, checking if dates available easy enough me difference calendar going check if there dates available in block require (4 days in row example) either side of dates entered.
cutting chase, have established next available dates in either direction need check in blocks, i.e, next 4 days in row available in either direction on table. alterations code possible?
$previousdate = mysql_query("select * calendar autoinc < $requesteddate , status='available' order autoinc desc"); if need more please let me know,
ok, actual story far...
actual table
autoincno | bookingdate | status the calendar in 2 parts.
calendar select date
a list menu select amount of nights wish stay.
so take original date (the 1 select) , rearrange format suit table...
$calendardate = str_replace("/", "-", "$calendardate"); $querydate = date("y-m-d", strtotime($calendardate)); connect database...
include_once('../connect/connectdatabase.php'); run first query check if dates require available.
$querydate date select
$nightsforquery amount of nights want stay
$calendarquery = mysql_query("select * bookingscalendar bookingdate='$querydate' limit 1"); while($row = mysql_fetch_array($calendarquery)) {$autoinc = $row["autoincno"];} $autoinc2 = $autoinc + $nightsforquery - 2; $selectdates = mysql_query("select * bookingscalendar autoincno between $autoinc , $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 handle not equal 1 fine , can book, but, if dates arn't available (i.e, $handle == 1) need check closest available dates either side (before , after) date wanted status 'available' amount of nights wish stay...
so set out establish first available date in either direction , thts got stuck. looking @ i'm sure run while loop find next available block inside code above, not sure how.
if ($handle == 1) { $firstdatequery = mysql_query("select * bookingscalendar autoincno < $lastautoincno , status='available' order 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 have better more efficient way or can adapt here, grand... brain hurts lol.
continuation...
i split down one...
select * bookingscalendar status='available' , autoincno < $lastautoincno order autoincno desc limit 4 it works mate, but, selects previous 4 rows individually.
so example, if tries book 2013.06.01 cant because 'booked' next 4 days, above script runs , brings 2013.05.31 - 2013.05.30 - 2013.05.29 - 2013.05.28 result.
but if 1 of dates booked skip , give me next 1 (selecting next 4 meet condition 'available')
so if 2013.05.29 booked show 2013.05.31 - 2013.05.30 - 2013.05.28 - 2013.05.27 missing out day booked.
now thing need next 4 rows (undivided/continuous/without breaks in dates) 'available'.
any ideas?
update:
the thing still select next 4 rows not appear date. so, if searches 4 days before or after date result spread out between dates booked. when book need 1 entire period, see mean bud?
maybe i'm not explaining sorry that, result must in block/together without 'booked dates in between'.
entire query:
$firstdatequery = mysql_query("select * ((select * bookingscalendar status='available' , autoincno < $lastautoincno order autoincno desc limit 4)union (select * bookingscalendar status='available' , autoincno > $lastautoincno order autoincno asc limit 4)) avail_4 order avail_4.autoincno desc")or die(mysql_error()); while($row = mysql_fetch_array($firstdatequery)) { $autoincno = $row["autoincno"]; $bookingdate = $row["bookingdate"]; $status = $row["status"]; $avail_4 = $row["avail_4"]; echo $bookingdate . ' ' . $avail_4 . ' ' . ' '; } // end - while($row = mysql_fetch_array($firstdatequery)) { this works, doesn't quite achieve goal.
data play with:
these scripts setup table way have it...
create table `table_setup` ( `number` int(10) unsigned not null auto_increment, primary key (`number`) ) collate='utf8_general_ci' engine=myisam; insert table_setup values(null); insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; insert table_setup select null table_setup; delete table_setup number > 18264; create table `bookingscalendar` ( `autoincno` smallint(2) not null auto_increment, `bookingdate` date not null, `status` varchar(10) not null default 'available' `invoicerefference` varchar(40) not null, `customername` varchar(40) not null, `customeremail` varchar(40) not null, `customerphone` varchar(30) not null, `address1` varchar(100) not null, `county` varchar(40) not null, `country` varchar(30) not null, `postcode` varchar(10) not null, primary key (`autoincno`) ) collate='utf8_general_ci' engine=myisam; insert bookingscalendar (bookingdate) select date_add('2013-05-05', interval number-1 day) table_setup number < 18264; drop table `table_setup`
just typing top of head.
select * calendar date(date) between date_sub($requesteddate, interval 4 day) , date_add($requesteddate, interval 4 day) , status='available' order autoinc desc hth
edit: new answer based on revised question
select * ( (select * bookingscalendar status='available' , autoincno < $lastautoincno order autoincno desc limit 4) union (select * bookingscalendar status='available' , autoincno > $lastautoincno order autoincno asc limit 4) ) avail_4 order avail_4.autoincno desc this should work. bad, forgot add parentheses around inner selects.
Comments
Post a Comment