[RESOLVED] Linking MySQL database info with Calendar Script

[BenGilbert]

I have a basic Calendar Script that highlights the current day of the week. I need some help understanding how to also highlight specific dates pulled from a MySQL database. Like an event calendar.

I would just like the calendar to highlight each day there is an event and then I would have it link to a specific URL. So as the Calendar builds the Months, Weeks, Days, I am having trouble understanding how to also call the database and check for a date and when it matches giving that cell the appropriate css style as an event.

Here is the code.

<?php include_once 'include/functions.php' ; 
///connects to my MySQL database
?> 
<?PHP

function isAjax() {
 return isset($_SERVER['HTTP_X_REQUESTED_WITH']) &&
     $_SERVER ['HTTP_X_REQUESTED_WITH']  == 'XMLHttpRequest';
}

if(isAjax() && isset($_POST['month']))
{
	$month = $_POST['month'];
	$year = !isset($_POST['year']) ? date('Y', $current_time) : $_POST['year'];
	die(getCalendar($month,$year));
}

// Assign variables for the break down of today -- day, month and year
$month = date('m', time());
$year = date('Y', time());
$calendar = getCalendar($month,$year);

function getCalendar($month,$year)
{
	// Use the PHP time() function to find out the timestamp for the current time
	$current_time = time();

// Get the first day of the month
$month_start = mktime(0,0,0,$month, 1, $year); 

// Get the name of the month
$month_name = date('F', $month_start); 

// Figure out which day of the week the month starts on.
$first_day = date('D', $month_start);

// Assign an offset to decide which number of day of the week the month starts on.
switch($first_day)
{
case "Sun":
	$offset = 0;
	break;
case "Mon":
	$offset = 1;
	break;
case "Tue":
	$offset = 2;
	break;
case "Wed":
	$offset = 3;
	break;
case "Thu":
	$offset = 4;
	break;
case "Fri":
	$offset = 5;
	break;
case "Sat":
	$offset = 6;
	break;
} 

// determine how many days were in last month.
//  Define this using the constant: CAL_GREGORIAN
if($month == 1)
	$num_days_last = cal_days_in_month(CAL_GREGORIAN, 12, ($year -1));
else
	$num_days_last = cal_days_in_month(CAL_GREGORIAN, ($month - 1), $year);

// determine how many days are in the this month.
$num_days_current = cal_days_in_month(CAL_GREGORIAN, $month, $year); 

// Count through the days of the current month -- building an array
for($i = 0; $i < $num_days_current; $i++)
{
	$num_days_array[] = $i+1;
} 

// Count through the days of last month -- building an array
for($i = 0; $i < $num_days_last; $i++)
{
	$num_days_last_array[] = '';
}

if($offset > 0){ 
	$offset_correction = array_slice($num_days_last_array, -$offset, $offset);
	$new_count = array_merge($offset_correction, $num_days_array);
	$offset_count = count($offset_correction);
}
else
{ 
	$new_count = $num_days_array;
}

// How many days?
$current_num = count($new_count); 

// Display is to be 35 cells so if we have less than that we need to dip into next month
if($current_num > 35)
{
	$num_weeks = 6;
	$outset = (42 - $current_num);
}
else if($current_num < 35)
{
	$num_weeks = 5;
	$outset = (35 - $current_num);
}
if($current_num == 35)
{
	$num_weeks = 5;
	$outset = 0;
}

// Outset Correction
for($i = 1; $i <= $outset; $i++)
{
	$new_count[] = '';
}

// into weeks. Each week has 7 days
// array_chunk it into 7 days.
$weeks = array_chunk($new_count, 7);

// Start the output buffer to output the calendar nicely
ob_start();

$last_month = $month == 1 ? 12 : $month - 1;
$next_month = $month == 12 ? 1 : $month + 1;

// Build the heading portion of the calendar table
echo <<<EOS
<table id="calendar">
<tr>
	<td><a href="#" class="monthnav" onclick="getPrevMonth();return false;">&laquo; Prev</a></td>
	<td colspan=5 class="month">$month_name $year</b></td>
	<td><a href="#" class="monthnav" onclick="getNextMonth();return false;">Next &raquo;</a></td>
</tr>
<tr class="daynames"> 
	<td>S</td><td>M</td><td>T</td><td>W</td><td>T</td><td>F</td><td>S</td>
</tr>
EOS;


foreach($weeks AS $week){
	echo '<tr class="week">'; 
	foreach($week as $day)
	{
		if($day == date('d', $current_time) && $month == date('m', $current_time) && $year == date('Y', $current_time))
			echo '<td class="today">'.$day.'</td>';
		else
			echo '<td class="days">'.$day.'</td>';
	}
	echo '</tr>';
}

echo '</table>';

return ob_get_clean();
}
?>

