MYSQL / PHP Help - Selecting next available dates from a table either side of original booking

Question

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

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 the dates are available is easy enough for me but the difference is this calendar is going to check if there are dates available in the block they require (4 days in a row for example) either side of the dates they originally entered.

Cutting to the chase, I have established what the next available dates are in either direction but what I need is for it to check in blocks, i.e, where the next 4 days in a row are available in either direction on the table. Would some alterations to this code be possible?

$previousDate = mysql_query("SELECT * FROM calendar WHERE autoInc < $requestedDate AND status='available' ORDER BY autoInc DESC");

If you need more please let me know,

OK, The actual story so far...

Actual table

AutoIncNo |    BookingDate    | Status

The calendar is in 2 parts.

Calendar to select a date
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.

Continuation...

I split it down to this one...

SELECT * FROM BookingsCalendar WHERE Status='available' AND AutoIncNo < $LastAutoIncNo ORDER BY AutoIncNo DESC LIMIT 4

It works mate, 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'.

Any ideas?

UPDATE:

The thing is that it will still select the next 4 rows but not together as they appear by date. So, if someone searches for 4 days before or after the date they will get a result but they will be spread out between dates which are booked. When they book it will need it be 1 entire period, see what I mean bud?

Maybe I'm not explaining it very well so sorry about that, but the result must be in a block/together without any 'booked dates in between'.

ENTIRE QUERY:

$FirstDateQuery = mysql_query("SELECT * FROM ((SELECT * FROM BookingsCalendar WHERE Status='available' AND AutoIncNo < $LastAutoIncNo ORDER BY AutoIncNo DESC LIMIT 4)UNION (SELECT * FROM BookingsCalendar WHERE Status='available' AND AutoIncNo > $LastAutoIncNo ORDER BY AutoIncNo ASC LIMIT 4)) as avail_4 ORDER BY 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 . ' ' . '&nbsp;&nbsp;&nbsp;';

        } // End - while($row = mysql_fetch_array($FirstDateQuery)) {

This works, but doesn't quite achieve the goal.

DATA TO PLAY WITH:

These scripts will setup the table the way I have it...

    CREATE TABLE `table_setup` (
    `number` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (`number`)
)
    COLLATE='utf8_general_ci'
    ENGINE=MyISAM;





    INSERT INTO table_setup VALUES(NULL);
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
INSERT INTO table_setup SELECT NULL FROM table_setup;
DELETE FROM table_setup WHERE 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 INTO BookingsCalendar (BookingDate)
SELECT DATE_ADD('2013-05-05', INTERVAL number-1 DAY)
FROM table_setup
WHERE NUMBER < 18264;





DROP TABLE `table_setup`

Harsh Gupta · Answer 1 · 2013-05-14T11:43:25.553

2

Just typing from the top of my head.

SELECT * FROM calendar 
WHERE DATE(date) BETWEEN DATE_SUB($requestedDate, INTERVAL 4 DAY) AND DATE_ADD($requestedDate, INTERVAL 4 DAY) 
AND status='available' 
ORDER BY autoInc DESC

HTH

EDIT: New answer based on revised question

SELECT * FROM (
(SELECT * FROM BookingsCalendar 
WHERE Status='available' AND AutoIncNo < $LastAutoIncNo 
ORDER BY AutoIncNo DESC LIMIT 4)
UNION 
(SELECT * FROM BookingsCalendar 
WHERE Status='available' AND AutoIncNo > $LastAutoIncNo 
ORDER BY AutoIncNo ASC LIMIT 4)
) as avail_4
ORDER BY avail_4.AutoIncNo DESC

This should work. My bad, forgot to add parentheses around inner selects.

edited May 14 '13 at 11:43

answered May 14 '13 at 07:15

Harsh Gupta

4,348
2
25
30

Thanks for your response, I edited the above post to contain more information – PHP Learner May 14 '13 at 08:15
So what I understand is, you are looking for previous 4 available and next 4 available booking details, and not just previous 4 and next 4. – Harsh Gupta May 14 '13 at 08:28
`SELECT * FROM (SELECT * FROM BookingsCalendar WHERE Status='available' AND AutoIncNo < $LastAutoIncNo ORDER BY AutoIncNo DESC LIMIT 4 UNION SELECT * FROM BookingsCalendar WHERE Status='available' AND AutoIncNo > $LastAutoIncNo ORDER BY AutoIncNo ASC LIMIT 4) as avail_4 ORDER BY avail_4.AutoIncNo DESC` – Harsh Gupta May 14 '13 at 08:51
Sorry, didn't quite understood you. Anyways, I am not too good with PHP although I proudly says that I am a Web Dev. So I was only focusing on the SQL part. But by looking at the logic of your flow, I would suggest you retrieve all the necessary rows at the start rather than firing individual queries. That would definitely structure your logic and ease your effort. – Harsh Gupta May 14 '13 at 09:14
The SQL query above in my comment is what you need. It `UNION`s next 4 results as well. Try it and post back. – Harsh Gupta May 14 '13 at 11:10
Can you please post the entire query that you are running? – Harsh Gupta May 14 '13 at 12:03
Ok, I understood. You need to select continuous dates instead of scattered. I am afraid, I might not be able to write an answer for it, but definitely this is not something you could so in a single SQL query. You are looking for consecutive records based on a column, which in your case could be AutoIncNo or BookingDate with condition status as available. You may use similar logic as [presented here](http://www.it-iss.com/mysql/mysql-counting-repetitions-in-a-specific-order/) and wrap everything in a Stored Procedure and use it. I would need some data to play with before I could try it. – Harsh Gupta May 14 '13 at 12:52
I am aware of this issue and frankly, it isn't that hard. You need to buckle up and solve it step by step. I know how to implement these idea to your problem. I could use sample that you posted, but unfortunately, my work system doesn't have MySQL, and SQL Fiddle is running kinda slow here, that's why I couldn't work on it. There might be a way to do it by `JOIN`ing the same table, but I have no way to check it, plus it would be really slow in your case because of humongous number of rows. I will let you know if I could try something. – Harsh Gupta May 14 '13 at 14:17

MYSQL / PHP Help - Selecting next available dates from a table either side of original booking

1 Answers1