Calendar help please

[chris_stfc]

Hello.
I’ve been looking at an event calendar that I found one suits my needs.

I made changes to the code to suit my needs, and now however, I want to make a couple more changes and would appreciate some help, as i'm stuck!.

The current calendar I have finds the date in the database for each event where the start_date happens and makes it a hyperlink in the calendar created.

Now what I want it to do is this:

Make the calendar find the start_date and end_date in my database for each event and makes the dates hyperlinks where they are EQUAL TO start_date OR BETWEEN OR EQUAL TO end_date.

Now I **think** I have my SQL statement right, but would anybody be able to help me understand how I can make the dates hyperlinks on the calendar for:

- The date the event starts (done already)
- The days in-between the events
- The end_date

So for example if an event started on 12th may and finished on 15th may the days. 12th, 13th, 14th and 15th would be hyperlinks in the calendar?

Thanks

Chris

<?php 
         
// Old Query Was to Just get the date
//$sql = "SELECT start_date FROM cms_events GROUP BY start_date ORDER BY start_date";

// New Query Want to get the between dates?
$sql = "SELECT * FROM cms_events WHERE start_date >= CURRENT_DATE() AND end_date <= CURRENT_DATE()";
$qry = mysql_query($sql) or die("SQL Error: $sql<br" . mysql_error()); 

$arrEVENTS = array(); 
WHILE ($r = mysql_fetch_array($qry)) : 
  $arrEVENTS[$r['start_date']] = 1; 
ENDWHILE; 

$pMON = (isset($_GET['m']) ? $_GET['m'] : gmdate("Ym")); 

$thisMONyy = substr($pMON,0,4); 
$thisMONmm = substr($pMON,4,2); 

$BOM     = gmmktime(0,0,0,$thisMONmm,1,$thisMONyy); 
$EOM     = gmmktime(0,0,0,$thisMONmm+1,0,$thisMONyy); 
$BOC     = gmmktime(0,0,0,$thisMONmm,1-gmdate("w", $BOM),$thisMONyy); 
$EOC     = gmmktime(0,0,0,$thisMONmm+1,0+(6-gmdate("w",$EOM)),$thisMONyy); 

$PREVmon = gmdate("Ym",gmmktime(0,0,0,$thisMONmm-1,1,$thisMONyy)); 
$NEXTmon = gmdate("Ym",gmmktime(0,0,0,$thisMONmm+1,1,$thisMONyy)); 

$PREVyear = gmdate("Ym",gmmktime(0,0,0,$thisMONmm,1,$thisMONyy-1)); 
$NEXTyear = gmdate("Ym",gmmktime(0,0,0,$thisMONmm,1,$thisMONyy+1)); 

print "<table style='smCal' cellspacing=0 cellpadding=0 bgcolor=c0c0c0>"; 
print "<tr bgcolor=navy>"; 
print "<td class='month' width='100%' colspan='7'>"; 
print "<table width='100%'><tr>"; 
print "<td align='left'>"; 
print "<a href='{$_SERVER['PHP_SELF']}?m=$PREVyear'>&laquo;</a>&nbsp;"; 
print "<a href='{$_SERVER['PHP_SELF']}?m=$PREVmon'>&lt;</a>"; 
print "</td>"; 
print "<td class='month'>" . gmdate("F Y", $BOM) . "</td>"; 
print "<td align='right'>"; 
print "<a href='{$_SERVER['PHP_SELF']}?m=$NEXTmon'>&gt;</a>"; 
print "<a href='{$_SERVER['PHP_SELF']}?m=$NEXTyear'>&raquo;</a>"; 
print "</td>"; 
print "</tr></table>"; 
print "</td>"; 
print "<tr>"; 

print "<tr class='header'>"; 
$DOWheader = array("Su","Mo","Tu","We","Th","Fr","Sa"); 
foreach($DOWheader as $dow => $thisHEADER) : 
  print "<td align='right'><b>$thisHEADER</b></td>"; 
endforeach; 
print "</tr>\n"; 