<html>
<head>
<link href="./css/calendar.css" rel="stylesheet" type="text/css">
<script src="./js/prototype.js" type="text/javascript"></script>
<script type="text/javascript" language="javascript">
	var current_month = <?PHP echo @$month ?>;
	var current_year = <?PHP echo @$year ?>;

function getPrevMonth()
{
	if(current_month == 1)
	{
		current_month = 12;
		current_year = current_year - 1;
	}
	else
	{
		current_month = current_month - 1;
	}
	params = 'month='+current_month+'&year='+current_year;
	new Ajax.Updater('calendar_wrapper',window.location.pathname,{method:'post',parameters: params});
}
	function getNextMonth()
	{
		if(current_month == 12)
		{
			current_month = 1;
			current_year = current_year + 1;
		}
		else
		{
			current_month = current_month + 1;
		}
		params = 'month='+current_month+'&year='+current_year;
		new Ajax.Updater('calendar_wrapper',window.location.pathname,{method:'post',parameters: params});
	}
</script>
</head>

<body>
<div id="calendar_wrapper"><?PHP echo @$calendar ?></div>
</body>
</html>

Thank You in advance

-Ben

[dalecosp]

I've not "debugged" your code, but it looks pretty competent at first glance. What exactly are you not understanding? Can you lay out a course of action, in English --- step A, B, C ... and then translate that into PHP?

I mean, you should be able to cobble together some function, based on what you know is in the database regarding a specific activity table, that could return a boolean true if there was something on that date, and test in a conditional during your output stage; at that point, you call a different CSS class.

Some ideas:

• Suppose the date of activity is a UNIX timestamp.

• Your script calls your function with the timestamp equivalent of midnight on a particular day.

• Your function takes the timestamp, and asks the database if an event exists between the timestamp and timestamp+86400, returning TRUE or FALSE as required.

• Suppose it's a MySQL date field.

• Your function supplies a date in the proper format to the function, which asks the database if an event exists on that date, returning TRUE or FALSE as required.

HTH,

[BenGilbert]

So this is where I am at now, I have managed to successfully call dates from my database and have them appear in my calendar. See attached image. My issue at the moment is with the unique links I need for each date.

I only attached the code that outputs the calendar, you can see the rest above. This is where I have made my changes.

Currently each event that gets marked on the calendar gets the $id link from the last entry in the database. I am working on putting the $id into an array and pulling them as needed, but I am in a mental infinite loop atm.

			 
		  $query  = "SELECT * FROM event";
          $result = mysql_query($query);

	  while($row = mysql_fetch_array($result, MYSQL_ASSOC)){
      $event_date = $row['event_date'];
      $id = $row['id'];
      $event[] = $event_date;

      }

foreach($weeks AS $week){
	echo '<tr class="week">';

	foreach($week as $day)
	{


	if (in_array($month .'/'.$day.'/'.$year,$event)){
     echo '<td class="event"><a href="event.php?id='.$id.'">'.$day.'</a></td>';

     }else
	echo '<td class="days">'.$day.'</td>'; 


	}
	echo '</tr>';
}

