CODEWITHSUNDEEP
mysqlsql
Kevin Karl Leaño
Kevin Karl Leaño

Reputation: 1800

How can I check if time period is already exist or between a specific period in database?

I have this query in my php. What i need to do is. i have example.

8:30 am to 10:00 am. so i want this time start and time end to be checked if it already exist in the database or it is between a time period.

input: 8:30 am to 10:00 am == database: 8:30 am to 10:am. -> already exist

input: 8:30 am to 11:30 am == database: 8:30 am to 10:am. -> already exist between

input: 9:30 am to 11:30 am == database: 8:30 am to 10:am. -> already exist between

input: 7:00 am to 11:30 am == database: 8:30 am to 10:am. -> already exist between

input: 7:00 am to 10:00 am == database: 8:30 am to 10:am. -> already exist between

input: 7:00 am to 9:30 am == database: 8:30 am to 10:am. -> already exist between

the code below works except that if the time start or time end is between the existing periods.. it still continues and doesn't tell me that it is already existing in between a period. i don't know what im lacking. thanks!

SELECT  * 
FROM room_schedules 
INNER JOIN rooms
ON room_schedules.Room_ID = rooms.Room_ID
WHERE
(
    (
        '[Start]' BETWEEN room_schedules.Starts AND room_schedules.Ends
    ) AND (
        '[End]' BETWEEN room_schedules.Starts AND room_schedules.Ends
    )
) OR (
    (
        room_schedules.Starts BETWEEN '[Start]' AND '[End]'
    ) AND (
        room_schedules.Ends BETWEEN '[Start]' AND '[End]'
    )
)

Upvotes: 0

Views: 1721

Answers (1)

Jun Rikson
Jun Rikson

Reputation: 1884

Just use COUNT(*). It will give you total rows of your result. Like this :

SELECT  COUNT(*) 
FROM room_schedules 
INNER JOIN rooms
ON room_schedules.Room_ID = rooms.Room_ID
WHERE
room_schedules.Starts BETWEEN '07:00:00' AND '10:00:00'
 AND 
room_schedules.Ends BETWEEN '10:00:00' AND '12:00:00'

Then in your php, you just need to check the result, for example :

$total = /* get from query */
if($total > 0)
    echo $total.' results';
else
    echo 'Data Not Exist';

Upvotes: 1

Related Questions