$rows = 0; 
$x = $BOC; 
DO { 
  $dow = gmdate("w", $x); 
  if ($dow == 0) : print "<tr>\n"; endif; 
  $thisSTYLE = ""; 
  IF (isset($arrEVENTS[gmdate("Y-m-d",$x)])) : 
    $thisSTYLE = "style='color:DarkGreen;'"; 
  ENDIF; 
  print "<td class='" . (gmdate("Ym",$x) == $pMON ? "active" : "inactive") . "'>"; 
  IF (isset($arrEVENTS[gmdate("Y-m-d",$x)])) : 
  print "<a href=\"javascript:void(null);\" onclick=\"pop_up('details.php?start_date=" . gmdate("Y-m-d", $x) . "');\">"; 
  ENDIF; 
  print gmdate("j",$x); 
  IF (isset($arrEVENTS[gmdate("Y-m-d",$x)])) : 
    print "</a>"; 
  ENDIF; 
  print "</td>\n"; 
  if ($dow == 6) : print "</tr>\n"; $rows++; endif; 
  $x+=86400; 
} WHILE ($x <= $EOC and $rows <= 5); 

print "<table>"; 
?>

[jondolar]

First, it doesn't really make sense to me that you have start_date >= current_date() and end_date <= current_date() how can something start today or latter and end today or earlier.

If you want to display a calendar and have links on the calendar only when there is an event that covers that day, then you can call one database query that covers the month and put all the records in an array, then as you build the calendar, look in the array to see if there is a match and display the hyperlink (much better than searching the database for each day).

In general, you can use this query to get all the results for the month:
$sql = "SELECT * FROM cms_events WHERE start_date >= '$startofmonth' AND end_date <= '$endofmonth'";

Stuff the results in a database using the month date as the arary index.

In your loop, look at date index in the array to determine if an event occurs then.

Good luck.

[Cups]

Ive been pondering this a long while, and I think Jondolar calls it right.

Either you decide that each day is an entry in your database, or you decide upon some kind of span (start date -> end date).

I think it depends on your app, but generally storing data is only done occassionally and selects are done all the time ... again and again and again.

It might be easy to store start and end date, but as he so well describes getting it out can start to get messy, arrays and multidimensional arrays.

I mean, if you are a dab hand with multi arrays then fine .. but you wouldnt be asking the question if you were - you would spot the answer.

So, I'd say rethink what you are doing - making a 3 day event save 3 individual entries in your database - it may seem wasteful, difficult even - and will no doubt involve some db schema changes ... getting the data out will seem a piece of pi$$.

Write is occasionally, read is all the bloomin time.

Let us know what you think, but the more I think about it the more I intend doing this.

I'd really like to hear anyone elses opinion on this...

[chris_stfc]

Cheers Guys,

multidimensional arrays are deffo not my thing. Still learning the basics of php really!

So, I'd say rethink what you are doing - making a 3 day event save 3 individual entries in your database - it may seem wasteful, difficult even - and will no doubt involve some db schema changes ... getting the data out will seem a piece of pi$$.

Thanks, but this way could be very tricky for a few reasons I can think of:

1) If an event lasts 16 days an online user will get peed off entering 2007-06-06, 2007-06-07, 2007-06-08 etc... for each event

2) and then i'd have to change my schema to encorporate first_date, second_date, third_date etc.. An event could span over 4 weeks, so is it right to have 28 fields in mysql database for fields?

Thanks

[catweasel]

Now what I want it to do is this:

Make the calendar find the start_date and end_date in my database for each event and makes the dates hyperlinks where they are EQUAL TO start_date OR BETWEEN OR EQUAL TO end_date.

all you need is the start and end dates in the database.. then you can generate a range array to get all the dates in between -

<?php
$query = "SELECT DATE_FORMAT(start_date,'%Y%m%d') AS start,DATE_FORMAT(end_date,'%Y%m%d') AS end FROM cms_events WHERE end_date > CURRENT_DATE";
if ($result = mysql_query($query)) {
    while ($row = mysql_fetch_assoc($result)) {
        echo "--- BEGIN EVENT ---<br>";
        $range = range($row['start'],$row['end']);
        foreach ($range as $ymd) {
            echo date ("d F Y",strtotime($ymd))."<br>";
        }
        echo "--- END EVENT ---<br><br>";
    }
}
?>

