We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Multiple Day Event on calendar

casit
casit asked
on
Medium Priority
634 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

Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
Please post the data base schema or the create table statement.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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.

Author

Commented:
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

Most Valuable Expert 2011
Author of the Year 2014
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
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.
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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

Author

Commented:
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
Most Valuable Expert 2011
Author of the Year 2014

Commented:
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

Author

Commented:
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

Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.