title: I have a problem when I try to book a table in my "restaurant" using php and sql
body: <p>I used this query: </p>
  
  <pre><code>SELECT 
    tavolo.ID_Tavolo
  FROM 
    tavolo
    LEFT JOIN prenota 
      ON tavolo.ID_Tavolo = prenota.ID_Tavolo
  WHERE 
    tavolo.ID_Tavolo NOT IN 
      (SELECT 
        Prenota.ID_Tavolo 
       FROM 
        prenota) 
    OR tavolo.ID_Tavolo IN 
      (SELECT 
        prenota.ID_Tavolo 
       FROM 
         prenota 
       WHERE 
         Data_Pren != '2020-06-04' 
         OR Data_Pren = '2020-06-04' 
         AND Ora_Pren NOT BETWEEN '19:00:00' AND '20:30:00' 
         AND ADDTIME(Ora_Pren, "1:30:00.000000") NOT BETWEEN '19:00:00' AND '20:30:00')
  ORDER BY Tavolo.ID_Tavolo
  </code></pre>
  
  <p>It worked when there was no data in the database but, after creating 3 reservations (I have 3 tables), it started using the same ID every time (7) not checking the date.</p>
  
  <p>Theese are my tables:</p>
  
  <p>Prenota table (structure)</p>
  
  <p><a href="https://i.stack.imgur.com/PH2ku.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/PH2ku.png" alt="enter image description here"></a></p>
  
  <p>Prenota table (data)</p>
  
  <p><a href="https://i.stack.imgur.com/cQ6oG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/cQ6oG.png" alt="enter image description here"></a></p>
  
  <p>Tavolo table (structure)</p>
  
  <p><a href="https://i.stack.imgur.com/j6ia2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/j6ia2.png" alt="enter image description here"></a></p>
  
  <p>Tavolo table (data)</p>
  
  <p><a href="https://i.stack.imgur.com/aRRev.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/aRRev.png" alt="enter image description here"></a></p>
  
  <p>(The query works by checking: </p>
  
  <p>1) If the ID doesn't exist in the table "prenota" give me the ID;</p>
  
  <p>2) If the ID already exists and the date is different <strong>OR</strong> the date is the same but the hour <strong>IS NOT BETWEEN</strong> the registration hour and +1h30m <strong>AND</strong> adding 1h30m to check if all the tables are still unavailable during that hour.</p>
  
  <p>Here's a screen of me trying the query:</p>
  
  <p><a href="https://i.stack.imgur.com/yxHjL.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/yxHjL.png" alt="enter image description here"></a></p>
  
  <p>I simply want to know how to make a reservation for a table working by checking:</p>
  
  <p>1) The ID of the table</p>
  
  <p>2) The Date of the already reserved tables</p>
  
  <p>3) The Time of the already booked tables</p>
  
  <p>4) Make a reservation if a "gap" is found checking the time chosen by the user and the time +1h30m (the max time that you can stay at the table)</p>
  
  <p>Here's the link to try it: <a href="https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=87563a5b3f14efee1ffc535e14675f06" rel="nofollow noreferrer">https://dbfiddle.uk/?rdbms=mysql_8.0&amp;fiddle=87563a5b3f14efee1ffc535e14675f06</a></p>