PHP/MySQL Availability Problems

[prueba123a]

Hi, I'm developing a system for my school so the school administrators can plan their events.

 

In every event there are people which are inserted to a MySQL table called 'people'. The structure for that table is:

 

id_person

name_person

mail

cost_per_hour

 

Then they are added to a table called 'events' which has the following structure:

 

id_event

id_person

name_event

start_date (DATE TIME)

end_date (DATE TIME)

 

So let's say they add the person 'Joe' to the event 'cleaning our school' which starts on March 17th, 2009 and ends May 20th, 2009. The event will happen in all the previous days from 10:00 AM to 11:00 AM and the row for that will be:

 

id_event=1

id_person=1

name_event=Joe

start_date= 2009-03-17 10:00:00

end_date= 2009-05-20 11:00:00

 

BUT HERE IS THE GREAT PROBLEM

 

Let's say that the admins want to use 'Joe' in a different event that starts on the same starting day and ends on the the same last day. BUT IT STARTS AT 5:00 PM AND ENDS 6:00 PM INSTEAD.

 

How can I make a script that returns 'Joe' as available on that days for all the hours BUT from 10:00 AM to 11:00 AM?

 

Do I have to modify my 'events' table structure?

 

Any help will be apreciated

 

[samshel]

your table structure is fine !

it contains date and time, so you should be able to do what u need.

it will depend on the queries when you check availability and conflict, you will have to check date as well as time,.

[rameshfaj]

in start_date and end_date you have date and time, so you can access the time and do any manipulations you desire. I suggest you to use "start_time" and "end_time" as the field names instead.