Solved

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

Posted on 2006-12-01
10
173 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 41

Expert Comment

by:HonorGod
ID: 18054115
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
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...
0
 
LVL 1

Author Comment

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

Thanks

Bob
0
Technology Partners: 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

by:HonorGod
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'>
<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
 
LVL 41

Expert Comment

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

Expert Comment

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

Author Comment

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

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
ID: 18203979
I'd like to request this question be closed.
0
 
LVL 1

Accepted Solution

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

Computer101
EE Admin
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
illegal characters in XML using UTF-8 8 46
Problem with Connection / Parameter: 4 22
Tech Writing Terminology - Pane or Panel? 8 142
How to silent print from safari browser 6 324
When you work with shopping cart / ecommerce relates web sites, you need to pass the certain form post details to the payment gateway process page with required details for the products items you give to order. Also you may need to track the ordered…
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 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:
The viewer will learn how to dynamically set the form action using jQuery.

738 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