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]

So in the database table you have a start date and an end date for each event/row, something like ...

eventID startDate  endDate 
0000001 2006-01-01 2006-01-05 
0000002 2006-02-12 2006-02-12 
0000003 2006-02-12 2006-02-15

... and in your calendar you want a links on

Jan 1 
Jan 2 
Jan 3 
Jan 4 
Jan 5 
Feb 12 (two links on this date) 
Feb 13 
Feb 14 
Feb 15

Is that correct?

[dreamcatcher]

Hi Coop,

Not quite. I want the display to span days. So using your example:

3 events. First spans 5 days, 2nd no days, 3rd 3 days...

eventID startDate endDate
0000001 2006-01-01 2006-01-05
0000002 2006-02-12 2006-02-12
0000003 2006-02-12 2006-02-15

This would be..

Jan 1 (link for event 0000001)
Jan 2 (link for event 0000001)
Jan 3 (link for event 0000001)
Jan 4 (link for event 0000001)
Jan 5 (link for event 0000001)
Feb 12 (link for event 0000002 && link for event 0000003)
Feb 13 (link for event 0000003)
Feb 14 (link for event 0000003)
Feb 15 (link for event 0000003)

Hope that makes some sense. So, basically a link on all days the the event or events span. I can easily loop and show the event for the start date or the end date on their own, but the span has me confused

dc

[coopster]

So everyday (that there would be an event) has only one link, even if that day has more than one event that day (Feb 12) ... correct?

[coopster]

I think I know where you are headed with this now. You want to know which days have events on them before you build the calendar so you can create links. You really don't care how many events are on any given day, you just want to know whether or not there is an event for that day.

So first you are going to

1. select from your table

   WHERE MONTH(startDate) = $thisMonth OR MONTH(endDate) = $thisMonth

2. loop through result set and build an array of days on which events are taking place for the month requested
3. build your calendar, making links by checking to see if the day being built is found in the events array built during the last step

Sound right?

[dreamcatcher]

Coop,

That is correct.

dc

[coopster]

Hmmm. I suppose there are a number of ways to approach this. I would tend to let the database engine do some work here for me. Have you ever used an SQL CASE control structure? They are handy...

$month = 3; 
$year = 2006; 
$first = 1; 
// last day in month for year 
$last = date [php.net]('t', mktime [php.net](0, 0, 0, $month+1, 0, $year)); 
$stmt = " 
 SELECT 
  eventID, 
  startDate, 
  endDate, 
    CASE MONTH(startDate)  
      WHEN $month THEN DAY(startDate)  
      ELSE $first  
    END 
    ¦¦ ',' ¦¦  
    CASE MONTH(endDate)  
      WHEN $month THEN DAY(endDate)  
      ELSE $last  
    END AS  
  range  
 FROM eventsTable 
 WHERE MONTH(startDate) = $month OR MONTH(endDate) = $month"; 
$eventDays = array(); 
$rows = mysql_query($stmt); 
while ($row = mysql_fetch_array($rows)) { 
 list($lo, $hi) = explode(',', $row['range']); 
 foreach (range($lo, $hi) as $day) { 
   $eventDays[$day] = $day; 
 } 
} 
ksort [php.net]($eventDays); 
print '<pre>'; 
print_r($eventDays); 
print'</pre>';

That will give you the array you are going to need I believe. Then use in_array [php.net] when you build your calendar to see if there are any events to link on that month's day.

[dreamcatcher]

Coop, I do appreciate the help you are giving me. Funny thing is only today I printed out a tutorial on the CASE control structure, I figured it might do the trick. Just couldn`t wrap my head around it.

Anyway, I have an event set as follows:

Start: '2006-03-01'
End: '2006-03-05'

When I ran your code the array it gave me was as follows:

Array
(
[0] => 0
[1] => 1
)

Not quite sure what thats telling me.

dc

[coopster]

Sorry, I should have explained it a bit --

The CASE logic is basically building a range of the days for each row selected for the month and year you are specifying (I did not include the year in the WHERE clause of the query, you will want to add that). So, if you had one row like the example you specified here it would return:

eventID startDate  endDate    range 
0000001 2006-03-01 2006-03-05 1,5

Then the array created should be a unique key=value pair representing each day (keys and values matching) in all the rows returned. In this case, I would expect to see

Array 
( 
  [1] => 1 
  [2] => 2 
  [3] => 3 
  [4] => 4 
  [5] => 5 
)

What happens when you echo each row to the browser during the while loop?

[dreamcatcher]

Thanks for the explanation. I echoed the rows and get the fromDate and toDate echoed correctly, but the range doesn`t appear to be returning the correct result.

