Go Premium for a chance to win a PS4. Enter to Win


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?


What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

SASS allows you to treat your CSS code in a more OOP way. Let's have a look on how you can structure your code in order for it to be easily maintained and reused.
Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Suggested Courses

916 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