StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSearching for availability with MySQL (and PHP)?
text
Body
copied!<p>I have two MySQL (MyIsAm) tables that represent letting units and bookings:</p> <ul> <li>LettingUnits (<code>ID</code>, <code>Name</code>, etc...)</li> <li>LettingUnitBookings (<code>ID</code>, <code>F_LU_ID</code>, <code>Start</code>, <code>End</code>)</li> </ul> <p>Where <code>F_LU_ID</code> is a foreign key to the unit.</p> <p>What is the best way to search for units that are available during a certain time frame? The search is passed a Start, End and Duration.</p> <ul> <li>Start = Earliest start of the booking</li> <li>End = Latest end of the booking</li> <li>Duration = Duration of the booking</li> </ul> <p>I'd be interested to know if it's even possible to do this in MySQL, however if not then the best way to do it in PHP.</p> <p><strong>Example</strong></p> <p>In answer to the answers below I feel an example will help explain the problem.</p> <p>A LettingUnit:</p> <ul> <li>(123, "Foo Cottage")</li> </ul> <p>Some LettingUnitBookings:</p> <ul> <li>(400, 123, 01/01/09, 05/01/09) - a 5 day booking</li> <li>(401, 123, 10/01/09, 20/01/09) - a 10 day booking</li> <li>(402, 123, 25/01/09, 30/01/09) - a 5 day booking</li> </ul> <p>If we search for:</p> <ul> <li>Start = 01/01/09</li> <li>End = 01/02/09</li> <li>Duration = 5 (days)</li> </ul> <p>Then we want the unit to show up. Because there is availability for a 5 day booking within the search range.</p> <p>If the duration is 10 then the unit won't show up as there are no 10 consecutive unbooked days within the search range.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload