title: Room Availability check SQLite Query
body: <p>I am a newbie but I am trying to master the art of Web Development and failing miserabely in my learning Process because of an SQLite Query.</p>
  
  <p>This is my DB Design:</p>
  
  <p><a href="https://i.stack.imgur.com/wlany.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/wlany.png" alt="enter image description here"></a></p>
  
  <p>I want to look for available rooms but my queries just don't give me the correct results!!!</p>
  
  <p>I populated the SQLite DB manually with fake bookings and then ran various Queries for the exact same dates already booked but the booked room keeps showing up as available because it IS indeed available before and after the query date! But the Query Dates are the important dates! That is why the booked room should not show up at all!! But i was unable to come up with the correct query!</p>
  
  <p>My Fake Booking:</p>
  
  <p>A Single Room with the Room Number 101 booked from 2016-05-10 to 2016-05-13</p>
  
  <p>This is my "best" Query so far:</p>
  
  <pre><code>SELECT * FROM Rooms NATURAL LEFT JOIN Booking WHERE RoomType='SR' AND
  Checkout IS NULL OR Arrival &lt;= '2016-05-10' AND Checkout &lt;= '2016-05-10'
  AND RoomType='SR' OR Arrival &gt;= '2016-05-13' AND Checkout &gt;= '2016-05-13'
  AND RoomType='SR' OR Arrival &lt;&gt; '2016-05-10' AND Checkout &lt;&gt; '2016-05-13'
  AND RoomType='SR';
  </code></pre>
  
  <p><code>SR</code> is the RoomType (Single Room)</p>
  
  <p>Room Number 101 needs to vanish entirely from the Resultset when i run the Query! But 101 keeps showing up!</p>
  
  <p>The <code>Arrival</code> and <code>Checkout</code> Fields are of type <code>DATE</code> in the SQLite DB.</p>
  
  <p>Can You guys please help me??</p>
  
  <p>And also, am i doing the PRAGMA Foreign Keys thing correctly in PHP? CustomerID and RoomNumber in the Booking table are Foreign Keys.</p>
  
  <pre><code>&lt;?php
  $db = new PDO('sqlite:gshotel.db');
  $db-&gt;exec('PRAGMA foreign_keys = ON;');
  $db-&gt;setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  prepare...
  execute...
  ?&gt;
  </code></pre>
  
  <p>Thank You very much</p>