Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 989
  • Last Modified:

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

0
katlees
Asked:
katlees
  • 5
  • 5
  • 3
1 Solution
 
betopaCommented:
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.
0
 
katleesAuthor Commented:
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?
0
 
betopaCommented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
katleesAuthor Commented:
That makes sense.. what if they wanted to do the 2nd tuesday of a month?
0
 
betopaCommented:
You can make it as complex - simple as you can - want. It could have more options for the users, but you will need more clever solutions.

For this, besides the options "daily, weekly, etc", you can add the option "choose the days" at the "periodicity" combo, but with this, you have to add to a "day selection" calendar where you ask the user to add the exact dates that he or she... wants. For this, you do need another field, for storing the dates. It can be a longtext field, where you can add each date separated by a ",", and then you can make an array from it with the explode function.

The way to make the "dates selection" can be as complex or simple as you can too. A very simple way, is adding (just for the "choose the days" combo option) three other <select> combos, one with the selection of the year, another with the month and another with the day, and an "ok" button, and every time they add a day, you show al the already added and stored dates. Or you could make a class "calendar" wich prints you a dynamic normal calendar, with "arrows" to change the month or a year selection... etc, that updates itself to the selection, and with links in every day, for the user to pick from there, every time they press the "ok" button or they press a link in the calendar, you need to code all the limits for the user "he cannot add two same dates, he cannot add a date outside the beginning-end range... etc".
0
 
katleesAuthor Commented:
Thanks.. I'll try and code in the morning and see if I have questions.
0
 
katleesAuthor Commented:
Thanks.. my calendar is built and working fine, just trying to simplify adding numerous events to the database for display
0
 
crazedsanityCommented:
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).
0
 
katleesAuthor Commented:
crazedsanity - that makes sense - how would you get it to add all the records to the database?
0
 
crazedsanityCommented:
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.
0
 
betopaCommented:
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...
0
 
crazedsanityCommented:
@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).  
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now