Need Help!! - Simple Scheduling System

cdrees

Hello Everyone,

PHP newbie here, so please pardon a possibly simple request.

I am trying to design a simple calendering/scheduling system for a website I'm working on.. here are the particulars.

The website offers 18 courses and 19 seminars. Each has a title and course/seminar description.

I would like to create an admin page that allows the client to

1) add new courses or seminars with title and description

2) Allow admin to add/schedule dates for each course

So when a visitor hits the site, it looks at the present date, and displays all courses available from that point forward. They can click on any one of the results and view the course/seminar in more detail.

I'm confused about how to proceed, and how to design the tables, and how to pull the info from the tables once they're designed... I know! Total Newbie...

Should I create one table for courses and seminars, and then how do I add dates to those courses? I want the admin to be able to have a drop down that pulls data from the list of courses and seminars, and then they just type in a date. Does that info go into a separate, new table?

Any help would be GREATLY appreciated!! 😃

Thanks

Christopher Rees
A+, Network+, Server+, MCSE, MCT
COPT Certified Law Enforcement Instructor
www.teamrees.com

bpat1434

3 tables:

events
[indent]- id (auto increment, integer, not null)
- title (varchar, length: 50)
- date (timestamp)
- instructor (integer, not null)
- seats (integer, not null)
- available (integer, not null)
- registered (varchar, length: 255)
- status (integer, not null)
[/indent]
registered
[indent]- id (auto increment, integer, not null)
- name (varchar, length: 50)
- email (varchar, length: 75)
- courseID (integer, not null)
[/indent]
Instructors
[indent]- id (auto increment, integer, not null)
- name (varchar, length: 50)
- credentials (varchar, lenght: 255)
- email (varchar, length: 75)
[/indent]

The relationships would look something like:

events		  registered	  instructors
  [ id ]-------+    id	      	   +-->[ id ]
  title	       |    name	   |   name
  date	       |    email	   |   credentials
  seats	       +--->[ courseID ]   |   email
  available			   |
  [ instructor ]-------------------+
  registered
  status

So your queries would just be grabing the info from the database and the timestamp would make it easy to get the date (and format any way you want) and you can keep a running tab on if the event is open for registration (status=1 && available >0), closed due to seats or past date, as well as give a list (for instructors) as to who is signed up for the class (as a type of roster).

~Brett

Roger_Ramjet

Something like this came up yesterday and led me to WebCalendars at SourceForge. The php one looks excellent and might be just what you need, or there is a Java calendar that uses MySql to store the data. Have a look for yourself.

bpat1434

Yes Roger, one did come up yesterday. And I believe i'm coding a custom calendar for him now... lol.

~Brett

cdrees

Thanks for the info!

I have a few follow up questions..

Admin Section
When the site admin wants to schedule a course, what would the code look like for them to be able to pull a drop down list of available courses (courses or seminars already entered into the database), and then add a date to the course that's already in the database.

That's my biggest hangup conceptually, is how to add dates to a course. Is a new table necessary to hold two new fields (i.e. date and course)? In my mind, it will be a table that will might have a course (or course ID) listed many times, each with a different date field.

Then from a user's perspective, a query will return all course with a date either today or later, or another query will show all courses today's date or later.

Thanks... I'm just trying to get my head around this...

Christopher Rees
A+, Network+, Server+, MCSE, MCT
COPT Certified Law Enforcement Instructor
www.teamrees.com

Roger_Ramjet

You should have a Courses table with the info that is specific to the whole course, and another that lists course id and then all the data that is specific to each class in that course, eg date time location tutor etc
When you join them you have the full information for the course and can schedule the timetable and participants.