scheduling database

[jrthor2]

I need to create a PHP application that uses MySQL database that allows users to schedule a meeting time with "Leaders". Each leader needs their own timeslots that are available. What would the best way be to design a database structure that my page would have a dropdown of leaders, you select the leader you want, and another dropdown displays the timeslots that are still available for that leader. I also need to store the leader, timeslot, email, phone and customer name.

thanks

[chazzy]

are the timeslots fixed or variable length? are they continuous? then it should be easy enough to say that each leader has 16 (or 8 or 32 or however many) timeslots a day, then update the timeslot to include the other party and status.

[jrthor2]

I'm not exactly sure what you are asking. One person might have a schedule of 1pm - 7pm, and another may have from 3pm - 5pm. I'd say they it might be possible that a person might have a schedule like 1pm - 4pm and 6px - 9pm.

I was thinking of having a table that had all the times from 5am - 10pm (or so), and then having a leaders table that had the leaders email, their name and a time column that mapped back to the times table. Maybe in the leaders table, there would be a row for each time the leader had available, and also had a available flag, and when a user selected a time, it would update the flag "N" and then inserted a row into a schedules table that would have all the users info, along with a column that indicated the user and the time selected. Do you think this would work?

Thanks

[jrthor2]

Ok, I almost got this working, but I have an issue with my sql when I only want to display the available times that are left, meaning some times have already been selected by a user. I have the below tables and columns:

meeting_times:
id
time

meeting_leader2times (can have multiple leaderId's the same, but with different timeId's). This is the table I use to say this leader has these available time slots:
id
leaderId
timeId

meeting_schedules:
id
leader (id)
time (id)
first name
last name
email
phone

What I'm trying to do is get all the times for a leader that have not been scheduled. Here is the sql that I have, but it is giving me all the times that leader has available, and not removing the times that have been scheduled:

SELECT distinct(t.id), TIME_FORMAT( t.time, '%h:%i %p' ) AS time
FROM meeting_leader2times l2t, meeting_times t, meeting_schedules s
WHERE l2t.leaderId = '2'
AND l2t.timeId = t.id
AND l2t.timeId != s.time
AND t.id != s.time
ORDER BY l2t.timeId ASC

Could someone help me?

Thanks!!