CODEWITHSUNDEEP
mysqlsqldatetime
user3218484
user3218484

Reputation: 15

SQL command between two datetimes for table reservation

I am developing at the moment the reservation system for a restaurant that is always full and if anyone wants to come must make your reservation.

This means that the host enters arrival and departure, so the exact interval in which the host wants to be in the restaurant.

So if that customer A has a reservation 8:00 p.m. to 10:00 p.m. and customer arrives B, who wants to come 9:00 p.m. to 11:00 p.m. and wants to book the same table as the customer A system will not let go, because the times overlap . 

$GuetComming = '2014-01-21 12:30:00'
$GuestDepature = '2014-01-21 16:30:00'
$i= '10'

SQL query:

SELECT * FROM `booking`
    where (Table='$i' AND Comming BETWEEN '$GuestComming' AND '$GuestDepature')
    or (Table='$i' AND Depature BETWEEN '$GuestComming' AND '$GuestDepature')
    or (Table='$i' AND Comming <= '$GuestComming' AND Depature >='$GuestDepature')
    or (Table='$i' AND Comming BETWEEN '$GuestComming' AND '$GuestDepature')

I am currently command that works, but I believe it would be much easier.

This is how it works, but unfortunately it happens sometimes that a table is available when it is reserved.

Therefore, seeking a better solution, because the restaurant is approximately 100 tables and I believe that sometimes it can happen that the server does not manage to find out whether a table is reserved.

Upvotes: 1

Views: 517

Answers (1)

athabaska
athabaska

Reputation: 455

I would reduce it to following:

SELECT * FROM `booking`
WHERE Table='$i' AND NOT(Comming >= '$GuestDepature' OR Depature <= '$GuestComming')

Query result should be empty if table is reserded. Not sure how 'odchodHosta' field works.

//Update SQL- Added NOT

Upvotes: 2

Related Questions