Note I merely select records where end_date is greater than the current date. If an event has started in the past but won't finish untill sometime in the future it's still relevant to the user.. only when the end_date has past will it cease to be relevant.

[Cups]

1) If an event lasts 16 days an online user will get peed off entering 2007-06-06, 2007-06-07, 2007-06-08 etc... for each event

2) and then i'd have to change my schema to encorporate first_date, second_date, third_date etc.. An event could span over 4 weeks, so is it right to have 28 fields in mysql database for fields?

This is a really interesting thread. I like catweasels reply,

In response to 1) above I was planning to let them put in start and end dates and create a list of dates with tickboxes so they uncheck those that don't apply. ( What if its a music festival running for 28 days but not on Mondays? )

Then go off and store each selected date in the db automatically.

I suppose my POV is jaundiced because I have written (local) diary apps in the past and they all fail on one big question:

Can the admin user add recurring events like:

"this event happens on the 2nd weds night of the month at 7pm for 1 year, but not in August, and from September to March it starts at 7.30pm."

I have pretty well got the logic to create an array of dates that users choose
from. I don't see a 28 day event in any different light.

(though will definitely try and make use of range() for dates as catweasel has showed us)

I suppose I am determined to capture time as well, maybe thats causing a different POV.

What happens if the above music festival always starts at 6pm except on Saturdays when it starts at 2pm?

In reply to your point 2) if each date/event occurence is a different entry then I dont see the need to know if a day is first or second. All you want is:

//simplified
"select * from diary where date is $today"

[chris_stfc]

Cheers for the comments people

Cups, with re-occurring events I have not totally thought that through in my mind, but i'm somehow thinking I could maybe hve a field in the database which you somehow specify it's a re-occuring event (ie a weekly event) from which I then a cron job that does this. However i've not really thought that far ahead, but i'm sure I could work out something with re-occuring events with a Cron Job...

My main concern still though is still th problem I started with and getting the calendar to span the dates. Ie if there is a event running from 2007-05-05, 2007-05-06, 2007-05-07 then I need the calendar to not only show the first date (as it currently does) as a hyperlink (5th May) but also 6th and 7th May as a hyperlink as the event spans 3 days.

Looking at catweasel's post I see my SQL statement was wrong, so by using the code he kindly put up it will select all the events, but i'm still totally baffled on how to make each day in the calendar a hyperlink and not just the start_date?

<?php 
          

// New Query Want to get the between dates? 

$sql = "SELECT DATE_FORMAT(start_date,'%Y%m%d') AS start,DATE_FORMAT(end_date,'%Y%m%d') AS end FROM cms_events";

$qry = mysql_query($sql) or die("SQL Error: $sql<br" . mysql_error()); 

$arrEVENTS = array(); 
WHILE ($r = mysql_fetch_array($qry)) : 
  $arrEVENTS[$r['start_date']] = 1; 
ENDWHILE; 

$pMON = (isset($_GET['m']) ? $_GET['m'] : gmdate("Ym")); 

$thisMONyy = substr($pMON,0,4); 
$thisMONmm = substr($pMON,4,2); 

$BOM     = gmmktime(0,0,0,$thisMONmm,1,$thisMONyy); 
$EOM     = gmmktime(0,0,0,$thisMONmm+1,0,$thisMONyy); 
$BOC     = gmmktime(0,0,0,$thisMONmm,1-gmdate("w", $BOM),$thisMONyy); 
$EOC     = gmmktime(0,0,0,$thisMONmm+1,0+(6-gmdate("w",$EOM)),$thisMONyy); 

$PREVmon = gmdate("Ym",gmmktime(0,0,0,$thisMONmm-1,1,$thisMONyy)); 
$NEXTmon = gmdate("Ym",gmmktime(0,0,0,$thisMONmm+1,1,$thisMONyy)); 

