asked on

Add recurring events to database

I have a very simple events calendar on a website. The user can add their own events. They now want to do recurring events - My questions are:
1) If an event is yearly or monthly - do you add a new row to the database for each event?
2) Any code out there to help adjust this code to allow this feature?

<?php

if (!(isset($_GET['id'])))
{
	echo "Invalid Request<br>\n";
	exit;
}
else
{
	$event_id = $_GET['id'];
}

$source_id = "";



//Database Information

include('includes/db_login.inc');

//Connect to database

$link = mysql_connect ( $dbhost, $dbuser, $dbpass);

if (!is_resource($link))
{
	die("Could not connect: " . mysql_error());
}

mysql_select_db($dbname) or die(mysql_error());

if ($event_id != "new")
{
	$sql = "SELECT * FROM Events WHERE id='$event_id'";
	$sql_query = mysql_query($sql);
	$event_row = mysql_fetch_assoc($sql_query);
}
elseif (isset($_GET['srcid']))
{
	$source_id = $_GET['srcid'];

	$sql = "SELECT * FROM Events WHERE id='$source_id'";
	$sql_query = mysql_query($sql);
	$event_row = mysql_fetch_assoc($sql_query);
}

if (isset($_POST['action']))
{
	if ($_POST['action'] == "update")
	{
		$EventName = $_POST['event_name'];
		mysql_real_escape_string($EventName, $link);
		
		$EventStartDate = $_POST['event_startdate'];
		mysql_real_escape_string($EventStartDate, $link);

		$EventEndDate = $_POST['event_enddate'];
		mysql_real_escape_string($EventEndDate, $link);

		$EventDisplayDate = $_POST['event_display_date'];
		mysql_real_escape_string($EventDisplayDate, $link);
		
		$EventMisc = $_POST['event_misc'];
		mysql_real_escape_string($EventMisc, $link);

		$EventDescription = $_POST['FCKeditor1'];


		$currenttime = date('Y-m-d H:i:s');

		if ($event_id == "new")
		{
			$sql = "INSERT INTO Events (Event, EndDate, StartDate, DisplayDate, MiscInfo, Description, DateAdded, DateUpdated) VALUES ('$EventName', '$EventEndDate', '$EventStartDate', '$EventDisplayDate', '$EventMisc', '$EventDescription', '$currenttime', '$currenttime')";
			$insert_result = mysql_query($sql);

			if (!($insert_result))
			{
				echo mysql_error();
				echo "<br>\n";
				echo "$sql<br>\n";
				exit;
			}

			// Grab the new ID
			$event_id = mysql_insert_id($link);
		}
		else
		{
			$old_approved_status = 1;
			
			$sql = "SELECT * FROM Events WHERE ID='$event_id'";
			$sql_result = mysql_query($sql);
			
			if ($event_row = mysql_fetch_assoc($sql_result))
			{
				$old_approval_status = $event_row['Approved'];
			}
			
			$sql = "UPDATE Events SET Event='$EventName', EndDate='$EventEndDate', StartDate='$EventStartDate', DisplayDate='$EventDisplayDate',  MiscInfo='$EventMisc', Description='$EventDescription', DateUpdated='$currenttime' WHERE ID='$event_id'";
			$update_result = mysql_query($sql);

			

						
		}
	}


	
	header("Location: manage_events.php");
}

?><HTML>
<HEAD>

<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
<SCRIPT LANGUAGE="JavaScript" SRC="../js/date.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript" SRC="../js/AnchorPosition.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript" SRC="../js/PopupWindow.js"></SCRIPT>
 <SCRIPT LANGUAGE="JavaScript" SRC="../js/CalendarPopup.js"></SCRIPT> 
 <SCRIPT LANGUAGE="JavaScript">
	var cal = new CalendarPopup();
</SCRIPT>