So, I now have two events in the system, both in March, one spanning 5 days, the other spanning 4. If I echo the dates thats ok:

from: 2006-03-01
to: 2006-03-05

from: 2006-03-03
to: 2006-03-07

but the range is just 1, whether I have one event or more. So, not returning 1,5 as expected.

[coopster]

Hmmm. I wonder what gives ...? Did you remember to replace the broken pipe characters since the forum breaks them when you cut/paste? Nah, can that idea, the query would have failed. Some troubleshooting ideas ...

1. Try dumping the $stmt variable and see what prints out
   
2. Try running that dumped stmt from the command line to see what the returned rows look like.
   
3. What database/version are you using?
   
4. Your date columns are of type DATE, correct?

[dreamcatcher]

Try dumping the $stmt variable and see what prints out

SELECT id, fromDate, toDate, CASE MONTH(fromDate) WHEN 3 THEN DAY(fromDate) ELSE 1 END ¦¦ ',' ¦¦ CASE MONTH(toDate) WHEN 3 THEN DAY(toDate) ELSE 31 END AS range FROM mgr_events WHERE MONTH(fromDate) = 3 OR MONTH(toDate) = 3

Array
(
[0] => 0
[1] => 1
)

Try running that dumped stmt from the command line to see what the returned rows look like.

I have no idea how to do that, sorry.

What database/version are you using?

localhost = 5.0.15-nt.
Production servers = MySQL 4.0.25-standard

Your date columns are of type DATE, correct?

Yes.

dc

[jatar_k]

>> Try running that dumped stmt from the command line to see what the returned rows look like.

so take that query that you dumped and login to mysql via the command line. Then see what that query returns that way.

Most hosts give you command line access, if not you could probably use that query in phpmyadmin or whatever you use to manage your db.

[coopster]

Try running that dumped stmt from the command line to see what the returned rows look like.

I have no idea how to do that, sorry.

No problem, that's just how I usually do it to see the raw data returned by and SQL query. I cut and paste the stmt from the browser as you did here and then paste it into a

mysql>

command line interface to see what the database returns to me without having to use PHP to parse and display the returned data. Another way to do so is to dump the $row data in a while loop:

print '<pre>'; 
while ($row = mysql_fetch_array($rows)) {  
 print_r($row); 
} 
exit('</pre>');

I'm just trying to help you figure out if you are getting the raw data back as we expect to see it.

[dreamcatcher]

Thanks guys. I did Coops while loop method, and this is what I see:

Array
(
[0] => 2
[id] => 2
[1] => 2006-03-01
[fromDate] => 2006-03-01
[2] => 2006-03-05
[toDate] => 2006-03-05
[3] => 1
[range] => 1
)
Array
(
[0] => 3
[id] => 3
[1] => 2006-03-03
[fromDate] => 2006-03-03
[2] => 2006-03-07
[toDate] => 2006-03-07
[3] => 1
[range] => 1
)

dc

[coopster]

Note: didn't realize jk had posted at the exact same time ;-)

All looks fine except our 'range' -- it looks like it is returning a boolean TRUE or something. I am using a standard SQL concatentation there but I wonder if the concatenation operators aren't being read as logical OR's or something. I would expect to see at least the comma separator there ...

[dreamcatcher]

I just tried the same code on a production server running MySQL 4.0.25-standard and the query failed completely:

Check the manual that corresponds to your MySQL server version for the right syntax to use near '(fromDate) ELSE 1 END ¦¦ ',' ¦¦ CASE M