$PREVyear = gmdate("Ym",gmmktime(0,0,0,$thisMONmm,1,$thisMONyy-1)); 
$NEXTyear = gmdate("Ym",gmmktime(0,0,0,$thisMONmm,1,$thisMONyy+1)); 

print "<table style='smCal' cellspacing=0 cellpadding=0 bgcolor=c0c0c0>"; 
print "<tr bgcolor=navy>"; 
print "<td class='month' width='100%' colspan='7'>"; 
print "<table width='100%'><tr>"; 
print "<td align='left'>"; 
print "<a href='{$_SERVER['PHP_SELF']}?m=$PREVyear'>&laquo;</a>&nbsp;"; 
print "<a href='{$_SERVER['PHP_SELF']}?m=$PREVmon'>&lt;</a>"; 
print "</td>"; 
print "<td class='month'>" . gmdate("F Y", $BOM) . "</td>"; 
print "<td align='right'>"; 
print "<a href='{$_SERVER['PHP_SELF']}?m=$NEXTmon'>&gt;</a>"; 
print "<a href='{$_SERVER['PHP_SELF']}?m=$NEXTyear'>&raquo;</a>"; 
print "</td>"; 
print "</tr></table>"; 
print "</td>"; 
print "<tr>"; 

print "<tr class='header'>"; 
$DOWheader = array("Su","Mo","Tu","We","Th","Fr","Sa"); 
foreach($DOWheader as $dow => $thisHEADER) : 
  print "<td align='right'><b>$thisHEADER</b></td>"; 
endforeach; 
print "</tr>\n"; 

$rows = 0; 
$x = $BOC; 
DO { 
  $dow = gmdate("w", $x); 
  if ($dow == 0) : print "<tr>\n"; endif; 
  $thisSTYLE = ""; 
  IF (isset($arrEVENTS[gmdate("Y-m-d",$x)])) : 
    $thisSTYLE = "style='color:DarkGreen;'"; 
  ENDIF; 
  print "<td class='" . (gmdate("Ym",$x) == $pMON ? "active" : "inactive") . "'>"; 
  IF (isset($arrEVENTS[gmdate("Y-m-d",$x)])) : 
  print "<a href=\"javascript:void(null);\" onclick=\"pop_up('details.php?start_date=" . gmdate("Y-m-d", $x) . "');\">"; 
  ENDIF; 
  print gmdate("j",$x); 
  IF (isset($arrEVENTS[gmdate("Y-m-d",$x)])) : 
    print "</a>"; 
  ENDIF; 
  print "</td>\n"; 
  if ($dow == 6) : print "</tr>\n"; $rows++; endif; 
  $x+=86400; 
} WHILE ($x <= $EOC and $rows <= 5); 

print "<table>"; 
?>

[jondolar]

You know, I just realized that I wrote a nifty calendar program about 5 years ago (my first php project) that is 99&#37; complete at www.quickreminders.com. I never launched it though.

I remember now that I used a separate table for the days of the project. When the admin entered an event, I added the event into one table, then entered multiple records into a separate table with an id pointing back to the original table. This allowed me to handle multi-day events and recurring events. I would just create a record for each day of the event.

Take a look and if you like it I can send you a snippet of code that handles the calendar piece. Warning, it was also my first HTML project so don't laugh.

[Cups]

>Cups, with re-occurring events I have not totally thought that through in my mind, but i'm somehow thinking I could maybe hve a field in the database which you somehow specify it's a re-occuring event (ie a weekly event) from which I then a cron job that does this.

Sorry to sound like a righteous git and I dont want to rain on your parade, but when you have had users using your app in anger, and found it wanting - then it kind of burns.

What I learnt is that in reality, that "recurring event" won't happen every single week, the world simply doesn't work like that.

The best you may be able do is to help Admin's "input once and use often".

Which is what I think Jondolar is saying.

