Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Multiple Day Event on calendar

Posted on 2009-07-05
9
Medium Priority
?
602 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 111

Expert Comment

by:Ray Paseur
ID: 24780447
Please post the data base schema or the create table statement.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 24780474
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
ID: 24781457
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
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.

 
LVL 111

Accepted Solution

by:
Ray Paseur earned 2000 total points
ID: 24785142
"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
 

Author Comment

by:casit
ID: 24787697
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 111

Expert Comment

by:Ray Paseur
ID: 24805445
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
ID: 24821080
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 111

Expert Comment

by:Ray Paseur
ID: 24823113
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
ID: 24829462
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

783 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