Spanning Days in Calendar

[dreamcatcher]

Hi Guys,

Calendars always confuse me. LOL.

Ok, I have coded a calendar which displays all the days for each month ok. They are shown as boxes, ie 1-31. Now I have events posted that last for a duration of days.

Example:

Start = 1 january
End = 5 January

So, whats the sql query to span the days and show a link for each of those days? 1-5? Bearing in mind that multiple events could be posted, so it needs to span for all events. I`m sure its something simple, but I keep on scratching my head. At this rate I`ll be bald.

If its more complicated that I first thought, I`ll just forget it and have just a start day.

Cheers,
dc

[coopster]

I just can`t wrap my head around the syntax I need to pull the desired results. I`ve tried loops within loops and the hole just keeps getting bigger and bigger

Can you explain what the desired results are a bit more? I'm not quite following that part yet. Perhaps if I understood where you are headed here I can clarify.

I'm assuming you mean the bit about building the calendar dates ... if so, that is the easy part. pseudocode:

while (making each day in the calendar for this month) { 
 if (in_array($thisCalendarDay, $eventDays)) { 
   // output the html day with a link 
 } else { 
   // output the html day without a link 
 } 
}

[dreamcatcher]

Coop, you are understanding it ok, its just I can`t get it to return events for a date range.

The other thing is this issue with the SQL versions. If there is no alternative for the earlier version, then maybe I should just stick with showing the start day and leave it at that?

dc

[coopster]

should just stick with showing the start day

That's what this does. It returns a range for you to use but the links would only be for a start date. If there is one event that spans from March 7 - 10, then there would be a link on each day of the calendar for March in that range. One for March 7, one for March 8, one for March 9 and one for March 10. When the user clicks the link, you would retrieve all events for that day (or events that fall in that range) and display them on another screen.

[coopster]

On the other hand, if you wanted to show links for each event on your calendar for the days which apply (multiple events on any given day would show multiple links), you could simply modify your inner loop to read and store the individual events. While we are at it, let's modify the SELECT query to just make the low/high date ranges separate columns returned in our result set. Perhaps this simplification will get you over your server version issue. Note, I also added the event description so you can use it to display on your links. You could SUBSTRING it or whatever to show only that portion you want.

$month = 3; 
$year = 2006; 
$first = 1; 
// last day in month for this year 
$last = date('t', mktime(0, 0, 0, $month+1, 0, $year)); 
$stmt = " 
 SELECT  
   eventID,  
   eventDesc, 
   startDate,  
   endDate,  
     CASE MONTH(startDate)  
      WHEN $month THEN DAY(startDate)  
      ELSE $first  
     END AS 
   lo, 
     CASE MONTH(endDate)  
      WHEN $month THEN DAY(endDate)  
      ELSE $last  
     END AS  
   hi  
 FROM eventsTable  
 WHERE  
   ( 
     YEAR(startDate) < $year  
     OR  
     YEAR(startDate) = $year AND MONTH(startDate) <= $month 
   ) 
   AND  
   ( 
     YEAR(endDate) > $year  
     OR  
     YEAR(endDate) = $year AND MONTH(endDate) >= $month 
   ) 
 ORDER BY lo, eventID 
;

Now in your loop we just modify the array building portion:

$eventDays = array(); 
$rows = mysql_query($stmt); 
while ($row = mysql_fetch_array($rows)) { 
 foreach (range($row['lo'], $row['hi']) as $day) { 
   $eventDays[$day][$row['eventID']] = $row['eventDesc']; 
 } 
} 
ksort($eventDays);

And finally, when you are building your days, we have to look at the array keys now, since they will represent the days which have events on them:

foreach (range($first, $last) as $day) { 
 if (array_key_exists($day, $eventDays)) { 
   // start the html 
   foreach($eventDays[$day] as $id => $desc) { 
     // output each link 
   } 
   // end the html 
 } else { 
   // output the html day without a link  
 }  
}

[dreamcatcher]

Coop, thank you so much for your help and patience. I now have it exactly as I want it. Fantastic.

dc

[coopster]

DAY() is a synonym for DAYOFMONTH(). It is available as of MySQL 4.1.1. The production server is running 4.0.25. Changing to DAYOFMONTH() will resolve the issue.