</HEAD>
<BODY BGCOLOR=#FFFFFF LEFTMARGIN=0 TOPMARGIN=0 MARGINWIDTH=0 MARGINHEIGHT=0>
<div align="center">
  <!-- ImageReady Slices (CityofRCweb.psd) -->
 
          <?php
		echo "<form action=\"edit_event.php?id=$event_id\" enctype=\"multipart/form-data\" method=\"post\" name=\"update_form\">\n";
		echo "<input type=\"hidden\" name=\"action\" value=\"update\" />\n";
		echo "<table>\n";

		if ($event_id != "new" || (!(empty($source_id))))
		{
			$EventName = $event_row['Event'];
			$EventStartDate = $event_row['StartDate'];
			$EventEndDate = $event_row['EndDate'];
			$EventDisplayDate = $event_row['DisplayDate'];
 			$EventMisc = $event_row['MiscInfo'];
			$EventDescription = $event_row['Description'];
		}
		else
		{
			$EventName = '';
			$EventStartDate = '';
			$EventEndDate = '';
			$EventDisplayDate = '';
			$EventMisc = '';
			$EventDescription = '';
		}




	
		echo "Event Name:\n";
	
		echo "<input type=\"text\" name=\"event_name\" value=\"$EventName\" size=\"40\" />\n";

	

		echo "<p>Start Date:\n";

		echo "<input type=\"text\" name=\"event_startdate\" value=\"$EventStartDate\">\n";
		echo "<A HREF=\"#\" onClick=\"cal.select(document.forms['update_form'].event_startdate, 'anchor1', 'yyyy/MM/dd'); return false;\" NAME=\"anchor1\" ID=\"anchor1\"><img src=\"../images/b_calendar.png\" border=\"0\" / ></A>\n";

		echo "<p>End Date:\n";

		echo "<input type=\"text\" name=\"event_enddate\" value=\"$EventEndDate\">\n";
		echo "<A HREF=\"#\" onClick=\"cal.select(document.forms['update_form'].event_enddate, 'anchor1', 'yyyy/MM/dd'); return false;\" NAME=\"anchor1\" ID=\"anchor1\"><img src=\"../images/b_calendar.png\" border=\"0\" / ></A>\n";


		echo "<p>Date Display Information:\n";

		echo "<input type=\"text\" name=\"event_displaydate\" value=\"$EventDisplayDate\" size=\"30\" />\n";

		echo "<p>Other Info:\n";

		echo "<input type=\"text\" name=\"event_misc\" value=\"$EventMisc\" size=\"40\" />\n";
	
		echo "<p>Description:\n";

		$oFCKeditor = new FCKeditor('FCKeditor1');
		$oFCKeditor->BasePath = '../fckeditor/';
		$oFCKeditor->Value = $EventDescription;
		$oFCKeditor->ToolbarSet = 'Default';
		$oFCKeditor->Width  = '600';
		$oFCKeditor->Height = '400';
		$oFCKeditor->Create();


		echo "<input type=\"submit\" value=\"Save\" />\n";

?>
              </form>
         
  <!-- End ImageReady Slices -->
</div>
</BODY>
</HTML>

<?php
mysql_close();
?>

Open in new window

betopa

I recommend to use another one or two fields in the table, not multiple rows for each "recurring event". You could use a "number_events" field and a "periodicity" field (daily, weekly, etc...). And solve the "repetitions" with this two values. You already have a startdate and enddate fields.

katlees

ASKER

What would the number_events field be for? If it is a repetitive event this would be true and the periodicity field would show how often it repeats?

betopa

Sorroy. You are right.
This field is useless.

You already have the begining and end of the event.
And with the new field "periodicity" (you just need this one) you can make the event to repeat as long as it reaches the end date. You can know if the event repeats itself or not, and how often, with the options of your "periodicity" field.

You could show to the user a combo box with:
JUST ONCE
DAILY
WEEKLY... ETC

This way you know.

katlees

ASKER

That makes sense.. what if they wanted to do the 2nd tuesday of a month?

ASKER CERTIFIED SOLUTION

betopa

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

katlees

ASKER

Thanks.. I'll try and code in the morning and see if I have questions.

betopa

Sure.
Here are some good (different ways) calendar classes:

http://style-vs-substance.com/code/calendar-class-php/
http://code.google.com/p/php-calendar-class/
http://davidwalsh.name/php-calendar
http://www.dreamincode.net/code/snippet3750.htm

Hope they help.

katlees

ASKER

Thanks.. my calendar is built and working fine, just trying to simplify adding numerous events to the database for display

crazedsanity

One reason why you may want to (conditionally) add entries to the database is for exceptions. For instance, if the user wants an event to occur every Monday for an entire year, but wants to skip the event on May 16th, that would have to be noted.

I would have the system create a row every time an event occurred, marking that it had occurred; if a user cancels the event for a specific day, the row could be added & marked as canceled, so the system would skip over it when it found a row existing (marked as canceled).

katlees

ASKER

crazedsanity - that makes sense - how would you get it to add all the records to the database?

crazedsanity

I would think it would be more of a hybrid approach: the system would have to know that future events are (for the most part) derived, meaning that "every monday until Jan1 2014" would have events not explicitly listed in the database. Users can cancel future events, which would cause the system to create a record in the database for that date & list it as canceled.

There would have to be a main record that stores what the plan is. Past events would link to that (as a way of indicating that the event occurred), and so would future occurrences that were altered. The process of handling canceled events can be a tricky one, especially if that main record (the original schedule) is altered; in that event, it may be easiest just to delete all associated future events.

The system can be as simple or complex as you want. Let me know if you need more information.

betopa

If you make the decision of adding records to the database, for each repeated event, the first thing you have to realize is the fact that you will have to use more disk space, and this is not the best way to go, but it does is the easiest.

For this, I recomend to make another small table. In your "Events" table, you have an unique id field. You can make a "recurring" table with its own id, an "id_event" field, a "date" field and a "status" field, where you will insert every date of the event. If the event will happen just once, you will insert just once, a record with the id of that event, the single date, and the proper status (1-active, 0-cancelled). If the user wants repetitions, you have to insert a row for each one, and every row will have its own date, which have to be between the beginning and end date of the event...

crazedsanity

@betopa: when going down the path of inserting a record for every event, the capability of having an event that repeats forever is no longer possible (it could be, I suppose, but the process that creates those future events would run until no disk space was left).