CODEWITHSUNDEEP
phpsql
Gelxxxx
Gelxxxx

Reputation: 161

Check if time already occupied using sql query

I'm currently developing s reservation system for meeting rooms, I'm having a trouble checking the time range if it is occupied. Example 9:00 - 11:00 is already occupied, if the other user will set or input 9:30 - 10:30 alert must show that time is already occupied and the reservation will not save to database.

this query is what I've tried, but it only works when the inputted time-in and out has same value..

SELECT * FROM `reservation`
    WHERE time_in = '$time_in' AND time_out = '$time_out' AND day = '$Fday' AND meeting_table = '$selected_radio'

$time_in,$time_out,$Fday is the value in selection box that the user set their time. And the $selected_radio, is the value of selected radio for their desired area to reserve.

my table 'reservation' looks like this :

id | emp_id | emp_name | month | day | year | meeting_table | time_in | time_out 
1  | 23345  | name     | 09    | 03  | 2019 | area 1        | 7:00    | 9:00

Upvotes: 0

Views: 971

Answers (2)

Caconde
Caconde

Reputation: 4483

You can use BETWEEN:

SELECT
    *
FROM
    reservation
WHERE
    (
        time_in BETWEEN ('$time_in' AND '$time_out') OR
        time_out BETWEEN ('$time_in' AND '$time_out') OR
        '$time_in' BETWEEN (time_in AND time_out) OR
        '$time_out' BETWEEN (time_in AND time_out)
    ) AND 
    day = '$Fday' AND 
    meeting_table = '$selected_radio'

This query will meet situations like:
9:00 - 11:00 - existing reservation

08:00 - 09:30 - ocuppied
10:30 - 12:00 - ocuppied
10:30 - 12:00 - ocuppied
07:30 - 08:30 - free
11:30 - 12:30 - free
07:00 - 12:00 - occupied

I believe you should keep your time slots like 8:00 - 8:59. Making it 8:00 - 9:00 will give you a false 'occupied' if somebody tries to schedule the slot 9:00 - 10:00, as the time_in will be at same time as the time_out of the previous reservation.

Important!!! The way it is, your code is open to SQL Injection attacks! I recommend using PDO to fix it. PHP PDO Prepared Statements Tutorial to Prevent SQL Injection is a good tutorial on how to use it.

EDIT: Added two more conditions to WHERE clause, to cover the situations when somebody tries to schedule a time slot with time_in and time_out between the start and end times of an existing reservation.

E.g.:
09:00 - 11:00 - existing reservation

10:00 - 10:30 - occupied

Upvotes: 5

Ed Bangga
Ed Bangga

Reputation: 13006

Here's your query. CAST to datetime first before working with time.

    SELECT * FROM `reservation`       
    WHERE 
     time_in >= cast('$time_in' as datetime) and time_out <= cast('$time_out' as datetime) 
       AND day = '$Fday' AND meeting_table = '$selected_radio'

Upvotes: 0

Related Questions