?

[coopster]

Now THAT one looks like it choked on the PIPE chars. The pipe characters used in this fashion are standard concatenation operators in ANSI SQL. Hold the phone, that's probably it. I have my server setup to mimic an ANSI standard SQL server. Let's change the concatenation operators to use MySQL's CONCAT() function instead:

$stmt = " 
 SELECT  
   eventID,  
   startDate,  
   endDate,  
     CONCAT( 
       CASE MONTH(startDate)  
         WHEN $month THEN DAY(startDate)  
         ELSE $first  
       END, 
       ',',  
       CASE MONTH(endDate)  
         WHEN $month THEN DAY(endDate)  
         ELSE $last  
         END 
     ) AS  
   range  
 FROM eventsTable  
 WHERE  
  YEAR(startDate) = $year AND MONTH(startDate) = $month  
  OR  
  YEAR(startDate) = $year AND MONTH(endDate) = $month" 
;

[coopster]

Oh yes, I'm sure of it now. Man, sorry dc -- completely spaced that MySQL comes default with pipes as OR operators -- search this page for 'concatenation' and you'll see what I mean:

MySQL Extensions to Standard SQL [dev.mysql.com]

I don't like that, I like developing in standards (purist?) so I modified my Server SQL Mode [dev.mysql.com] to change the

PIPES_AS_CONCAT

directive.

I know this is it. Use CONCAT and you should be good to go.

[dreamcatcher]

Woo Hoo, Coop, you`ve nailed it. Its now returning the correct range.

Thank you so much for all the help. Learnt me a couple of new things along the way too. Superb.

:)

[dreamcatcher]

Coop, one more issue I have if you have time..

I have the calendar returning the correct range now, but I`ve noticed that if I set a date as:

Start: Feb 26
End: April 2

It only shows links for April 1 & 2. I don`t mind it ignoring February as thats in the past, just wondering why its not spanning the whole of March too? If I specify a range for a single month it works fine.

Many thanks,

dc

[coopster]

Right on, dc. I was assuming for any given calendar you were building it was for one month at a time, which we will stick with for now. So, we have to have a closer look at our WHERE clause. We are asking for rows WHERE

WHERE  
 YEAR(startDate) = $year AND MONTH(startDate) = $month  
 OR  
 YEAR(startDate) = $year AND MONTH(endDate) = $month"  
;

or where the startDate is '2006-03-?' or endDate is '2006-03-?' where the '?' respresents any day within the month/year calendar being built. This works great when the event starts or ends in the month/year being requested, but as you said, what if my start date is before this and the end date is after this? I need to get those as well. Right. So now we need to go to a larger range in the WHERE clause. We want any rows where the year/month is between the start date and end date (inclusive).

WHERE 
 YEAR(startDate) <= $year AND MONTH(startDate) <= $month 
 AND  
 YEAR(endDate) >= $year AND MONTH(endDate) >= $month

[dreamcatcher]

hee hee, well thats certainly spanning March now.

And April, May, June...

In fact all the events are spanning all the months. LOL.

I had to have a chuckle at that. Sorry. I played around with the operands, but couldn`t get the desired result.

dc

[coopster]

But isn't that what you would want? For any given month, you would want to build links if that month had an event that spanned the entire month, ... no? For example, if the month to build the calendar for was March (03), and the only rows in the file were ...

eventID startDate endDate range 
0000001 2006-01-01 2006-12-31 1,31 
0000001 2006-02-28 2006-04-01 1,31 
0000001 2006-03-01 2006-03-02 1,2

You would want the array to return key=value pairs for 1 - 31, or every day in March since there indeed was an event for every day in March. Is that not the desired result?

[dreamcatcher]

Coop,

I have two problems. The first is the query is still failing on a production server.

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(fromDate) ELSE 01 END, ',',

Fine on localhost using the later sql version.

The second is the span. Let me explain more clearly. Lets say I have this event in the system:

Start: 25 Feb
End: 2 April

Now I would expect to see the event for the 25,26,27,28 Feb, ALL of March and then 1, 2 April.

