Efficent Calendar Code to Extract Date Based Events & Display as Columned Calendar

Hi all,

I've been considering trying to build something like this for about 12 months but when running over it in my head, I can never work out an efficient way of doing this.

I want to know the best way to extract all of the events for a given month from a database and display then in a HTML formatted calendar.  I have the code sorted to generate a calendar both in PHP and Vbscript (as the code produced by the ASP.NET Calendar Control is appalling!) but how to efficiently retrieve the data?  Doing the following (Pseudo code) seems clumsy to me:

FOR day = 1 TO daysInMonth
    GET ALL RESULTS events for day/November/2006

Anyone good any ideas?  Am I missing something blindingly obvious?


Who is Participating?
Computer101Connect With a Mentor Commented:
PAQed with points refunded (500)

EE Admin
How about:

- GET ALL RESULTS events for month/year
- Process the results, populating your data structure(s) based upon the event information
In PHP I would generate a timestamp for the first day of the month and one for the last day:
$begin = mktime(0,0,0,11,1,2006);
$end = mktime(23,59,59,11,30,2006);

Use those timestamps to retrieve all values from the DB in 1 query, sort them by date and then put them in the calendar.

It will save you the 1st for loop and a lot of queries...
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Mango-ManAuthor Commented:
Hi guys,

Thanks for your responses - I considered the option of returning all the events for a given month and then dropping them in to the appropriate days but unless I have a database record for every day and no more than one record for every day, this does help me sort the data in to days efficiently as far as I can tell?  Ultimate I have to loop for every day of the month one way or the other in order to build a container (eg "<td></td>") for each day.

Perhaps I not understanding your solutions - could you give me an example in PHP, VBSCRIPT or Pseudo code of how to actually write the data to HTML?

Thanks in advance for your help so far - I appreciate this is an odd one - is there anyway I can bolster the points for this past 500?


There is no way to "bolster the points past 500".
However, you can ask multiple questions.
<!DOCTYPE HTML PUBLIC '-//W3C//DTD HTML 4.01//EN' 'http://www.w3.org/TR/html4/strict.dtd'>
<title> Cal Test </title>
<script type='text/javascript'>

  function cal() {
    var body = document.getElementsByTagName( 'body' )[ 0 ];
    var days = 'Sun,Mon,Tue,Wed,Thu,Fri,Sat'.split( ',' );    // Creates a 7  element array of the DoW
    table = '<table border="1"><tr>';
    for ( var day = 0; day < 7; ++ day ) {
      table += '<th>' + days[ day ] + '<\/th>';
    table += '<\/tr>';
    // Create Date object for 1st of the month (e.g., 12/1/2006)
    var first = new Date( 2006, 11, 1 );
    alert( first );
    var last  = new Date( 2007,  0, 0 );      // Last day of preceeding month
    // Determine Date of Week (DoW) of the 1st (e.g., Fri)
    var dow = first.getDay();
    for ( day = 0; day < dow; ++day ) {
      table += '<td>&nbsp;<\/td>';
    for ( day = 1; day <= last.getDate(); ++day ) {
      table += '<td>' + day + '<\/td>';
      dow = ( ++dow % 7 );
      if ( dow == 0 ) {
        table += '<\/tr><tr>';
    for ( ; dow < 7; ++dow ) {
      table += '<td>&nbsp;<\/td>';
    table += '<\/tr><\/table>';
    var div = document.createElement( 'div' );
    div.innerHTML = table;
    body.appendChild( div );

  window.onload = cal;
 Now, all you have to do is fill in the information for each day that has stuff in your database.

Just in case you ask the question:

Q: Why is 12/1/2006 created using new Date( 2006, 11, 1 )
A: The month field is origin 0 (i.e., 0..11)
I wouldn't use a JS solution, but would sort the array like this. I just use 'date' for the name of your column that contains the dates... You should sort on date already in the query, so you don't have to order the array afterwards...

$days = array();
while ($row = mysql_fetch_assoc($result)) {
  $days[$row['date']][] = $row;

Now you have an array that looks something like:
array('01-10-2006'=>array(relevant days...), '02-10-2006'=>array(array of relevant days...), etc.)

Which you can easily traverse through with a foreach loop..
Mango-ManAuthor Commented:
Hi all,

Thanks so much for your contributions - I will post a second question for 500 points to the accepted commenteer (is that a word?  should be...)

HonorGod - Thanks for the contribution but I don't want to use a JavaScript solution - although the benefits of passing over some of the work to the client-side are very appealing, it immediately restricts me to JavaScript browsers and I may well want to port this to mobile devices, HTML emails, etc.

TeRReF - my PHP is at a very basic level but from what I understand - your array will propagate from a recordset / datagrid and loop through it but what defines where one day finishes and another begins if their can be a variable number of events (therefore rows in the recordset) per day and perhaps no event for a given day.  I don't understand what is defining the days in a month?  I really apologize if I'm missing something stupid.

In order to try and define my problem further I thought I should give a sample of database code:

eventID     eventDate        eventName
1              1/2/2006         Mango-Man's Birthday
2              13/2/2006       Remember to wash socks
5              14/2/2006       Buy a monkey outfit
9              19/2/2006       Breakfast at Tiffany's
13            28/2/2006       Make Calendar Application

I can easily determine the number of days in a month in PHP or ASP and loop through each day, querying the database for these OR I could load the whole month in to an array and query this for each day but both seem to require a loop within a loop and an if statement which just seems unwieldy.  I built an application like this before and did all of the querying with a stored procedure which sped things up but when deployed online with concurrent users hitting it, I wasn't happy that it was efficient enough.  

There are only two other options as I see it:

Have a table that has a day per row for the next few years, this would have a column that said whether or not there were any events for that day (and store their IDs  comma delimited) then each iteration of this datagrid would only query the events datagrid *IF* events were available and would know there IDs to speed things up significantly.

When the calendar database information is update, have it recreate a static HTML include for that month and keep say, two years worth of future tables on hand.  Not exactly elegant.

What do you think?  I must admit I was hoping there was a set way of managing calendar / event data that I simply had never found!

Thanks again all for your input!


Mango-ManAuthor Commented:
I'd like to request this question be closed.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.