StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>The problem you're having is that your query is not sufficiently robust. When you break down the problem, what you have is this:</p> <p>If the range defined by <code>$check_in</code> and <code>$check_out</code> overlaps the range defined by <code>checkin</code> and <code>checkout</code> in <em>any</em> way, then the room is booked. Otherwise, it is free.</p> <p>This means that:</p> <ul> <li>If <code>$check_in</code> >= <code>checkin</code> and <code>$check_in</code> <= <code>checkout</code>, the room is <strong>BOOKED</strong></li> <li><strong>OR</strong> If <code>$check_out</code> >= <code>checkin</code> and <code>$check_out</code> <= <code>checkout</code>, the room is <strong>BOOKED</strong></li> <li><strong>OR</strong> If <code>$check_in</code> <= <code>checkin</code> and <code>$check_out</code> >= <code>checkout</code>, the room is <strong>BOOKED</strong></li> </ul> <p>So, you need to represent both of these scenarios in your subquery in order to get the information you're looking for.</p> <p>Also, you will hopefully be using <code>datetime</code> for your comparisons and not just <code>time</code>, otherwise you will have side effects.</p> <p><strong>EDIT: SQL Query</strong> </p> <p>(Keep in mind that there is more than one way to skin a cat, so to speak. I'm just providing an example that keeps with what you already have as much as possible. Once again, I'm also assuming that <code>checkin</code>, <code>checkout</code>, <code>$check_in</code>, and <code>$check_out</code> will all resolve to <code>datetime</code> types)</p> <pre><code>SELECT * FROM room WHERE room_id NOT IN (SELECT room_id FROM bookings WHERE (checkin <= '$check_in' AND checkout >= '$check_in') OR (checkin <= '$check_out' AND checkout >= '$check_out') OR (checkin >= '$check_in' AND checkout <= '$check_out')) </code></pre>

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