Solved

Multiple Day Event on calendar

Posted on 2009-07-05
9
570 Views
Last Modified: 2012-05-07
hello,
I have a php/mysql event calendar.  However at the moment it only shows the first day of the event and then doesn't do anything else.
I'm posting my code below in case anyone wants to make a stab at making it show more than one day for an event.
(here is my db rows)
INSERT INTO `calendar` VALUES(1, 1, 1, NULL, NULL, NULL, 'test', NULL, '2009-06-23', '2009-06-23', '16:00:00', '23:00:00', 'this si a test', 'DGBC', 2);
INSERT INTO `calendar` VALUES(2, 1, NULL, NULL, NULL, NULL, 'Vacation', NULL, '2009-07-02', '2009-07-07', '00:00:00', '01:00:00', 'Vacation in San Diego', 'San Diego', 2);

<?php
 

class calendar

{

    var $time, $today, $current_month, $current_year, $current_month_text, $total_days_of_current_month, $events, $row_event, $first_day_of_month, $day, $first_w_of_month;

    var $total_rows,$next_month,$next_month_text,$previous_month,$previous_month_text,$next_year,$next_year_text,$previous_year,$previous_year_text;

    function __construct($site_id,$ministry_id='') {

        

        $db = new db();

        $this->time = time();

        

        $this->today = date("Y/n/j", time());

        

        $this->current_month = date("n", $this->time);

        

        $this->current_year = date("Y", $this->time);

        

        $this->current_month_text = date("F Y", $this->time);

        

        $this->total_days_of_current_month = date("t", $this->time);

        $this->events = array();

        //query the database for events between the first date of the month and the last of date of month

        $sql = "SELECT DATE_FORMAT(start_date,'%e') AS day,content,title FROM calendar WHERE start_date BETWEEN  '$this->current_year/$this->current_month/01' AND '$this->current_year/$this->current_month/$this->total_days_of_current_month' AND site_id = '". $site_id ."'";

	if (strlen($ministry_id) > 0) { $sql .= " AND ministry_id = '". $ministry_id ."'"; }

        $records = $db->select_assoc($sql);

	if ($records !== "No Rows") {

	    foreach($records as $row_event)

	    {

		    //loading the $events array with evenTitle and eventContent inside the <span> and <li>. We will add then inside <ul> in the calender

		    $this->events[$row_event['day']] = "";

		    $this->events[$row_event['day']] .= '<li><span class="title">'.stripslashes($row_event['title']).'</span><span class="desc">'.stripslashes($row_event['content']).'</span></li>';

	    }

	}
 

        $this->first_day_of_month = mktime(0,0,0,$this->current_month,1,$this->current_year);

        

        //geting Numeric representation of the day of the week for first day of the month. 0 (for Sunday) through 6 (for Saturday).

        $this->first_w_of_month = date("w", $this->first_day_of_month);

        

        //how many rows will be in the calendar to show the dates

        $this->total_rows = ceil(($this->total_days_of_current_month + $this->first_w_of_month)/7);

        

        //trick to show empty cell in the first row if the month doesn't start from Sunday

        $this->day = -$this->first_w_of_month;
 

        

        $this->next_month = mktime(0,0,0,$this->current_month+1,1,$this->current_year);

        $this->next_month_text = date("F \'y", $this->next_month);

        

        $this->previous_month = mktime(0,0,0,$this->current_month-1,1,$this->current_year);

        $this->previous_month_text = date("F \'y", $this->previous_month);

        

        $this->next_year = mktime(0,0,0,$this->current_month,1,$this->current_year+1);

        $this->next_year_text = date("F \'y", $this->next_year);

        

        $this->previous_year = mktime(0,0,0,$this->current_month,1,$this->current_year-1);

        $this->previous_year_text = date("F \'y", $this->previous_year);

    }