Whats happening is the event is spanning ALL of Feb, ALL of March, ALL of April, ALL of May etc...ALL events in the system are spanning ALL months. Even Christmas which I have set in December is showing up in March. LOL.

As it is at the moment its spanning great for the current month. So, do we need another query for the previous month and then one for the next?

Hope that made some sense. Still not sure why the query is failing on MySQL 4.0.25 Standard.

David.

[dreamcatcher]

Also Coop, as a follow up, I am changing the month variable dynamically, so if I select April, then this changes the variable to 04. Isn`t this the same as your original code where I just used 03 for March?

Can`t understand why its working for March, but then not for other months if I go back or forward.

dc

[coopster]

The only thing I can figure on the server issue is the version. There is a short blurb on the 4.1 docs page for the CASE control structure:

Before MySQL 4.1, the type of the return value (INTEGER, DOUBLE, or STRING) is the same as the type of the first returned value (the expression after the first THEN). From MySQL 4.1.0, the default return type is the compatible aggregated type of all return values.

Note that CASE evaluation depends also on the context in which it is used. If used in string context, the result is returned as a string. If used in numeric context, the result is returned decimal, real, or integer value.

I'm trying to put this all together in my head to figure out the difference and what you may have to do to get it to work correctly.

BTW, I looked at the column name being used --> 'range' and in 5.1 it is a reserved word, dc. Rename that column alias to something else, 'daysRange' perhaps.

[dreamcatcher]

Thanks Coop. I changed the name, but still the same. This advanced SQL stuff is a little out of my depth I`m afraid. :(

[coopster]

Yes, the result set returned would indeed bring back multiples of the same range if the dates spanned the month you are building. You could always add a DISTINCT keyword to trim that back -- but I usually do this last, once I have the results coming in the way I want. The only row we really want or need back from this query is the 'daysRange' that we are building. We are displaying the other columns for now just to see how things are working.

We may be missing in communication here. Have a closer look at the rows being returned in message #24 ... posted again here:

eventID startDate  endDate    daysRange 
0000001 2006-01-01 2006-12-31 1,31 
0000001 2006-02-28 2006-04-01 1,31 
0000001 2006-03-01 2006-03-02 1,2

Although the first row being returned is spanning from Jan 1 - Dec 31, the event still covers every day in March, so we need the range 1 - 31, or every day of March to be recognized as holding an event on that day so that when we build the calendar for March we know which days hold events. The same holds true for the next row except that it has a different startDate and endDate -- but, it still spans all of March so we need every day to be recognized. The last row of course only spans the first two days of March, but those two days have already been 'included' in the previous date range(s) returned '1,31'.

We aren't so much concerned with which rows are being returned here as we are with the range of days telling us to *mark* as having events. Eventually we could add a DISTINCT keyword to our query and specify only the 'daysRange' column to be returned:

daysRange 
1,31 
1,2

In our processing loop then, we could even check to see if we have a full month value returned and after the array gets created we break from the

while

loop since we know we really don't need to continue building the array of eventDays.

while ($row = mysql_fetch_array($rows)) { 
 list($lo, $hi) = explode(',', $row['range']); 
 foreach (range($lo, $hi) as $day) { 
   $eventDays[$day] = $day; 
 } 
 // Is this a full date range? 
 if ($lo == $first && $hi == $last) { 
   break; 
 } 
}

Does this make sense? You can see where now we could actually take it a step further by making 'lo' and 'hi' two separate result set columns and sort them to get them to come to the top of our result set if they did indeed ever exist. That would let our PHP processing loop execute but once and break out because we had the full date range in our array of possible eventDays.

I'm getting ahead though. First, let's stick with bridging the possible communication gap in regards to the current result set being returned and the how/why of it. Remember that the assumption is we are building for only one calendar month at a time and the possibility of an event on any given day within that month.

Hopefully I haven't misunderstood the desired return!

[dreamcatcher]

Does this make sense?

I understand now about the date range. 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. And there`s still this problem of the syntax not working on the production server.

Grr..:)