Creating db entires for all the dates between a start date and end date

[danielwriter]

I am building a calendar event system where you can enter events into a db, the admin user enters a start date and an ending date for each event, right now it just makes one entry into the database using the start date as the event date, however if an event last more than one day there isn't an entry for any of the other days. How do I make multiple entries for each day in between the start date and end date so there is an entry in the db for each day the event is held?

[demtron]

I would create a loop that starts with the start date and increments a date variable by one up to the end date. For each interation of the loop, use a SQL INSERT statement on your database connection to add the appropriate rows. The actual code to implement this will vary depending on whether you are using VBScript of JScript for your ASP page.

Hope that helps!

[danielwriter]

Would that still work if the event lasts through a few months?

[demtron]

Sure. Even if there were 1000's of days between beginning and end, this would add one row for each day. The only limitations here are 1) how much DB space you have and 2) CPU utilization from adding tons of rows (I wouldn't worry about it unless you were adding thousands of rows at a time - at that point, I would write a batch INSERT statement). Several years ago, I recall writing something exactly like this in FoxPro 2.0 (yikes!) for seminar scheduling.

[M@rco]

I would suggest something completely different...

What you're trying to do is store the data in the right format for how it needs to be presented - this is not best practice, nor is it practical.

Instead, you should store data in its simplest form (i.e. start date/time + end date/time) and move the logic to render each of the days covered by the event to the presentation layer so that they are calculated only when the calendar needs to draw them, which requires a little extra thought on the front end but means that altering the dates of the event (e.g. to make the length shorter by 2 days and change the hour of the start and end times) is as easy as changing the values in a single record, the presentation layer will handle everything the next time it has to display it.

Contrast that with your current approach, where you'd either need to remove all the records relating to the event, delete them, and insert new ones according to the new range... or to work out which ones were the same and which were not needed, deleting the ones surplus to requirements, and updating the front and back records with the new times. This makes a lot of additional work for yourself, is prone to introducing error into the logic, and is significantly more work for the database too (in volume of data processed for each calendar query, not just data storage).

And then there's time zones, multi-user updates, and other issues to consider which become a lot more complex to implement with your approach.

So which makes more sense to you?

[danielwriter]

Thats sounds much better, do you know how I would do that?
I use vb script.
Thanks!

[M@rco]

This is really a much bigger question than you think - what you're really saying is "I don't know how to do this, how to I find out how it's done?"... and the simple answer is that you should work it out!

:p

No doubt it wouldn't be too hard to find some client-side JavaScript or server-side VBScript that constructs a calendar in the way that I describe and to adapt it for your use, but that would be no "fun", and also won't help you develop your programming skills as much as it would if you sat down and worked out how to do it - it's not very difficult, but it's not easy, and I actually think that this is an excellent tutorial kind of problem, one I might use as a test if I were interviewing someone for a programmer/developer role, just to see how their mind works.

And if you can't think of the solution off the top of your head right now then won't it be satisfying to work it out for yourself? So have a go...

As with all programming problems, if you can do it on paper you can do it in code, so thinking through the logical process that you would perform if you were asked to draw the calendar on paper (cell by cell, i.e. day by day) if given the right event data is a good way to get your brain working.

To help, you might want to construct a data table in a spreadsheet listing the data (e.g. event name, date & time for start & finish, etc) associated with a few events (perhaps five or so) which overlap in various ways (with other events, rolling over weekends, over the start/end of months, etc).

Then think about how you'd use that data to draw the events for a single day, ANY day, just one day that's got at least one event on it. Write down the logic you work out as pseudocode (e.g. simplified code that won't execute but lays out the basic principle of what the code is doing, with loops, logic branches, calculations, etc) - this is often called "thinking in code", and is an essential programming skill that you should try to employ as often as possible. Once you think you've got the essence of it right, you can quickly turn the pseudocode into real code in order to test it, but of course do the bare minimum necessary to run the code and see if you're right - worry about pretty HTML, CSS, metadata, and other fluffy stuff later. Once you've got it right for a day, test it with a few other days. Then loop through a whole month and see if it's right.

And focus on the most important bits first, such as how you need to sort the data, how to loop through the data (by which I mean the range through which you are looping in order to draw the current cell, because past events don't need to be looped through once the cell on which they end has been passed), what order they appear in on a given day, etc. etc. You can always (and will) refine your code later - so, just think about the days of the month as a linear sequence first, don't worry about wrapping round to a new line every 7 days, which day of the week the month starts, etc. that's not the hard bit so leave it out until you've nailed the rest - only worry about making the calendar look pretty at the very end once it's functionally correct!

I hope you'll see this as a fun challenge that will help you learn to be able to solve the problem for yourself rather than just ask other people (like me) for the answer, or by looking at someone else's code.

If you get stuck then post relevant code, explain the problem, and I'll happily help you get past that particular obstacle, ok? But use me as a last resort!

Post your solution once you've nailed the basics, it would be interesting (for me) to see what you come up with, and I might be able to show you ways of optimizing the code to perform faster, etc. which would also further enhance your programming proficiency...



PS - And if anyone else fancies using this as an exercise then let me know, if there's interest I could create a little database with some demo event data which everyone could use as a starting point, it could even become some sort of group tutorial?! Or maybe I'm getting ahead of myself... lol :p