    function display() {

        $content = "<h2>".$this->current_month_text."</h2>";

	$content .= "<table cellspacing=\"0\">

		<thead>

		<tr>

			<th>Sun</th>

			<th>Mon</th>

			<th>Tue</th>

			<th>Wed</th>

			<th>Thu</th>

			<th>Fri</th>

			<th>Sat</th>

		</tr>

		</thead>

		<tr>";

			for($i=0; $i< $this->total_rows; $i++)

			{

				for($j=0; $j<7;$j++)

				{

					$this->day++;					

					

					if($this->day>0 && $this->day<=$this->total_days_of_current_month)

					{

						//YYYY-MM-DD date format

						$date_form = "$this->current_year/$this->current_month/$this->day";

						

						$content .= '<td';

						

						//check if the date is today

						if($date_form == $this->today)

						{

							$content .= ' class="today"';

						}

						

						//check if any event stored for the date

						if(array_key_exists($this->day,$this->events))

						{

							//adding the date_has_event class to the <td> and close it

							$content .= ' class="date_has_event">'.$this->day;

							

							//adding the eventTitle and eventContent wrapped inside <span> & <li> to <ul>

							$content .= '<div class="events"><ul>'.$this->events[$this->day].'</ul></div>';

						}

						else 

						{

							//if there is not event on that date then just close the <td> tag

							$content .= '> '.$this->day;

						}

						

						$content .= "</td>";

					}

					else 

					{

						//showing empty cells in the first and last row

						$content .= '<td class="padding">&nbsp;</td>';

					}

				}

				$content .= "</tr><tr>";

			}

		$content .= "</tr>

		<tfoot>		

			<th>";

				$content .= "<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->previous_year."\" title=\"".$this->previous_year_text."\">&laquo;&laquo;</a>";

			$content .="</th>

			<th>";

				$content .="<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->previous_month."\" title=\"".$this->previous_month_text."\">&laquo;</a>";

			$content .= "</th>

			<th>&nbsp;</th>

			<th>&nbsp;</th>

			<th>&nbsp;</th>

			<th>";

				$content .= "<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->next_month."\" title=\"".$this->next_month_text."\">&raquo;</a>";

			$content .="</th>

			<th>";

				$content .="<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->next_year."\" title=\"".$this->next_year_text."\">&raquo;&raquo;</a>";

			$content .="</th>

		</tfoot>

	</table>";

        return $content;

    }

}

?>

Open in new window

0
Comment
Question by:casit
  • 5
  • 4
9 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Please post the data base schema or the create table statement.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
OK, after looking over the class I will tell you how I handle this in my calendar.  

I use a query something like this to find events that are happening on a given day (pidgin SQL):

SELECT * FROM calendar WHERE \"$today\" BETWEEN start AND end

You can also do this with some PHP programming.  The key is to know the start and end dates for each calendar item.  I can see that you are selecting the start date, so a step in the right direction might be to select the end date, too.
0
 

Author Comment

by:casit
Comment Utility
Do you use that sql statement for each day of the month in the calendar?

