Solved

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

Posted on 2006-12-01
10
168 Views
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
    LOOP THRU RESULTS
         WRITE RESULTS
    END LOOP
END FOR

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

Thanks

Bob
0
Comment
Question by:Mango-Man
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 41

Expert Comment

by:HonorGod
Comment Utility
How about:

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

Expert Comment

by:TeRReF
Comment Utility
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...
0
 
LVL 1

Author Comment

by:Mango-Man
Comment Utility
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?

Thanks

Bob
0
 
LVL 41

Expert Comment

by:HonorGod
Comment Utility
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'>
<html>
<head>
<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;
</script>
</head>
<body>
</body>
</html>
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 41

Expert Comment

by:HonorGod
Comment Utility
 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)
0
 
LVL 29

Expert Comment

by:TeRReF
Comment Utility
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..
0
 
LVL 1

Author Comment

by:Mango-Man
Comment Utility
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:

DAY TABLE & SEPERATE EVENT TABLE
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.

STATIC HTML
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!

Bob

0
 
LVL 1

Author Comment

by:Mango-Man
Comment Utility
I'd like to request this question be closed.
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed with points refunded (500)

Computer101
EE Admin
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

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.
JavaScript has plenty of pieces of code people often just copy/paste from somewhere but never quite fully understand. Self-Executing functions are just one good example that I'll try to demystify here.
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:
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now