24 hr schedule problems

SeenGee

Hi, i asked a question similar to this a few days ago but i still havent been able to really find a solution. The situation is this, i'm creating a schedule system for a 24 hour radio station but i'm having problems getting my head around how to set the database backend up for it. The problem i'm having really is trying to resolve the connection between the schedule and actual dates and times. This is how the schedule needs to work, the day is divided into two blocks of 12 hours, the first 12 hours of the schedule will be repeated in the second 12 hour block. Each day in the week will be different but each week will remain the same for the whole of that month.

I'm just having real problems in my head trying to work out what the best way will be to put the schedule together, it needs to be in such a way as the client will be able to easily update, edit and plan for weeks ahead. So far i have a schedule table in the db, this has this structure:

CREATE TABLE schedule (
schedule_id int(11) NOT NULL auto_increment,
slot_1 int(11) NOT NULL default '0',
slot_2 int(11) NOT NULL default '0',
slot_3 int(11) NOT NULL default '0',
slot_4 int(11) NOT NULL default '0',
slot_5 int(11) NOT NULL default '0',
slot_6 int(11) NOT NULL default '0',
slot_7 int(11) NOT NULL default '0',
slot_8 int(11) NOT NULL default '0',
slot_9 int(11) NOT NULL default '0',
slot_10 int(11) NOT NULL default '0',
slot_11 int(11) NOT NULL default '0',
slot_12 int(11) NOT NULL default '0',
PRIMARY KEY (schedule_id)
) TYPE=MyISAM;

Each of the different slot_numbers will hold a dj_id that will reference a dj table i have so that further info can be linked to. I was also thinking of adding a table that will hold individual weeks, each holding the schedule_id from the schedule table under the specific days(as columns in the weeks table) and also containing a start and end date for the week. This is a major part of the problem though, how can i find out what the date will be in a weeks time (and indeed in any day in between)from any given date, given leap years and months with different days.

Thanks for taking the time to read this through, any ideas people might have would be greatly appreciated. Thanks.

TheDefender

First thing first... If you want to use and manipulate dates, you should dig into the PHP manual and read about the date function:
http://www.php.net/manual/en/function.date.php

For the kind of stuff you are doing, you probably want to stick with Unix Time Stamps for the date manipulation...

Here's what I mean (There are other ways of doing this, but I am showing you this so you can get a basic idea of how to manipulate the data):

<?
$today = date("F j, Y, g:i:s a", date("U"));
$oneweek = date("F j, Y, g:i:s a", (date("U")+604800));

echo $today."<BR/>".$oneweek;
?>

The 604800 is the number of seconds in 7 days, so you would just add that to the seconds of today's date, and then parse that.

Like I said, you really wouldn't code it this way, but this will hopefully help you to see how to manipulate dates.