[SOLVED] Calendar Database Design

[Carterhost]

I've been racking my brains over this one, searched t'interweb countless times, and keep coming up with things that are too complicated.

I want to build a calendar display page, with dates "blocked out" to signify that i'm busy.

I want to be able to select sate ranges that i'm busy, and to be able to delete blocks if my schedule frees up.

 

What I can't figure out is the best database design for this.  How would it be best to insert a date range into a database, and pull it out again?

I was thinking along the lines of:

 

ID          START                END

 

For which each field contains a block of unavailable dates.

 

If I want to display say, 3 months, how would I select just the date blocks within those months?

 

 

[DeathStar]

It is Internet.

 

And how do you want to display the calender?

You can use a simple loop:

while($data = mysql_fetch_array($query)){
if ($data['blocked'] == TRUE){
echo '<p class="red">' . $data['time'] . '</p>'; 
} 
else {
echo '<p class="normall">' . $data['time'] . '</p>';  
}
}

 

Or you could just edit that structure into a week and change the loop a bit:

while($data = mysql_fetch_array($query)){
echo '<tr><td>' . $data['time'] . '</td><td>' . $data['time2'] . '</td><td>' . $data['time3'] . '</td><td>' . $data['time4'] . '</td><td>' . $data['time5'] . '</td><td>' . $data['time6'] . '</td><td>' . $data['time7'] . '</td>'; 
}

 

Just put the if statements in.

[Carterhost]

I think I have it, I'm going to break it down within the database:

 

ID | START_DAY | START_MONTH | START_YEAR | END_DAY | END_MONTH | END_YEAR | COMMENTS

 

Then I can select each busy block per month/year, and output the day as a class change on my calendar CSS.

 

If noone can see any problems, I'll give it a go!

[Barand]

Better to keep dates as DATE fields

 

id int

startdate DATE                    //format YYYY-MM-DD

enddate  DATE

comments varchar(255)

[Carterhost]

Better to keep dates as DATE fields

 

id int

startdate DATE                     //format YYYY-MM-DD

enddate   DATE

comments varchar(255)

 

Oh, can you slect from the middle of a date field in SQL then?

[Barand]

SELECT MONTH(startdate)

 

SELECT DAY(startdate)

 

see

 

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

[Carterhost]

Cheers!