match entries within date period

I have a booking system; each booking containing entry [Arrival date: Jan 14, 2011 00:30; Departure date: Jan 16,开发者_StackOverflow 2011 00:30]; I want to display all bookings in the database that appear within the period of this booking.

well say, I have these bookings: [see below] first two are already in the database; now the third is a new entry; both 1st and 2nd are in the time when 3rd booking appears.

a: 2010 01 03
d: 2010 01 10

a: 2009 09 10
d: 2010 02 10

a: 2010 01 02
d: 2010 01 05

how do I query MySQL to get that?

---

Using SQL:

SELECT * FROM bookings WHERE date BETWEEN arrival AND departure

---

SELECT * FROM hp_reservations WHERE ((`arrival_date` BETWEEN i:arrival_date AND i:departure_date) OR (`departure_date` BETWEEN i:arrival_date AND i:departure_date)) AND `id` != i:id;

---

So you want all bookings that are between Booking A and Booking B, right?

Is it safe to assume you already know the two bookings that mark the end points? If so, this is pretty simple (though I had to ask awhile back), but may not be obvious.

Scenario 1 - Known dates:

$booking_a_arr = 2010-01-03;
$booking_a_dep = 2010-01-10;

$booking_b_arr = 2009-09-10;
$booking_b_dep = 2010-02-10;

$query = "SELECT id, arrival, departure FROM BOOKINGS
          WHERE 
               arrival >= '$booking_a_arr' AND 
               departure <= $booking_b_dep";

Scenario 2 - Known IDs:

With only the booking IDs, this requires a subquery for each booking to get the date:

$booking_a_id = "A";
$booking_b_id = "B";

$query = "SELECT id, arrival, departure FROM BOOKINGS
          WHERE 
          arrival >= (SELECT arrival FROM BOOKINGS WHERE id = '$booking_a_id')
          AND departure <= (SELECT departure FROM BOOKINGS WHERE id = '$booking_b_id')";

By setting the arrival to be greater than the earlier booking date and the departure to be less than the later booking date, you basically are bounding the results to those that fall in between but still contain the outer bookings. If you wanted a list of all bookings that do not overlap but are between those two events, you would set the boundaries to the inner edges of the two events instead of their outer edges, like so:

$booking_a_id = "A";
$booking_b_id = "B";

$query = "SELECT id, arrival, departure FROM BOOKINGS
          WHERE 
          arrival >= (SELECT departure FROM BOOKINGS WHERE id = '$booking_a_id')
          AND departure <= (SELECT arrival FROM BOOKINGS WHERE id = '$booking_b_id')";

In that last case, if the two original bookings are themselves overlapping, this would return all events that were scheduled inside of their overlap. But if this is the case, and you want fall in that overlap, not just start and end inside that overlap, you would use:

$query = "SELECT id, arrival, departure FROM BOOKINGS
          WHERE 
          arrival >= (SELECT departure FROM BOOKINGS WHERE id = '$booking_a_id')
          OR departure <= (SELECT arrival FROM BOOKINGS WHERE id = '$booking_b_id')";

In all of the above cases, switching the query from AND to OR will give you events that fall inside the two events but are not bound by both events, just one or the other.