Link to home
Create AccountLog in
Avatar of mrroy69
mrroy69

asked on

php My SQLDisplay Multiday Events in Calendar

Here is the problem...I have a multiday calendar and I wish tp display single dayevents and each day for a multiday event. each Multiday event has a start date and an end date. Single day events only have start dates.

- So a singlre day event with a start date 2011-01-03, should display on Monday
- A Multi day event with a start date of 2011-01-03 and an end date of 2011-01-05, should display on Mon, Tue and Wed

- A different multiday event with a start date of 2011-01-03 and an end date of 2011-01-07, should display on Mon, Tue, Wed, Thu and Fri


JANUARY, 2011
---------------
Sun            Mon            Tue            Wed            Thu            Fri            Sat
------------------------------------------------------------------------------------------
2                    3              4                     5                6                  7             8




Here is the Table
--------------------

CREATE TABLE `calendar` (
  `id` int(11) NOT NULL auto_increment,
  `eventID` varchar(25) NOT NULL,
  `title` text NOT NULL,
  `startdate` varchar(25) NOT NULL,
  `enddate` varchar(25) NOT NULL,
  `start_time` varchar(25) NOT NULL,
  `end_time` varchar(25) NOT NULL,
  `location` varchar(200) NOT NULL,
  `featured` varchar(5) NOT NULL,
  `website` varchar(255) NOT NULL,
  `description` longtext NOT NULL,
  `documents` text,
  PRIMARY KEY  (`id`,`featured`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=latin1;








 Here is the Code I'm using, this is where i'm stuck
 ---------------------------
 
 <?php
if (isset($_GET['month'])) { $month = $_GET['month']; $month = ereg_replace ("[[:space:]]", "", $month); $month = ereg_replace ("[[:punct:]]", "", $month); $month = ereg_replace ("[[:alpha:]]", "", $month); }
if (isset($_GET['year'])) { $year = $_GET['year']; $year = ereg_replace ("[[:space:]]", "", $year); $year = ereg_replace ("[[:punct:]]", "", $year); $year = ereg_replace ("[[:alpha:]]", "", $year); if ($year < 2010) { $year = 2010; } if ($year > 2035) { $year = 2035; } }
if (isset($_GET['today'])) { $today = $_GET['today']; $today = ereg_replace ("[[:space:]]", "", $today); $today = ereg_replace ("[[:punct:]]", "", $today); $today = ereg_replace ("[[:alpha:]]", "", $today); }


$month = (isset($month)) ? $month : date("n",time());
$year = (isset($year)) ? $year : date("Y",time());


//  FUNCTION
function dates_inbetween($mydate1, $mydate2){

    $myday = 60*60*24;

    $mydate1 = strtotime($mydate1);
    $mydate2 = strtotime($mydate2);

    $mydays_diff = round(($mydate2 - $mydate1)/$myday); // Unix time difference devided by 1 day to get total days in between

    $mydates_array = array();

    $mydates_array[] = date('Y-m-d',$mydate1);
   
    for($x = 1; $x < $mydays_diff; $x++){
        $mydates_array[] = date('Y-m-d',($mydate1+($myday*$x)));
    }

    $mydates_array[] = date('Y-m-d',$mydate2);

    return $mydates_array;
}
// END OF FUNCTION
/* draws a calendar */
function draw_calendar($month,$year){

include("link.php");

      /* draw table */
      $calendar = '<table cellpadding="0" cellspacing="1" width="550"bgcolor="#999999"class="calendar">';

      /* table headings */
      $headings = array('Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday');
      $calendar.= '<tr class="calendar-row"><td class="calendar-day-head">'.implode('</td><td class="calendar-day-head">',$headings).'</td></tr>';

      /* days and weeks vars now ... */
      $running_day = date('w',mktime(0,0,0,$month,1,$year));
      $days_in_month = date('t',mktime(0,0,0,$month,1,$year));
      $days_in_this_week = 1;
      $day_counter = 0;
      $dates_array = array();

      /* row for week one */
      $calendar.= '<tr class="calendar-row">';

      /* print "blank" days until the first of the current week */
      for($x = 0; $x < $running_day; $x++):
            $calendar.= '<td bgcolor="#FFFFFF"class="calendar-day"> </td>';
            $days_in_this_week++;
      endfor;

$today = date("d");
$nowmonth = date("m");
$nowyear = date("Y");            
                  for($list_day = 1; $list_day <= $days_in_month; $list_day++):
if($list_day == $today && $month == $nowmonth && $year == $nowyear) {
$calendar.= '<td bgcolor="#FFFFFF"class="calendar-day-today"valign="top">';
} else {
$calendar.= '<td bgcolor="#FFFFFF"class="calendar-day"valign="top">';
}

// Foramt the day for the calendar day
$calendar_date = mktime(0,0,0,$month,$list_day,$year);
$calendar_day="".date("Y-m-d", strtotime("+0 month", $calendar_date))."";

//////////////////////////////////////////////////
// GRAB THE SINGLE DAY EVENTS FROM THE DB /////////////////////

$GetThisEvent1 = mysqli_query($link, "SELECT * FROM calendar WHERE startdate='".$calendar_day."' AND (enddate ='' OR enddate='".$calendar_day."')");

if(mysqli_num_rows($GetThisEvent1) > 0){
while ($thisrow = mysqli_fetch_array($GetThisEvent1)) {
$thisid=$thisrow['id'];
$eventID = $thisrow['eventID'];
$title=$thisrow['title'];
$startdate=$thisrow['startdate'];
$enddate=$thisrow['enddate'];

//$start_time=$thisrow['start_time'];
//$end_time=$thisrow['end_time'];
//$location=$thisrow['location'];
//$featured=$thisrow['featured'];
//$website=$thisrow['website'];
//$description=$thisrow['description'];
//$document=$thisrow['document'];

$comments_stripped=strip_tags($title);
if (strlen($comments_stripped) > 30) {
$text1=$comments_stripped;
$chars = 20;
$text = $text1." ";
$text2 = substr($text,0,$chars);
$text3 = substr($text,0,strrpos($text2,' '));
$text4 = $text3."...";
$comments="".$text4."";
}
else
{
$comments=strip_tags($title); // Strip tags on this page only allow on detailed listing
}

$thisEvent.='<li>S - <a href="javascript:void(0)" onclick="javascript:eventTitle_click(' . $eventID . '); return false;" class="mainslinks"title="' . $title . '">' . $comments . ' </a><br />';
} // End of While
mysqli_free_result($GetThisEvent);
} // End of Num Rows
else {
$thisEvent = '';
}
// END OF GRAB THE SINGLE  DAY EVENTS HERE /////
//////////////////////////////////////////////////


// GRAB THE MULTIDAY EVENTS HERE ////////
//////////////////////////////////////////////////
//echo $calendar_day;
$GetThisEvent2 = mysqli_query($link, "SELECT * FROM calendar WHERE enddate > startedate") or die(mysqli_error());


if(mysqli_num_rows($GetThisEvent2) > 0){
echo 'YES';
while ($thisrow2 = mysqli_fetch_array($GetThisEvent2)) {
$thisid2=$thisrow2['id'];
$eventID2 = $thisrow2['eventID'];
$title2=$thisrow2['title'];


$comments_stripped22=strip_tags($title2);
if (strlen($comments_stripped22) > 30) {
$text122=$comments_stripped22;
$chars22 = 20;
$text22 = $text122." ";
$text222 = substr($text22,0,$chars22);
$text322 = substr($text22,0,strrpos($text222,' '));
$text422 = $text322."...";
$comments22="".$text422."";
}
else
{
$comments22=strip_tags($title2); // Strip tags on this page only allow on detailed listing
}

$thisEvent2='<li>M - <a href="javascript:void(0)" onclick="javascript:eventTitle_click(' . $eventID2 . '); return false;" class="mainslinks"title="' . $title2 . '">' . $comments22 . ' </a><br />';
} // End of While
mysqli_free_result($GetThisEvent2);
} // End of Num Rows
else {
$thisEvent2 = '';
}



$calendar.= '<div class="day-number"align="right">'.$list_day.'</div><br /><br />';

$calendar.= '<ul style="padding-left: 10px;margin-top: 0; margin-bottom: 0;"type="square">' . $thisEvent . $thisEvent2 . '</ul>';


                  /** QUERY THE DATABASE FOR AN ENTRY FOR THIS DAY !!  IF MATCHES FOUND, PRINT THEM !! **/
                  $calendar.= str_repeat('<p> </p>',2);
                  
            $calendar.= '</td>';
            if($running_day == 6):
                  $calendar.= '</tr>';
                  if(($day_counter+1) != $days_in_month):
                        $calendar.= '<tr class="calendar-day">';
                  endif;
                  $running_day = -1;
                  $days_in_this_week = 0;
            endif;
            $days_in_this_week++; $running_day++; $day_counter++;
      endfor;

      /* finish the rest of the days in the week */
      if($days_in_this_week < 8):
            for($x = 1; $x <= (8 - $days_in_this_week); $x++):
                                    $calendar.= '<td bgcolor="#FFFFFF"class="calendar-day-np"height="80"><br /></td>';
                  
            endfor;
      endif;

      /* final row */
      $calendar.= '</tr>';

      /* end the table */
      $calendar.= '</table>';
      
      /* all done, return result */
      return $calendar;
}


$sql_date = mktime(0,0,0,$month,01,$year);

// Get the correct year when the month is January
if($month == "01"){
$previous = date("m", strtotime("-1 month", $sql_date)) . '&year=' . date("Y", strtotime("-1 year", $sql_date));

$previous_title_month = date("F", strtotime("-1 month", $sql_date));
$previous_title_year = date("Y", strtotime("-1 year", $sql_date));

} else {
$previous = date("m", strtotime("-1 month", $sql_date)) . '&year=' . $year;

$previous_title_month = date("F", strtotime("-1 month", $sql_date));
$previous_title_year = $year;
}

// Get the correct year when the month is december
if($month == "12"){
$next = date("m", strtotime("+1 month", $sql_date)) . '&year=' . date("Y", strtotime("+1 year", $sql_date));
// For Title Links
$next_title_month = date("F", strtotime("+1 month", $sql_date));
$next_title_year = date("Y", strtotime("+1 year", $sql_date));

} else {
$next = date("m", strtotime("+1 month", $sql_date)) . '&year=' . $year;
// For Title Links
$next_title_month = date("F", strtotime("+1 month", $sql_date));
$next_title_year = $year;
}

echo '<table cellpadding="5" cellspacing="0" width="550">
<tr>
<td width="33%"align="left"><h2><a href="?month=' . $previous . '"title="' . $previous_title_month . ' ' . $previous_title_year . '"><< Previous</a></h2></td>
<td width="33%"align="center"><h2>' . date("F", strtotime("+0 month", $sql_date)) . ' ' . date("Y", strtotime("+0 month", $sql_date)) . '</h2></td>
<td width="33%"align="right"><h2><a href="?month=' . $next . '"title="' . $next_title_month . ' ' . $next_title_year . '">>> Next</a></h2></td>
</tr>
</table>';
echo draw_calendar($month,$year);
?>
ASKER CERTIFIED SOLUTION
Avatar of mankowitz
mankowitz
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of mrroy69
mrroy69

ASKER

Thanks, well I finally got it at 3:00AM. I decided to create a separate table that will also have all of the date ranges between a start date and an end date:

CREATE TABLE `mirthe_calendar_dates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `eventID` varchar(25) NOT NULL,
  `title` varchar(255) NOT NULL,
  `date` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

I then revised the calendar script to the following, which did it...Hope this helps some:

<style type="text/css">
<!--
td.calendar-day-head { background:#4A248C; font-weight:bold; text-align:center; width:120px; padding:5px; color: #FFF;
}
div.day-number{
background:#210042; padding:5px; color:#fff; font-weight:bold; float:right; margin:0px 0px 0 0; width:20px; text-align:center; font-size:12px
}

div.day-number a, a:hover, a:visited, a:visited:hover{
color:#fff;
text-decoration: none;
}

td.months{
background:#210042; padding:5px; color:#fff; font-weight:bold; text-align:center; font-size:12px;
}

td.months a, a:hover, a:visited, a:visited:hover{
color:#fff;
text-decoration: none;
}
-->
</style>

<div align="center">
<table border="0" cellpadding="0" cellspacing="0" width="550">
<tr>
<td width="550" valign="top">
<?php
//clean input
if (isset($_GET['month'])) { $month = $_GET['month']; $month = ereg_replace ("[[:space:]]", "", $month); $month = ereg_replace ("[[:punct:]]", "", $month); $month = ereg_replace ("[[:alpha:]]", "", $month); }
if (isset($_GET['year'])) { $year = $_GET['year']; $year = ereg_replace ("[[:space:]]", "", $year); $year = ereg_replace ("[[:punct:]]", "", $year); $year = ereg_replace ("[[:alpha:]]", "", $year); if ($year < 2007) { $year = 2007; } if ($year > 2035) { $year = 2035; } }
if (isset($_GET['today'])) { $today = $_GET['today']; $today = ereg_replace ("[[:space:]]", "", $today); $today = ereg_replace ("[[:punct:]]", "", $today); $today = ereg_replace ("[[:alpha:]]", "", $today); }


$month = (isset($month)) ? $month : date("m",time());
$year = (isset($year)) ? $year : date("Y",time());
$today = (isset($today))? $today : date("d", time());
$daylong = date("l",mktime(1,1,1,$month,$today,$year));
$monthlong = date("F",mktime(1,1,1,$month,$today,$year));
$dayone = date("w",mktime(1,1,1,$month,1,$year));
$numdays = date("t",mktime(1,1,1,$month,1,$year));
$alldays = array('Sun','Mon','Tue','Wed','Thu','Fri','Sat');
$next_year = $year + 1;
$last_year = $year - 1;
if ($today > $numdays) { $today--; }


$dayname = date("l",mktime(1,1,1,$current_month,$current_day,$current_year));

$calendar_day = date("Y-m-d",mktime(0,0,0,$month,$today,$year));

// Current day
$sql_currentday = "$current_year-$current_month-$current_day";


echo"
<table border='0' cellpadding='5' cellspacing='1' bgcolor='#999999' width='550'>
      <tr>
<td class='months'valign='middle' align='center'>
<a href='".$_SERVER['PHP_SELF']."?year=$year&today=01&month=01' class='normal'>Jan</a></td>

<td class='months'valign='middle' align='center'>
<a href='".$_SERVER['PHP_SELF']."?year=$year&today=01&month=02' class='normal'>Feb</a> </td>

<td class='months'valign='middle' align='center'>
<a href='".$_SERVER['PHP_SELF']."?year=$year&today=01&month=03' class='normal'>Mar</a> </td>

<td class='months'valign='middle' align='center'>
<a href='".$_SERVER['PHP_SELF']."?year=$year&today=01&month=04' class='normal'>Apr</a> </td>

<td class='months'valign='middle' align='center'>
<a href='".$_SERVER['PHP_SELF']."?year=$year&today=01&month=05' class='normal'>May</a> </td>

<td class='months'valign='middle' align='center'>
<a href='".$_SERVER['PHP_SELF']."?year=$year&today=01&month=06' class='normal'>Jun</a> </td>

<td class='months'valign='middle' align='center'>
<a href='".$_SERVER['PHP_SELF']."?year=$year&today=01&month=07' class='normal'>Jul</a> </td>

<td class='months'valign='middle' align='center'>
<a href='".$_SERVER['PHP_SELF']."?year=$year&today=01&month=08' class='normal'>Aug</a> </td>

<td class='months'valign='middle' align='center'>
<a href='".$_SERVER['PHP_SELF']."?year=$year&today=01&month=09' class='normal'>Sep</a> </td>

<td class='months'valign='middle' align='center'>
<a href='".$_SERVER['PHP_SELF']."?year=$year&today=01&month=10' class='normal'>Oct</a> </td>

<td class='months'valign='middle' align='center'>
<a href='".$_SERVER['PHP_SELF']."?year=$year&today=01&month=11' class='normal'>Nov</a> </td>

<td class='months'valign='middle' align='center'>
<a href='".$_SERVER['PHP_SELF']."?year=$year&today=01&month=12' class='normal'>Dec</a> </td>
</tr>
</table>
<table border='0' cellpadding='5' cellspacing='0' width='550'>
<tr>
<td width='50%'valign='middle'align='left'>
<b>$monthlong, $year</b></td>

<td width='50%'valign='middle' align='right'>

<table border='0' cellpadding='0' cellspacing='0'>
<tr>
<td valign='middle' align='center'><a href='".$_SERVER['PHP_SELF']."?year=$last_year&today=$today&month=$month'><img src='jcal_img/ltab2.gif' border='0' name='calb2'align=middle></a></td>
<td width='20'>&nbsp;</td>
<td bgcolor='white' valign='middle' align='center'><b>$year</b></td>
<td width='20'>&nbsp;</td>
<td valign='middle' align='center'><a href='".$_SERVER['PHP_SELF']."?year=$next_year&today=$today&month=$month'><img src='jcal_img/rtab2.gif' border='0' name='calb1'align=middle></a></td>
</tr>
</table>
</td>
</tr>
</table>";


//display day names
echo "<table border='0' cellpadding='0' cellspacing='1' bgcolor='#999999' width='550'>\n<tr>\n";
foreach($alldays as $value) {
  echo "<td class='calendar-day-head' valign='middle'>$value</td>\n";
} // End od for each
//display day names
echo "</tr>\n<tr>\n";




//////////////////
for ($zz = 1; $zz <= $numdays; $zz++) {
// Foramt the day for the calendar day
$calendar_date = mktime(0,0,0,$month,$zz,$year);

$calendar_day_zz="".date("d", strtotime("+0 month", $calendar_date))."";

$calendar_day="".date("Y-m-d", strtotime("+0 month", $calendar_date))."";

$sql_currentday = "$year-$month-$calendar_day_zz";

////////////////////
$eventQuery = mysql_query("SELECT * FROM calendar_dates WHERE date = '$sql_currentday'", $link);      
      
while($row = mysql_fetch_array($eventQuery)) {
$thiseventID = $row['eventID'];
$text1=$row['title'];
if (strlen($text1) > 20) {

$chars = 15;
$text = $text1." ";
$text2 = substr($text,0,$chars);
$text3 = substr($text,0,strrpos($text2,' '));
$text4 = $text3."...";

$thisTitle= stripslashes($text4);
} else {
$thisTitle = $text1;
}


if($thiseventID){
$eventTitle[$zz].= '<a href="javascript:void(0)" onclick="javascript:eventTitle_click(' . $thiseventID . '); return false;"style="color: #000;font-size: 9pt;">' . $thisTitle . ' </a><br />';
} else {
$eventTitle[$zz].= '';
}
}
//////////////////
 
}
/////////////

//display blank days as nbsp's
for ($i = 0; $i < $dayone; $i++) {
  echo '<td bgcolor="#FFFFFF"width="75"height="50"><img src="jcal_img/spacer.gif"width="75"height="50"alt="" /></td>';
} // end of for




//display days
for ($zz = 1; $zz <= $numdays; $zz++) {
  if ($i >= 7) {
 
print("</tr>\n<tr>\n");

$i=0;
}

// Foramt the day for the calendar day
$calendar_date = mktime(0,0,0,$month,$zz,$year);

$calendar_day_zz="".date("d", strtotime("+0 month", $calendar_date))."";

$calendar_day="".date("Y-m-d", strtotime("+0 month", $calendar_date))."";

$sql_currentday = "$year-$month-$calendar_day_zz";


  //check current day for an event
  if ($calendar_day_zz == $today) { //mark todays cell regardless

echo "<td bgcolor='#CCCCCC'valign='top' width='75'style='minheight: 75px;'>
<div class='day-number'><b>$zz</b></div><br /><img src='jcal_img/spacer.gif'width='75'height='2'alt='' /><br />";

if(!empty($eventTitle)){
print '<ul style="margin-top:0;margin-bottom: 0;padding-left: 2px;margin-left: 2px;"type="square">';
print_r($eventTitle[$zz]);
print '</ul><br />';
} else {
print '<img src="jcal_img/spacer.gif"width="75"height="50"alt="" />';
}



echo"</td>\n";

} else {

echo "<td valign='top' bgcolor='#FFFFFF'width='75'>
<div class='day-number'><b>$zz</b></div><br /><img src='jcal_img/spacer.gif'width='75'height='2'alt='' />";

if(!empty($eventTitle)){
print '<ul style="margin-top:0;margin-bottom: 0;padding-left: 2px;margin-left: 2px;"type="square">';
print_r($eventTitle[$zz]);
print '</ul><br />';
} else {
print '<img src="jcal_img/spacer.gif"width="75"height="50"alt="" />';
}


echo"</td>\n";

}
 


  $i++;
}

// Create empty days
$create_emptys = 7 - (($dayone + $numdays) % 7);
if ($create_emptys == 7) { $create_emptys = 0; }

//display blank remaining cells
if ($create_emptys != 0) {
  echo "<td bgcolor='#FFFFFF' valign='top' align='center' colspan='$create_emptys'>";

print '<img src="jcal_img/spacer.gif"width="75"height="50"alt="" /><br />';  
 
echo"</td>\n";
}

echo "</tr>\n";
echo "</table><br />\n";
?>
</div>
Avatar of mrroy69

ASKER

Wish I had thought of this. Thanks