echo '</table>';

return ob_get_clean();

Do I need to store the $id in an array and with in the if statement have another foreach with the event as the key to obtain the correct $id ?

[johanafm]

BenGilbert;10988768 wrote:

I am working on putting the $id into an array and pulling them as needed, but I am in a mental infinite loop atm.

Infinite loops are bad. But there are several ways to achieve what you want.

First however, a look at what goes wrong. The very first thing I'd recommend you to do is choose an indentation style and consistently stick to it. Otherwise it's hard to
see where loops start and end among other things, which at the very best is hard and annoying to read. What's worse is that it's likely to lead to errors in program logic.

			 
# Get all events from the db
# This should be restricted to the date range of the currently
# displayed dates in the calendar:
#		WHERE (startdate between calstart AND calend) OR
#			(enddate between calstart AND calend)
$query  = "SELECT * FROM event";
$result = mysql_query($query);

while($row = mysql_fetch_array($result, MYSQL_ASSOC))
{
	$event_date = $row['event_date'];
	# this obviously happens on each iteration
	$id = $row['id'];
	# This also happens on each iteration, so that you finally
	# have an array of ALL event dates
	$event[] = $event_date;
}
# Now that you have looped over all your events, what value do you expect
# $id to hold?

foreach($weeks AS $week)
{
	echo '<tr class="week">';

foreach($week as $day)
{
	# Does the current calendar date exist ANYWHERE
	# among the event dates?
	if (in_array($month .'/'.$day.'/'.$year,$event))
	{
		# Then echo the very last event id
		echo '<td class="event">
			<a href="event.php?id='.$id.'">'.$day.'</a></td>';
	}
	else
	{
		echo '<td class="days">'.$day.'</td>'; 
	}


}
echo '</tr>';
}

BenGilbert;10988768 wrote:

Do I need to store the $id in an array and with in the if statement have another foreach with the event as the key to obtain the correct $id ?

You could do either that, or to keep track of only one array, while also making the check for event dates a whole lot easier
And it just struck me that one other way of handling things, which may be the simplest, is to iterate over our result set, using the dates as keys

$events = array();
while ($e = mysql_fetch_assoc($result))
{
	$events[$e['event_date']] = $e;
}

since you can then directly check for events as you iterate over your calendar dates

foreach ($weeks as $day)
{
	if (isset($events["$year-$month-$day"]))
	{
		# event exists
	}
	else
		# it doesn't
}

Do note that above, I am using YYYY-MM-DD (ISO-8601) format, since that is how MySQL stores date, all in accordance with the SQL standard. Since you were using

$month .'/'.$day.'/'.$year

you are either not comparing things that makes sense, such as comparing 2011-10-30 and 10/30/2010 when they are meant to represent the same date, or you are not storing your dates as date data types but rather VARCHAR which you shouldn't do either.

But if you prefer the month/day/year format, since you shouldn't use SELECT * anyway, you might as well use the DATE or DATETIME type for storage and use DATE_FORMAT(), to convert from ISO-8601 to your own format.

[BenGilbert]

Makes perfect sense.

I have everything in working order now. Thanks for all of the guidance and input.

-Ben

[clausowitz]

Ben,

I am struggling with the same kind of calendar on my website. I would like to have the possibility to add more then one event a day. At the moment I can't.

Did you manage to get that working?

Regards
Marco

[BenGilbert]

Marco,

I did resolve my issue and have since expanded its functionality. If you like post your code where you have issues and i will help if i an and we can atleast get you going in the right direction

[clausowitz]

Thanks Ben,

this shows my calendar and with it the events. However if we have more then one event a day it doesn't work:

<?php
print "<link rel='stylesheet' href='style/cal.css' type='text/css'>";
error_reporting('0');

ini_set('display_errors', '0');
// Gather variables from
// user input and break them
// down for usage in our script

