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

Posted on 2006-12-01
Medium Priority
Last Modified: 2010-04-06
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?


Question by:Mango-Man
  • 3
  • 3
  • 2
  • +1
LVL 41

Expert Comment

ID: 18054115
How about:

- GET ALL RESULTS events for month/year
- Process the results, populating your data structure(s) based upon the event information
LVL 29

Expert Comment

ID: 18054182
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...

Author Comment

ID: 18054854
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?


Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 41

Expert Comment

ID: 18058611
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;
LVL 41

Expert Comment

ID: 18058616
 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)
LVL 29

Expert Comment

ID: 18059594
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..

Author Comment

ID: 18059788
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!



Author Comment

ID: 18203979
I'd like to request this question be closed.

Accepted Solution

Computer101 earned 0 total points
ID: 18401328
PAQed with points refunded (500)

EE Admin

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
Browsers only know CSS so your awesome SASS code needs to be translated into normal CSS. Here I'll try to explain what you should aim for in order to take full advantage of SASS.
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question