PHP - Events Calendar

sirnutty1
sirnutty1 used Ask the Experts™
on
Hi Experts

I'm in the process of integrating an events calendar into a website. I'm really keen to not resort to a pre-made solution as I'm trying to learn the ropes as I go along.

I've managed to get a simple calendar working and highlighting the current day. I also have an events database table that contains event data.

I would like to have every day in the calendar on which an events falls on to become a link that my users can click to go through to a seperate  events page. I have a DW recordset returning the data I need but I am stumped as to the procedure for relating the calendar days to the event (if one exists).

My events table has a fromDate field and a toDate field so I would like any event that covers more than one days to be reflected in the calendar.

Any help would be sincerely appreciated, I have included the code below.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:

<?php require_once('../../../Connections/connEACKDXP.php'); ?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  if (PHP_VERSION < 6) {
    $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
  }

  $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
}

mysql_select_db($database_connEACKDXP, $connEACKDXP);
$query_rsEvents = "SELECT *, DATE_FORMAT(fromDate, '%Y-%c-%e') as fromCalDate ,  DATE_FORMAT(toDate, '%Y-%c-%e') as toCalDate FROM module_calendar_events";
$rsEvents = mysql_query($query_rsEvents, $connEACKDXP) or die(mysql_error());
$row_rsEvents = mysql_fetch_assoc($rsEvents);
$totalRows_rsEvents = mysql_num_rows($rsEvents);
?>
<!--<link href="/site/css/calendar.css" rel="stylesheet" type="text/css" />
<link rel="stylesheet" type="text/css" href="/site/css/main.css"/>-->
<?php
  $monthNames = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");
	if (!isset($_REQUEST["month"])) $_REQUEST["month"] = date("n");
	if (!isset($_REQUEST["year"]))  $_REQUEST["year"]  = date("Y");
	
  $cMonth = $_REQUEST["month"];
  $cYear  = $_REQUEST["year"];
 
  $todayDate = date("Y-n-j"); 
                
  $prev_year = $cYear;
  $next_year = $cYear;

 	$prev_month = $cMonth-1;
 	$next_month = $cMonth+1;

 	if ($prev_month == 0 ) {
    $prev_month = 12;
    $prev_year = $cYear - 1;
  }
 	if ($next_month == 13 ) {
    $next_month = 1;
    $next_year = $cYear + 1;
  }
?>
   <div id="calendar" name="calendar">
    <table width="100%" cellpadding="0" cellspacing="0">
        <tr>
            <td align="center">
              <table width="100%" border="0" cellpadding="0" cellspacing="10">
                <tr align="center">
                  <td colspan="7" class="contentBoxInnerBorder"><table width="100%" border="0" cellspacing="0" cellpadding="0">
                    <tr>
                      <td><a href="<?php echo $_SERVER["PHP_SELF"] . "?month=". $prev_month . "&year=" . $prev_year; ?>">&lt;&lt;</a></td>
                      <td width="100%" align="center"><strong><?php echo $monthNames[$cMonth-1].' '.$cYear; ?></strong></td>
                      <td><a href="<?php echo $_SERVER["PHP_SELF"] . "?month=". $next_month . "&year=" . $next_year; ?>">&gt;&gt;</a></td>
                    </tr>
                  </table></td>
                </tr>
                <tr>
                  <td align="center"><strong>M</strong></td>
                  <td align="center"><strong>T</strong></td>
                  <td align="center"><strong>W</strong></td>
                  <td align="center"><strong>T</strong></td>
                  <td align="center"><strong>F</strong></td>
                  <td align="center"><strong>S</strong></td>
                  <td align="center"><strong>S</strong></td>
                </tr>

                <?php 
                	$timestamp = mktime(0,0,0,$cMonth,0,$cYear);
                	$maxday    = date("t",$timestamp);
                	$thismonth = getdate ($timestamp);
                	$startday  = $thismonth['wday'];
										
                  for ($i=0; $i<($maxday+$startday); $i++) {
                    if(($i % 7) == 7 ) echo "<tr>\n";
                    if($i < $startday) echo "<td></td>\n";
					elseif(($cYear . "-" . $cMonth . "-" . ($i - $startday + 1)) == $todayDate) echo "<td class='calendarToday' align='center' valign='middle' height='20px'>". ($i - $startday + 1) . "</td>\n";
					else echo "<td align='center' valign='middle' height='20px'>". ($i - $startday + 1) . "</td>\n";
					if(($i % 7) == 6 ) echo "</tr>\n";
                  }  
                 ?>
              </table>
            </td>
        </tr>
    </table>
</div>
   <?php
mysql_free_result($rsEvents);
?>

Open in new window

Most Valuable Expert 2011
Top Expert 2016
Commented:
The dreamweaver stuff is over my head, but I can help you a little with calendar theory and practice.

A calendar needs three things to record an event - a key, a start DATETIME and an end DATETIME.  The other stuff, like the title, description, contacts, etc. are all part of the payload, but the "propulsion" part requires those three things.  If you want to go a little farther and add a resource ID you can get a scheduling calendar that can check for conflicts over resource acquisition.  You index the start and end fields.  If you are using a resource ID, you index that, too.

The internal representation of DATETIME fields in the calendar are all held in the MySQL DATETIME format.  This is the ISO8601 date/time format - in PHP this is date('c').  You can use the combination of strtotime() and date() to take these ISO date/time strings and repurpose them for human readable display.