I remember now that I used a separate table for the days of the project. When the admin entered an event, I added the event into one table, then entered multiple records into a separate table with an id pointing back to the original table. This allowed me to handle multi-day events and recurring events. I would just create a record for each day of the event.

If you are saying you describe the event once, and point to it x times in true normalized database fashion, then fine.

The thing is, although the base information stays the same, they (damn them) will also want to change the description slighltly for each recurring event.

Take this example.
--------------------------

The yoga class meets every second Friday night at 7pm.

During the summer holidays there are no meetings.

The last meeting of the year starts at 8pm and is an open night, open to all partners and friends.

The last meeting before easter starts at 6pm, and weather permitting is held in the school playing fields, 200 meters away.

In May, there are optional Tai-chi lessons free of charge for half an hour after the end of the session.

-------------------------------

Yet all of these items share a great amount of text, where, how, what the lessons are like, skill level etc etc

Hence I have come to conclusion that to make a diary work as Admin's want:

They need to provide a range of date options like:

a single day
a range of days e.g.
- first tues of month - for x months
- every weds - for x months
- every 2nd thurs - for x months
- from 2 april for 5 days

Then from the list of dates they can deselect those that dont apply.

For those that do apply:

Allow them to apply some default text to each entry.

Save it all as single rows in a dbase

Show them all the rows they have added and allow them to edit each one individually if they want to.

I can't think of another way of doing it that matches real world requirements. I just find it silly that I dont share with you what I have learned, especially as there might be someone else out there who can put me on the right path.

(I found this paper on temporal expressions by Martin Fowler useful in my thinking about getting recurring events )

[catweasel]

Looking at catweasel's post I see my SQL statement was wrong, so by using the code he kindly put up it will select all the events, but i'm still totally baffled on how to make each day in the calendar a hyperlink and not just the start_date?

You can make each day a link simply by outputting the data wrapped in html ...
so instead of this line -
echo date ("d F Y",strtotime($ymd))."<br>";

you would put something like this -
echo "<a href='youlink.html'>".date ("d F Y",strtotime($ymd))."</a>";

Also.. any calendar program, if you want this app to be scalable well into the future, should incorporate in some way the doomsday algorithm. This is a algorithm used to calculate any given day of the week in any given year.

[Mastodont]

I agree with jondolar. For events calendar I've got two tables:

- first with event_id, name, place ... startDay, endDay, recurringType, recurringDays ... commonDescription .. (one record per event)
- second with event_id, occurDay, startTime, endTime, individualDescription (count of records for event = count of occurences)

Admin selects type and days of recurring (each Monday till Friday, second Thursday of month etc.), script generates all days and creates records.

When update is needed, admin can change type and days of recurring (which are stored in first table), script deletes old records in second table and creates new.

For each day in calendar, list of actions is simple SELECT ".. WHERE occurDay = ..." Queries for whole month or week are simple, too.

[chris_stfc]

Thanks all,

Turning into a good thread this and defintely something to think about further. Hopefully it will give users searching sitepoint for this type of thing a starting point to think about when it comes to events

I like Mastodont's idea of two tables and the table structure he describes. That seems the best sollution to 're-occurring' events and thats defintely something i'm going to look into. The problem with a calendar script as Cups rightly points out is I guess there are all types of scenarios that could occur with events being cancelled one week, breaks for summer weeks etc... Defo something to defintely think about as well.

catweasel i'm still utterly confused how to make these spanning 'x-day' events each a link as the below makes nothing a link:

You can make each day a link simply by outputting the data wrapped in html ...
so instead of this line -
echo date ("d F Y",strtotime($ymd))."<br>";

you would put something like this -
echo "<a href='youlink.html'>".date ("d F Y",strtotime($ymd))."</a>";

<? 
         

// New Query Want to get the between dates? 

$sql = "SELECT DATE_FORMAT(start_date,'%Y%m%d') AS start,DATE_FORMAT(end_date,'%Y%m%d') AS end FROM cms_events"; 

$qry = mysql_query($sql) or die("SQL Error: $sql<br" . mysql_error()); 

$arrEVENTS = array(); 
WHILE ($r = mysql_fetch_array($qry)) : 
  $arrEVENTS[$r['start_date']] = 1; 
ENDWHILE; 

$pMON = (isset($_GET['m']) ? $_GET['m'] : gmdate("Ym")); 

$thisMONyy = substr($pMON,0,4); 
$thisMONmm = substr($pMON,4,2); 

$BOM     = gmmktime(0,0,0,$thisMONmm,1,$thisMONyy); 
$EOM     = gmmktime(0,0,0,$thisMONmm+1,0,$thisMONyy); 
$BOC     = gmmktime(0,0,0,$thisMONmm,1-gmdate("w", $BOM),$thisMONyy); 
$EOC     = gmmktime(0,0,0,$thisMONmm+1,0+(6-gmdate("w",$EOM)),$thisMONyy); 

$PREVmon = gmdate("Ym",gmmktime(0,0,0,$thisMONmm-1,1,$thisMONyy)); 
$NEXTmon = gmdate("Ym",gmmktime(0,0,0,$thisMONmm+1,1,$thisMONyy)); 

$PREVyear = gmdate("Ym",gmmktime(0,0,0,$thisMONmm,1,$thisMONyy-1)); 
$NEXTyear = gmdate("Ym",gmmktime(0,0,0,$thisMONmm,1,$thisMONyy+1)); 

print "<table style='smCal' cellspacing=0 cellpadding=0 bgcolor=c0c0c0>"; 
print "<tr bgcolor=navy>"; 
print "<td class='month' width='100%' colspan='7'>"; 
print "<table width='100%'><tr>"; 
print "<td align='left'>"; 
print "<a href='{$_SERVER['PHP_SELF']}?m=$PREVyear'>&laquo;</a>&nbsp;"; 
print "<a href='{$_SERVER['PHP_SELF']}?m=$PREVmon'>&lt;</a>"; 
print "</td>"; 
print "<td class='month'>" . gmdate("F Y", $BOM) . "</td>"; 
print "<td align='right'>"; 
print "<a href='{$_SERVER['PHP_SELF']}?m=$NEXTmon'>&gt;</a>"; 
print "<a href='{$_SERVER['PHP_SELF']}?m=$NEXTyear'>&raquo;</a>"; 
print "</td>"; 
print "</tr></table>"; 
print "</td>"; 
print "<tr>"; 

print "<tr class='header'>"; 
$DOWheader = array("Su","Mo","Tu","We","Th","Fr","Sa"); 
foreach($DOWheader as $dow => $thisHEADER) : 
  print "<td align='right'><b>$thisHEADER</b></td>"; 
endforeach; 
print "</tr>\n"; 

$rows = 0; 
$x = $BOC; 
DO { 
  $dow = gmdate("w", $x); 
  if ($dow == 0) : print "<tr>\n"; endif; 
  $thisSTYLE = ""; 
  IF (isset($arrEVENTS[gmdate("Y-m-d",$x)])) : 
    $thisSTYLE = "style='color:DarkGreen;'"; 
  ENDIF; 
  print "<td class='" . (gmdate("Ym",$x) == $pMON ? "active" : "inactive") . "'>"; 
  IF (isset($arrEVENTS[gmdate("Y-m-d",$x)])) : 
  // OLD CODE TO PRINT JUST START DATE
  // print "<a href=\"javascript:void(null);\" onclick=\"pop_up('details.php?start_date=" . gmdate("Y-m-d", $x) . "');\">"; 
  print "<a href='youlink.html'>".date ("d F Y",strtotime("Y-m-d"))."</a>";
  ENDIF; 
  print gmdate("j",$x); 
  IF (isset($arrEVENTS[gmdate("Y-m-d",$x)])) : 
    print "</a>"; 
  ENDIF; 
  print "</td>\n"; 
  if ($dow == 6) : print "</tr>\n"; $rows++; endif; 
  $x+=86400; 
} WHILE ($x <= $EOC and $rows <= 5); 

print "<table>"; 
?>

Thanks