if(!isset($REQUEST['date'])){
$date = mktime(0, 0, 0, date('m'), date('d'), date('Y'));
} else {
$date = $REQUEST['date'];
}

$day = date('d', $date);
$month = date('m', $date);
$year = date('Y', $date);

// Get the first day of the month
$month_start = mktime(0, 0, 0, $month, 1, $year);

// Get friendly month name
$month_name = date('M', $month_start);

// Figure out which day of the week
// the month starts on.
$month_start_day = date('D', $month_start);

switch($month_start_day){
case "Sun": $offset = 0; break;
case "Mon": $offset = 1; break;
case "Tue": $offset = 2; break;
case "Wed": $offset = 3; break;
case "Thu": $offset = 4; break;
case "Fri": $offset = 5; break;
case "Sat": $offset = 6; break;
}

// determine how many days are in the last month.
if($month == 1){
$num_days_last = cal_days_in_month(0, 12, ($year -1));
} else {
$num_days_last = cal_days_in_month(0, ($month -1), $year);
}
// determine how many days are in the current month.
$num_days_current = cal_days_in_month(0, $month, $year);

// Build an array for the current days
// in the month
for($i = 1; $i <= $num_days_current; $i++){
$num_days_array[] = $i;
}

// Build an array for the number of days
// in last month
for($i = 1; $i <= $num_days_last; $i++){
$num_days_last_array[] = $i;
}

// If the $offset from the starting day of the
// week happens to be Sunday, $offset would be 0,

// so don't need an offset correction.

if($offset > 0){
$offset_correction = array_slice($num_days_last_array, -$offset, $offset);
$new_count = array_merge($offset_correction, $num_days_array);
$offset_count = count($offset_correction);
}

// The else statement is to prevent building the $offset array.
else {
$offset_count = 0;
$new_count = $num_days_array;
}

// count how many days we have with the two
// previous arrays merged together
$current_num = count($new_count);

// Since we will have 5 HTML table rows (TR)
// with 7 table data entries (TD)
// we need to fill in 35 TDs
// so, we will have to figure out
// how many days to appened to the end
// of the final array to make it 35 days.

if($current_num > 35){
$num_weeks = 6;
$outset = (42 - $current_num);
} elseif($current_num < 35){
$num_weeks = 5;
$outset = (35 - $current_num);
}
if($current_num == 35){
$num_weeks = 5;
$outset = 0;
}
// Outset Correction
for($i = 1; $i <= $outset; $i++){
$new_count[] = $i;
}

// Now let's "chunk" the $all_days array
// into weeks. Each week has 7 days
// so we will array_chunk it into 7 days.
$weeks = array_chunk($new_count, 7);

// Build Previous and Next Links
$previous_link = "<a href=\"".$_SERVER['PHP_SELF']."?date=";
if($month == 1){
$previous_link .= mktime(0, 0, 0, 12, $day, ($year -1));
} else {
$previous_link .= mktime(0, 0, 0, ($month -1), $day, $year);
}
$previous_link .= "\"><< Prev</a>";

$next_link = "<a href=\"".$_SERVER['PHP_SELF']."?date=";
if($month == 12){
$next_link .= mktime(0, 0, 0, 1, $day, ($year + 1));
} else {
$next_link .= mktime(0, 0, 0, ($month +1), $day, $year);
}
$next_link .= "\">Next >></a>";

// Build the heading portion of the calendar table
echo "<table class=\"maintable\">\n".
"<tr class='headline'>\n".
"<td colspan=\"7\">\n".
"<table align=\"center\">\n".
"<tr class='headline'>\n".
"<td colspan=\"2\" width=\"125\" align=\"left\">$previous_link</td>\n".
"<td colspan=\"3\" width=\"150\" align=\"center\">$month_name $year</td>\n".
"<td colspan=\"2\" width=\"75\" align=\"right\">$next_link</td>\n".
"</tr>\n".
"</table>\n".
"</td>\n".
"<tr class='mainrow'>\n".
"<td>S</td><td>M</td><td>T</td><td>W</td><td>T</td><td>F</td><td>S</td>\n".
"</tr>\n";