Given a day, you can SELECT events that are a MySQL LIKE match on the DATETIME field (or maybe use BETWEEN).  You can make a query for each day to see if there are any events on that day.  If you are using a month calendar, this will be 30 queries, and for most calendar tables that has minimal performance implications because your start and end DATETIME values are indexed.  If there are any results sets for the day, you would make that day a link to the "daily display" page that would list all the events.

In calendar programming I have found it useful to have an array of dates.  Start with the dates all empty, then select each event that starts or ends anywhere inside the event range.  Iterate over that results set and mark all days that have an event occurrence, testing the start and end dates for each event and marking those days, as well as the days between, in the array of dates.  This gives you an array that has all the active days marked.

Hope those ideas are useful to you, and best of luck with it, ~Ray

Author

Commented:
Many thanks for your response Ray.

I have managed to get it as far as checking for events that fall on a single day but can't work out how to make it check for days that fall witin "to" and "from" events.

I have included the code below, would certainly appreciate a nudge in the right direction.
<?php
/* Connecting, selecting database */
$db_link = mysql_connect("localhost", "xxxxx", "xxxxx");
if (!$db_link) {
   die("Could not connect: " . mysql_error());
}
mysql_select_db("xxxxx") or die("Could not select database");

/* Performing SQL query */
?><link href="/site/css/calendar.css" rel="stylesheet" type="text/css" />
<link rel="stylesheet" type="text/css" href="/site/css/main.css"/>
<?php
  $monthNames = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");
	if (!isset($_REQUEST["month"])) $_REQUEST["month"] = date("n");
	if (!isset($_REQUEST["year"]))  $_REQUEST["year"]  = date("Y");
	
  $cMonth = $_REQUEST["month"];
  $cYear  = $_REQUEST["year"];
 
  $todayDate = date("Y-n-j"); 
                
  $prev_year = $cYear;
  $next_year = $cYear;

 	$prev_month = $cMonth-1;
 	$next_month = $cMonth+1;

 	if ($prev_month == 0 ) {
    $prev_month = 12;
    $prev_year = $cYear - 1;
  }
 	if ($next_month == 13 ) {
    $next_month = 1;
    $next_year = $cYear + 1;
  }

				  $events = array();
$query = "SELECT title, DATE_FORMAT(fromDate,'%Y-%c-%e') AS fromDate FROM module_calendar_events";
$result = mysql_query($query,$db_link)/* or die('cannot get results!')*/;
while($row = mysql_fetch_assoc($result)) {
$events[$row['fromDate']][] = $row;
}    
?>
   <div id="calendar" name="calendar">
    <table width="100%" cellpadding="0" cellspacing="0">
        <tr>
            <td align="center">
              <table width="100%" border="0" cellpadding="0" cellspacing="10">
                <tr align="center">
                  <td colspan="7" class="contentBoxInnerBorder"><table width="100%" border="0" cellspacing="0" cellpadding="0">
                    <tr>
                      <td><a href="<?php echo $_SERVER["PHP_SELF"] . "?month=". $prev_month . "&year=" . $prev_year; ?>">&lt;&lt;</a></td>
                      <td width="100%" align="center"><strong><?php echo $monthNames[$cMonth-1].' '.$cYear; ?></strong></td>
                      <td><a href="<?php echo $_SERVER["PHP_SELF"] . "?month=". $next_month . "&year=" . $next_year; ?>">&gt;&gt;</a></td>
                    </tr>
                  </table></td>
                </tr>
                <tr>
                  <td align="center"><strong>M</strong></td>
                  <td align="center"><strong>T</strong></td>
                  <td align="center"><strong>W</strong></td>
                  <td align="center"><strong>T</strong></td>
                  <td align="center"><strong>F</strong></td>
                  <td align="center"><strong>S</strong></td>
                  <td align="center"><strong>S</strong></td>
                </tr>

                <?php 
                	$timestamp = mktime(0,0,0,$cMonth,0,$cYear);
                	$maxday    = date("t",$timestamp);
                	$thismonth = getdate ($timestamp);
                	$startday  = $thismonth['wday'];
															
                  for ($i=0; $i<($maxday+$startday); $i++) {
					$fullCalDate = $cYear.'-'.$cMonth.'-'.($i - $startday + 1);
                                        
                    if(($i % 7) == 7 ) echo "<tr>\n";
                    if($i < $startday) echo "<td></td>\n";
					                                     
					// Check for events using the looped date and create TD's accordingly
					elseif(isset($events[$fullCalDate])) 
					{
					echo "<td align='center'";
					// Add CSS if event falls on today
						if($fullCalDate == $todayDate) echo " class='calendarToday'";
					echo " valign='middle' height='20px'><a href='events.php?date=" . $fullCalDate . "'>L</a></td>\n";
					}
					
					// All non-event days now get a TD
					else 
					{
					echo "<td align='center'";
					// Add CSS if date is today
						if($fullCalDate == $todayDate) echo " class='calendarToday'";
					echo " valign='middle' height='20px'>". ($i - $startday + 1) . "</td>\n";
					}
					if(($i % 7) == 6 ) echo "</tr>\n";
                  } 
 
                 ?>
              </table>
            </td>
        </tr>
    </table>
</div>
<?php
/* Closing connection */
mysql_close($db_link);	
?>

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial