This may seem a typical question, but I can’t find an answer to this problem.
I have a booking table:
book_id | arrive_date | depart_date 1 2015-07-20 2015-07-22 2 2015-07-22 2015-07-23 3 2015-07-19 2015-07-20
You will see that I have demonstrated that a depart date can be an arrive date and vice versa
Before a date is added I check that the date range won’t conflict with dates in the table, it is important to allow a depart date to be an arrive date as the person will be leaving, to allow this when I check the dates in the table I ADD a date to the arrive_date and SUBTRACT a day from the depart_date using this SQL:
Select * From booking Where booking.unit_id = 58
And ( DATE_ADD(booking.from_date, INTERVAL 1 DAY)
BETWEEN '2015-07-23' AND '2015-07-24'
OR
DATE_SUB(booking.to_date, INTERVAL 1 DAY)
BETWEEN '2015-07-23' AND '2015-07-24'
OR '2015-07-23'
BETWEEN DATE_ADD(booking.from_date, INTERVAL 1 DAY)
AND DATE_SUB(booking.to_date, INTERVAL 1 DAY)) Limit 1When I added the first date, no problem…same with the second and third, this worked because the first date range I entered a day in between the (20th and 22nd). Then I try and add ‘2015-07-23’ AND ‘2015-07-24’, which is checked using the above SQL, which is obviously returning a result. I need a way that will allow these dates to be accepted as the 23rd is a depart_date.
Any help would be appreciated
I should also mention that I also have a table that holds blocked dates “booking_prev”, therefore a similar query will also be used to check block dates to those in the booking table before they are entered.
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Consider the following…
DROP TABLE IF EXISTS my_table; CREATE TABLE my_table (book_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,arrive_date DATE NOT NULL ,depart_date DATE NOT NULL ); INSERT INTO my_table VALUES (1,'2015-07-20','2015-07-22'), (2,'2015-07-22','2015-07-23'), (3,'2015-07-19','2015-07-20'); SELECT * FROM my_table; +---------+-------------+-------------+ | book_id | arrive_date | depart_date | +---------+-------------+-------------+ | 1 | 2015-07-20 | 2015-07-22 | | 2 | 2015-07-22 | 2015-07-23 | | 3 | 2015-07-19 | 2015-07-20 | +---------+-------------+-------------+
So, the SELECTs might look like this…
SELECT '2015-07-22','2015-07-24'
FROM (SELECT 1) x
LEFT
JOIN my_table y
ON y.arrive_date < '2015-07-24' AND y.depart_date > '2015-07-22'
WHERE y.book_id IS NULL
LIMIT 1;
Empty set (0.00 sec)
SELECT '2015-07-23','2015-07-24'
FROM (SELECT 1) x
LEFT
JOIN my_table y
ON y.arrive_date < '2015-07-24' AND y.depart_date > '2015-07-23'
WHERE y.book_id IS NULL
LIMIT 1;
+------------+------------+
| 2015-07-23 | 2015-07-24 |
+------------+------------+
| 2015-07-23 | 2015-07-24 |
+------------+------------+…but you don’t need to check them first. I’ve deliberately written the SELECTs in such a way that the check can happen as part of the INSERT…
INSERT INTO my_table (arrive_date,depart_date)
SELECT '2015-07-23','2015-07-24'
FROM (SELECT 1) x
LEFT
JOIN my_table y
ON y.arrive_date < '2015-07-24' AND y.depart_date > '2015-07-23'
WHERE y.book_id IS NULL
LIMIT 1;
SELECT * FROM my_table;
+---------+-------------+-------------+
| book_id | arrive_date | depart_date |
+---------+-------------+-------------+
| 1 | 2015-07-20 | 2015-07-22 |
| 2 | 2015-07-22 | 2015-07-23 |
| 3 | 2015-07-19 | 2015-07-20 |
| 4 | 2015-07-23 | 2015-07-24 |
+---------+-------------+-------------+In practice, and depending on the user experience you want to provide, you may want to run the SELECTs first, so that the user can immediately see which dates aren’t available, and then run the INSERTs when it comes to making the booking – to make sure that no one grabbed those dates while the user was mid-way through booking.
Method 2
Ok, well although the accepted answer does the job very well, I still needed to check that a date entry was valid, mainly because I was checking dates in the main booking table before I added the to a table that blocked dates. Anyway, the follow SQL, seems to do all the checks that I need:
Select * From booking Where booking.unit_id = 58 And ((booking.arrive_date < '2015-07-23' And booking.depart_date >= '2015-07-24') OR (booking.arrive_date <= '2015-07-23' And booking.depart_date > '2015-07-24') OR (booking.arrive_date >= '2015-07-23' And booking.depart_date <= '2015-07-24') OR ('2015-07-23' > booking.arrive_date And '2015-07-23' < booking.depart_date ) OR ('2015-07-24' > booking.arrive_date And '2015-07-24' < booking.depart_date )) Limit 1A little disappointed at being marked down for the solution I gave as it does check the values in the table, which was one of my requirements, though it is not all that efficient for the actual insert.
Moving on I have expanded on the accepted answer adding more of the fields I am using based on a table to hold prevented booking dates:
INSERT INTO booking_prevent (unit_id,from_date,to_date,note) SELECT 58, ‘2015-07-28’, ‘2015-07-29’, ‘My Note’ FROM (SELECT 1) x LEFT JOIN booking_prevent y ON y.from_date < ‘2015-07-29’ AND y.to_date > ‘2015-07-28’ And y.unit_id = 58 WHERE y.booking_prevent_id IS NULL LIMIT 1
This works very well, but I am a little stuck how I can do an update on an entry but only make the change…again, if it is valid. This is what I have, which is a stab in the dark:
UPDATE booking_prevent SET (unit_id,from_date,to_date,note) SELECT 58, ‘2015-07-20’, ‘2015-07-26’, ‘Updated Note’ FROM (SELECT 1) x LEFT JOIN booking_prevent y ON y.from_date < ‘2015-07-26’ AND y.to_date > ‘2015-07-20’ And y.unit_id = 58 WHERE y.booking_prevent_id IS NULL WHERE booking_prevent_id = 466 LIMIT 1
Any pointers would be appreciated.
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0