// Now we break each key of the array
// into a week and create a new table row for each
// week with the days of that week in the table data

$i = 0;
while (list(, $week) = each($weeks))
{

   echo "<tr class='mainrow'>\n"; 
   while (list(,  $d) = each($week)){ 

     if($i < $offset_count){ // cells in the row which have no date
         echo "<td class=\"nonmonthdays\">$day_link</font></td>\n"; 
     } 
     if(($i >= $offset_count) && ($i < ($num_weeks * 7) - $outset)){ 
        $datecount=mktime(0, 0, 0, $month -1, $d, $year);
		$short = '';
		$day_link = "<A href=\"javascript:popWin('editcalendar.php?ID=$datecount', 450, 620)\">$d<br><br><br><br></a>";
		$query = "SELECT * FROM bl_calender WHERE viewable<>'0'";
		$r = mysql_query ($query) or die ("Query error: ".mysql_error());
		while($row=mysql_fetch_array($r)) {//Array of records stored in $row
			if($row['dateclass']==$datecount) {
				$short = $row['short'];
				$short_wrapped = wordwrap( $short, 15,"\n" );
				$event = $row['type'];
				$day_link = "<A href=\"javascript:popWin('editcalendar.php?ID=$datecount', 450,  620)\" title=\"$short\"><b>$d</b><br><font color='black'>$short_wrapped</font></a>";}  // show the dates with an event with yellow background

}

	   $this_month = date('M');	
	// if $date == date of today
	if($date == mktime(0, 0, 0, $month, $d, $year)){                                       

			// make date red if it is current month
			if($this_month == $month_name) {
			   if (strpos($day_link, $short_wrapped) <> 0) {
		   echo "<td class='$event'>$day_link</td>\n";
					  } else {
			   echo "<td><A href=\"javascript:popWin('editcalendar.php?ID=$datecount', 450,  620)\" title=\"$short\">
			   <font color='red'><b>$d</b><br><br><br><br></font></a></td>\n"; 
					  }


			   } 
			   else { 
			   if (strpos($day_link, $short_wrapped) <> 0) {
		   echo "<td class='$event'>$day_link</td>\n";
					  } else {				   
			   echo "<td><A href=\"javascript:popWin('editcalendar.php?ID=$datecount', 450,  620)\" title=\"$short\"><font style='color:#0066FF'>$d<br><br><br><br></font></a></td>\n"; }
			   }
	}

	  else { // if $date <> date of today

			// if there is an event make background yellow
		   if (strpos($day_link, $short_wrapped) <> 0) {
		   echo "<td class='$event'>$day_link</td>\n";
					  } else { echo "<td class=\"days\">$day_link</td>\n";   }

}

    } elseif(($outset > 0)) { 
        if(($i >= ($num_weeks * 7) - $outset)){ 
           $day_link = ""; 
           echo "<td class=\"nonmonthdays\">$day_link</td>\n"; 
       } 
    } 
    $i++; 
  } 
  echo "</tr>\n";    

}

// Close out your table and that's it!
echo "<tr class='headline'><td colspan='7' class='days'>&nbsp;</td></tr>";
echo '</table>';

?>
<SCRIPT LANGUAGE="Javascript">
//<!--
// pop a windoid (Pictures)
function popWin(url, w, h)
{
var madURL = url;
var x, y, winStr;
x=0; y=0;
self.name="opener";
winStr = "height="+h+", width="+w+", screenX="+x+", left="+x+", screenY="+y+", top="+y+", channelmode=0, dependent=0, directories=0, fullscreen=0, location=0, menubar=0, resizable=1, scrollbars=0, status=0, toolbar=0";
lilBaby = window.open(madURL, "_blank", winStr);
}
//--> </script>