casit
asked on
Multiple Day Event on calendar
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);
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"> </td>';
}
}
$content .= "</tr><tr>";
}
$content .= "</tr>
<tfoot>
<th>";
$content .= "<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->previous_year."\" title=\"".$this->previous_year_text."\">««</a>";
$content .="</th>
<th>";
$content .="<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->previous_month."\" title=\"".$this->previous_month_text."\">«</a>";
$content .= "</th>
<th> </th>
<th> </th>
<th> </th>
<th>";
$content .= "<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->next_month."\" title=\"".$this->next_month_text."\">»</a>";
$content .="</th>
<th>";
$content .="<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->next_year."\" title=\"".$this->next_year_text."\">»»</a>";
$content .="</th>
</tfoot>
</table>";
return $content;
}
}
?>
Please post the data base schema or the create table statement.
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.
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.
ASKER
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 ;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
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
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
ASKER
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
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
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
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
ASKER
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)
(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"> </td>';
}
}
$content .= "</tr><tr>";
}
$content .= "</tr>
<tfoot>
<th>";
$content .= "<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->previous_year."\" title=\"".$this->previous_year_text."\">««</a>";
$content .="</th>
<th>";
$content .="<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->previous_month."\" title=\"".$this->previous_month_text."\">«</a>";
$content .= "</th>
<th> </th>
<th> </th>
<th> </th>
<th>";
$content .= "<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->next_month."\" title=\"".$this->next_month_text."\">»</a>";
$content .="</th>
<th>";
$content .="<a href=\"".$_SERVER['PHP_SELF']."?time=".$this->next_year."\" title=\"".$this->next_year_text."\">»»</a>";
$content .="</th>
</tfoot>
</table>";
return $content;
}
}
?>