Two Platefuls: Online Calendar and Event Database

flickr foto

Brunch
available on adrian’s flickr

Turnovers, fruit, muffins, eggs. Juice.
(Another fine free image from the flickr Creative Commons By Attribution collection.

So for the first installment here on what’s been on my plate project-wise, we are grabbing a couple of related items that support dynamic calendar information for our site and registration for events. The whole point was to make it so we were not manually editing date-specific content on our project web pages, so as events passed, they would roll off the “upcoming” items list.

We’ve had a mySQL powered event calendar running for our MCLI web sites since before 2000. The basic premise is a structure that provides the descriptive info (title, dates, times, locations, contact names, URLs) and a way so each calendar item is “tagged” or associated with a different project area, and as well provide a way of looking at all events.

It’s been working fine and has been extended to have a web admin interface so we can give control of the content to our staff and people who manage the projects. It’s one of these programming projects that has grown by accretion, a solid start, with other things strapped on and modified over the years. The original code was developed by previously employed part-time student programmer, so making the recent mods has been the fun exercise of diving in to OPC (Other People’s Code). There are still a few pieces that I am not 100% how they work, but they function.

The admin interface allows us to create some role assigned access (giving people access to only certain items in the calendar database), and a typical editing screen looks like:

So basically, all items tagged with a database flag to “include on front page” appear on the left sidebar of our main MCLI site and then link to a comprehensive calendar page, which can by browsed by date, searched, and we also have PDFs of the print calendars we send out each year to our faculty.

And then we can have specific calendar items pumped to different project pages- like the Honors Program events (left sidebar for upcoming and its own full calendar) and similar calendar data pushed for Dialogue Days, Ocotillo.

This is all handled by some centrally linked PHP library scripts for connecting to the database, and requesting event data based on some flags passed to a getEvents function (upcoming or past, how many to return, which site to filter by) etc. This allows us to do any kind of formatting on the output anyone needs.

For more than 5 years we’ve been providing online registration for many of our events… it had developed into a set of PHP scripts that were easily copies, but the registration data was just saved as tab delimited text files in each site, but it allowed is to provide realtime snapshots of the numbers registered, to generate email reminder messages, and some funky scripts for moving people from the registration list. The sites ended up fairly templated, with a PHP file that specified most of the event specifics, but it required manually edits for any changes in things like the registration cap or the event times. Plus about 1 out of 3 times I forgot to set the correct permissions on the data files that would allow the scripts to write to them.

But event registration and calendar data were related, so in August I whipped up a new system that would use a database to store rsvp information and then connect to the calendar database to provide the even information to the relevant site. The RSVP system is not so complicated, there is a users table that stores names, phone, email, college etc for each person who uses our system. And there is an RSVP table that links a users id to an event id (which links to the events database) with some other flags for tracking participation (e.g. a cancelled RSVP and other ones that indicate of they actually attended).

The big advantage is that we no longer have to require people to retype in their contact data. The first time they register, they plug in their email address:

and if it is not in the users database, the registration form includes these data fields:

Now, if they return to register for any other event, after entering their email above, they just get a short form (usually one button) since we know who they are:

I was really hoping to make this a slick one page AJAX powered system, but that will/may come later. A registration inserts an entry in the RSVP database, and triggers an automatic email (each site has its own template in case there is event specific information). The email includes a unique URL that allows a registered participant to cancel their RSVP, saving the trouble of having to contact our office.

We can provide a unique address to our event planners so they can see how many are registered for events, some summaries by colleges and job role.

Initially I was deleting the row from the database, but was reminded we measure the total number of pre-event RSVPs as a measure of leave of interest, and since it is safer in databases just to mark data rows as either active or not, I just inserted a new column to indicate their status… this also means we can create a future tool that will allow someone to “undo” their cancellation.

The modifications just added recently include our calendar admin interface to provide a checkbox to indicate if online registration is enabled, and allow changing of the maximum registration and status of professional growth credit… previously these were all info in the past that needed to be sent to me and manually editing in web pages.

By flagging calendar items as having online registration available, we can generate pages that now show a list of all upcoming events that have online RSVP and the current availability of openings. We provide new tools on our admin interface that allow our staff to also see live registration data, but tools to cancel an RSVP (if someone phones in) or to move someone from the canceled list back to active (bottom of the screen shot with the event summary):

And what is coming soon are some tools for individuals that can show all upcoming and past events they have registered for as a personal inventory… a longer term goal may be to have that info communicate with our PeopleSoft system that tracks employee training. Maybe. Maybe not.

Okay, calendars may not be all that exciting, but being able to build entries so our staff can update event details saves me a lot of time, we are trying to provide more comprehensive and less clumsy tools for our participants. None of this code development is truly sane or rational, it is being done as a “get it done” mode, as we do not have months or staff to plan it out in gory detail. Some of the code libraries need to be debundled into smaller pieces.

 Share this barking on social media

If this kind of stuff has value, please support me by tossing a one time PayPal kibble or monthly on Patreon