CREATE TABLE `calendar` (

  `calendar_id` int(11) NOT NULL auto_increment,

  `site_id` int(11) default NULL,

  `creator_id` int(11) default NULL,

  `created` date default NULL,

  `last_modified_id` int(11) default NULL,

  `last_modified` date default NULL,

  `title` varchar(100) default NULL,

  `visible` smallint(6) default NULL,

  `start_date` date default NULL,

  `end_date` date default NULL,

  `start_time` time NOT NULL,

  `end_time` time NOT NULL,

  `content` text NOT NULL,

  `location` varchar(255) NOT NULL,

  `ministry_id` int(11) NOT NULL,

  PRIMARY KEY  (`calendar_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

Open in new window

0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
"sql statement for each day" - No, after looking back at my code I see that I do only one query, then I put the data into an array that has one element for each day of the calendar.  I use the array to decide whether to highlight the calendar day with a link to the daily information page.  Event count = 0, no highlight.

Our DB schemas look very much alike, however I have the start date and time together in a single field.

Hope that helps, ~Ray


// A CALENDAR FOR THE NEXT "$calendar_count" DAYS

$present = date("Y-m-d H:i");

$future  = date("Y-m-d H:i", strtotime("$calendar_count days"));
 

// QUERY TO GET EVERYTHING GOING ON DURING THIS TIME PERIOD

$sql =  "SELECT * FROM $calendar_table WHERE approved = \"Y\" ";

$sql .= "AND ( start >= \"$present\" OR end >= \"$present\" ) ";

$sql .= "AND ( start <= \"$future\" ) ";

$sql .= "ORDER BY start ";

if (!$result = mysql_query("$sql", $db_connection)) { fatal_error($sql); }
 

// SET UP AN ARRAY OF DAYS WITH ACTIVITY

$caldata = array();

while ($row = mysql_fetch_assoc($result)

{

// EXTRACT ISO8601 DATE FROM DATETIME STRINGS

    $start = date('Y-m-d', strtotime($row["start"]);

    $end   = date('Y-m-d', strtotime($row["end"]);

// SET A POINTER IN THE ARRAY TO SHOW DAYS WITH ACTIVITY

    while ($start <= $end)

    {

        $caldata[$start] = TRUE;

        $start = date('Y-m-d', strtotime("$start plus 1 day");

    }

}

Open in new window

0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:casit
Comment Utility
ok I think I understand what you do.
However what do you do for in between the days of start adn end?
So say I have an event that starts on Mon and ends on Fri.
Currently my system will only show link on Mon.
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Right, I understand that part of the issue.  If you look at the "while" iterator above on line 20-24 it sets an element in the caldata array to TRUE for every day that has an event or part of an event.  When I display the monthly calendar, I display a link to the daily calendar for every day that is TRUE in the caldata array.

In the daily calendar script, I use the date from the GET argument to find all the events that are happening on that day.

Does that help? ~Ray
0
 

Author Comment

by:casit
Comment Utility
ah so first you are looping through records from db. then you loop through caldata.
any ideas on how I can switch mine to be like yours?
I will give you the points now though.  You have helped me out quiet a bit
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Thanks for the points - it's a good question, for sure.  

Not sure about this, but...  

I think the SELECT statement in the post above might be modified to be aware of the "end" date of the event.  It looks like it is only selecting the start dates.

Best regards, ~Ray
0
 

Author Comment

by:casit
Comment Utility
Just in case anyone wants the code to calendar class that displays multiple events per day and events over a one day period it will show it for each day.
(still working on class so maybe bugs but it can at least get you on right track)

<?php
 

class calendar

{

    var $db, $time, $today, $current_month, $current_year, $current_month_text, $total_days_of_current_month, $events, $row_event, $first_day_of_month, $day, $first_w_of_month;

    var $total_rows,$next_month,$next_month_text,$previous_month,$previous_month_text,$next_year,$next_year_text,$previous_year,$previous_year_text;

    function __construct($site_id,$ministry_id='',$logged_in_user = '') {

		$this->site_id = $site_id;

		$this->ministry_id = $ministry_id;

		$this->db = new db();

		$this->logged_in_user = $logged_in_user;

    }

    

    /**

    *

    * function to insert calendar entry

    *

    * @param string title of calendar

    * @param int visible calendar entry to public

    * @param date start_date of calendar entry

    * @param date end_date of calendar entry

    * @param time start_time in military time for start time of event

    * @param time end_time in military time for end time of event

    * @param string content of calendar entry

    * @param string location of calendar entry

    */

    public function add_calendar_entry($title,$visible,$start_date,$end_date,$start_time,$end_time,$content,$location)

    {

    	$insert_values = array('event_id'=>'',

		'site_id'=>''.$this->site_id.'',

    	'creator_id'=>''.$this->logged_in_user.'',

    	'created'=>''.date("Y-m-d").'',

    	'last_modified_id'=>''.$this->logged_in_user.'',

    	'last_modified'=>''.date("Y-m-d").'',

    	'title'=>''.$title.'',

    	'visible'=>''.$visible.'',

    	'start_date'=>''.$start_date.'',

    	'end_date'=>''.$end_date.'',

    	'start_time'=>''.$start_time.'',

    	'end_time'=>''.$end_time.'',

    	'content'=>''.$content.'',

    	'location'=>''.$location.'',

    	'ministry_id'=>''.$this->ministry_id.'');

    	$this->db->insert_array('calendar',$insert_values);

    	add_event_log($this->site_id,$this->logged_in_user,$module_id,'Insert','Calendar Event has been added::Name:'.$title.'');	

    }

    

    /**

    *

    * function to update calendar entry

    *

    * @param string title of calendar

    * @param int visible calendar entry to public

    * @param date start_date of calendar entry

    * @param date end_date of calendar entry

    * @param time start_time in military time for start time of event

    * @param time end_time in military time for end time of event

    * @param string content of calendar entry

    * @param string location of calendar entry

    */

    public function edit_calendar_entry($title,$visible,$start_date,$end_date,$start_time,$end_time,$content,$location)

    {

    	$update_values = array('last_modified_id'=>''.$this->logged_in_user.'',

    	'last_modified'=>''.date("Y-m-d").'',

    	'title'=>''.$title.'',

    	'visible'=>''.$visible.'',

    	'start_date'=>''.$start_date.'',

    	'end_date'=>''.$end_date.'',

    	'start_time'=>''.$start_time.'',

    	'end_time'=>''.$end_time.'',

    	'content'=>''.$content.'',

    	'location'=>''.$location.'',

    	'ministry_id'=>''.$this->ministry_id.'');

    	$this->db->update_array('calendar_events', 'event_id', $event_id, $update_values);

    	add_event_log($this->site_id,$this->logged_in_user,$module_id,'Update','Calendar Event has been updated::Name:'.$title.'');	

    }

    

    /**

    *

    * function to delete calendar entry

    * 

    * @param int entry_id of calendar entry

    */

    public function delete_calendar_entry($entry_id)

    {

    	$record = $this->db->select_array("SELECT title from calendar_events WHERE event_id = '$event_id'");

    	$this->db->delete_row('calendar_events', 'event_id', $event_id);

    	add_event_log($this->site_id,$this->logged_in_user,$module_id,'Delete','Calendar Event has been deleted::Name:'.$record['title'].'');	

    }

    /**

    *

    * Function to display calendar for given ministry and site

    *

    * @return string of html calendar

    */

    public function display() {

        $this->time = time();

        

        $this->today = date("Y/n/j", time());

        

        $this->current_month = date("n", $this->time);

        

        $this->current_year = date("Y", $this->time);

        

        $this->current_month_text = date("F Y", $this->time);

        

        $this->total_days_of_current_month = date("t", $this->time);

        $this->events = array();

        //query the database for events between the first date of the month and the last of date of month

        $sql = "SELECT DATE_FORMAT(start_date,'%e') AS day,content,title,event_id,start_date, end_date FROM calendar_events WHERE start_date BETWEEN  '$this->current_year/$this->current_month/01' AND '$this->current_year/$this->current_month/$this->total_days_of_current_month' AND site_id = '". $this->site_id ."'";

	if (strlen($this->ministry_id) > 0) { $sql .= " AND ministry_id = '". $this->ministry_id ."'"; }

		$sql .= " ORDER BY start_date";

        $records = $this->db->select_assoc($sql);

	if ($records !== "No Rows") {

	    foreach($records as $row_event)

	    {

			while ($row_event['start_date'] < $row_event['end_date'] || $row_event['start_date'] == $row_event['end_date'])

			{

			$row_event['start_date'] = date('Y-m-d', strtotime(''.$row_event['start_date'].' + 1 day'));

		    //loading the $events array with evenTitle and eventContent inside the <span> and <li>. We will add then inside <ul> in the calender

			if (ISSET($this->events[$row_event['day']]) && strlen($this->events[$row_event['day']]) >1) {

				$this->events[$row_event['day']] .= '<li><a href="popup.php?event_id='.$row_event['event_id'].'" rel="popup"><span class="title">'.$row_event['title'].'</span></a></li>';

			} else {

				$this->events[$row_event['day']] = "";

				$this->events[$row_event['day']] = '<li><a href="popup.php?event_id='.$row_event['event_id'].'" rel="popup"><span class="title">'.$row_event['title'].'</span></a></li>';

			}

		    //<span class="desc">'.stripslashes($row_event['content']).'</span>

			$row_event['day'] = $row_event['day']+1;

			}

	    }

	}
 

        $this->first_day_of_month = mktime(0,0,0,$this->current_month,1,$this->current_year);

        

        //geting Numeric representation of the day of the week for first day of the month. 0 (for Sunday) through 6 (for Saturday).

        $this->first_w_of_month = date("w", $this->first_day_of_month);

        

        //how many rows will be in the calendar to show the dates

        $this->total_rows = ceil(($this->total_days_of_current_month + $this->first_w_of_month)/7);

        

        //trick to show empty cell in the first row if the month doesn't start from Sunday

        $this->day = -$this->first_w_of_month;
 

        

        $this->next_month = mktime(0,0,0,$this->current_month+1,1,$this->current_year);

        $this->next_month_text = date("F \'y", $this->next_month);

        

        $this->previous_month = mktime(0,0,0,$this->current_month-1,1,$this->current_year);

        $this->previous_month_text = date("F \'y", $this->previous_month);

        

        $this->next_year = mktime(0,0,0,$this->current_month,1,$this->current_year+1);

        $this->next_year_text = date("F \'y", $this->next_year);

        

        $this->previous_year = mktime(0,0,0,$this->current_month,1,$this->current_year-1);

        $this->previous_year_text = date("F \'y", $this->previous_year);
 

        $content = "<h2>".$this->current_month_text."</h2>";

	$content .= "<table cellspacing=\"0\">

		<thead>

		<tr>

			<th>Sun</th>

			<th>Mon</th>

			<th>Tue</th>

			<th>Wed</th>

			<th>Thu</th>

			<th>Fri</th>

			<th>Sat</th>

		</tr>

		</thead>

		<tr>";

			for($i=0; $i< $this->total_rows; $i++)

			{

				for($j=0; $j<7;$j++)

				{

					$this->day++;					

					

					if($this->day>0 && $this->day<=$this->total_days_of_current_month)

					{

						//YYYY-MM-DD date format

						$date_form = "$this->current_year/$this->current_month/$this->day";

						

						$content .= '<td';

						

						//check if the date is today

						if($date_form == $this->today)

						{

							$content .= ' class="today"';

						}

						

						//check if any event stored for the date

						if(array_key_exists($this->day,$this->events))

						{

							//adding the date_has_event class to the <td> and close it

							$content .= ' class="date_has_event">'.$this->day;

							

							//adding the eventTitle and eventContent wrapped inside <span> & <li> to <ul>

							$content .= '<div class="events"><ul>'.$this->events[$this->day].'</ul></div>';

						}

						else 

						{

							//if there is not event on that date then just close the <td> tag

							$content .= '> '.$this->day;

						}

						

						$content .= "</td>";

					}

					else 

					{

						//showing empty cells in the first and last row

						$content .= '<td class="padding">&nbsp;</td>';

					}

				}

				$content .= "</tr><tr>";

			}

		$content .= "</tr>

		<tfoot>		

			<th>";

				$content .= "<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->previous_year."\" title=\"".$this->previous_year_text."\">&laquo;&laquo;</a>";

			$content .="</th>

			<th>";

				$content .="<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->previous_month."\" title=\"".$this->previous_month_text."\">&laquo;</a>";

			$content .= "</th>

			<th>&nbsp;</th>

			<th>&nbsp;</th>

			<th>&nbsp;</th>

			<th>";

				$content .= "<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->next_month."\" title=\"".$this->next_month_text."\">&raquo;</a>";

			$content .="</th>

			<th>";

				$content .="<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->next_year."\" title=\"".$this->next_year_text."\">&raquo;&raquo;</a>";

			$content .="</th>

		</tfoot>

	</table>";

        return $content;

    }

}

?>

Open in new window

0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now