CODEWITHSUNDEEP
mysql
JAMES RYAN
JAMES RYAN

Reputation: 97

Verifying availability in a scheduling table MySql PHP

I currently have a table that holds events with the following columns : 

ID  | Date_From |   Date_To |   Time_From  |    Time_To

When a user wants to create a new event through a form, I am trying to write the query that will verify if an event is already existent during the requested time-slot and return a number of rows for further processing.

I'm not certain about how to proceed for the query to verify the existence of an event during that time frame.

The end user is providing the same 4 fields as above.

Both date fields in the yyyy-mm-dd format and times as 00:00:00;

Thank you in advance for any tips you could provide!

Upvotes: 1

Views: 126

Answers (2)

Isisco
Isisco

Reputation: 141

SELECT *
FROM events_table WHERE
Date_From = 'start_event_date' AND  Date_To = 'end_event_date' AND Time_From = 'start_event_time'  AND  Time_To = 'end_event_time';

However, Anirudha's suggestion should be more efficient to manage depending on what you want to achieve.

Upvotes: 2

Anirudha Biswas
Anirudha Biswas

Reputation: 128

Instead of managing date and time separately use datetime filed. Please check my table structure (table name is meeting).

enter image description here

And the table with some sample data.

enter image description here

After that, you can run MySQL query to check the given time is there on the table or not. For your reference, I have given you a sample query. Hope this will help you.

SELECT * FROM meeting WHERE ('2018-03-08 10:00:00' between start_date_time and end_date_time) AND ('2018-03-08 13:00:00' between start_date_time and end_date_time);

Upvotes: 1

Related Questions