One of my main reasons for posting on this blog is to show some of the processes I use and the problems that can occur when coding web applications. A good case in point is when I recoded our staff directory a couple of weeks ago. When I code something for SCOCA it almost always involves three applications: an application for SCOCA public users, an app for staff, and a backend for someone on the staff to administer. I’m going to write a bit about my decision to integrate vCards into the staff directory.
The vCard is a standard that allows exchange of contact information between various PIM (Personal Information Manager) software applications. You’ve probably seen vCards attached to emails or as links on web pages. vCards are now a fairly old standard. Lets take a look at some of its structure (I’ve modified the email address below to prevent bots from getting the real email):
BEGIN:VCARD
VERSION:3.0
CLASS:PUBLIC
PRODID:-//class_vcard from TroyWolf.com//Modification by SCOCA//EN
REV:2009-03-15 22:24:07
FN:Joshua Leeth
N:Leeth;Joshua;;;
TITLE:Network Engineer
ORG:SCOCA
ADR;TYPE=work:PO Box 577;;175 Beavercreek Road;Piketon;Ohio;45661;
EMAIL;TYPE=internet,pref:leeth@scoca.itc.com
TEL;TYPE=work,voice:740-289-5327
URL;TYPE=work:http://www.scoca-k12.org/departments/department.php?did=3
Even if you’re not a coder you can see this is a fairly straightforward structure, although if the vCard was introduced now it would undoubtedly be done with XML, making it more versatile for use in other applications. If you want to look at the structure of any vCard, simply load it into Notepad.
For the public staff directory, I wanted our users to be able to click an icon next to a staff member’s name to download a vCard for their own PIM. Since the staff directory is displayed from a MySQL database, I wanted to create the vCard “on the fly” when users click the icon for the vCard.
Although it can take time to code, dynamically creating vCards, vCalendars, and other types of documents isn’t extremely difficult once you know the MIME-type of a file and some of its structure. For SCOCA’s Training Management System (TMS), one of my backend applications dynamically creates an Excel spreadsheet that tracks all of the training we do at SCOCA, including numbers trained, districts served,who is doing the training etc., and ties this into our goals and strategies for our Continuous Improvement Plan. It took me about two weeks to figure out the proper Excel Structure and SQL queries to get it right for the requirements of SCOCA administration. For the vCard I was fairly sure that someone had probably already done the heavy lifting and created a PHP class that would make the process easier. After a bit of searching I found a vCard class coded by Troy Wolf.
One thing I wanted to try and add to the vCard was a staff photo. I knew that Outlook 2007 supported this feature so I thought it would be a nice addition. Looking through the vCard class file I noticed although a call was made to add a photo for the vCard, the proper structure was missing. I emailed Troy, and he replied that I was the first to discover this bug, and he told me where I should add it into his class.
I already have photos of each staff member stored in a directory on our web server, so my next step was to find how photos get into a vCard.. After a quick Google search, i found this line of code:
PHOTO;VALUE=URL;TYPE=GIF:http://www.abc.com/dir_photos/my_photo.gif
I couldn’t believe it was going to be this easy. It wasn’t.
The code simply wasn’t working, and I couldn’t see any pictures when loading the vCard into Outlook. I spent the rest of the day searching for answers, and of course I found “sort of” the same line of code, but with different syntax and additions. This is the time when coding can get frustrating, as I try to debug or find answers to problems.
The next day I found an alternative to getting the picture through a URL. I would need to convert each photograph to BASE64 and enter the entire structure into the vCard file. I was beginning to suspect that there was nothing wrong with my code, and it was probably that Outlook did not support a photo through a URL.
I didn’t want to code my own app just to convert 50 photos to BASE 64, so I found an online PHP application that would do it for me. All I had to do was enter the URL for each photo. I then created a new field in our staff SQL table and dropped each BASE64 conversion into the field. I imported my test vCard into Outlook, and there was the photo.
Ny final code for the vCard is a mixture of hard-coded information, and data retrieved from the database. Basically, I hard coded SCOCA’s address, and added a vCard note that links to our main web site. In the code excerpt below you can see how I added the hard coded info and the data pulled from MySQL: For each staff member’s web page I hard coded the URL for the individual’s SCOCA department page, and then dynamically added the department ID at the end, pulled from the database.
query_Recordset1 = sprintf("SELECT * FROM scocastaffdir WHERE id = %s",
$vc->data['first_name'] = $row_Recordset1['firstname'];
$vc->data['last_name'] = $row_Recordset1['lastname'];
$vc->data['title'] = $row_Recordset1['position'];
$vc->data['work_po_box'] = "PO Box 577";
$vc->data['work_address'] = "175 Beavercreek Road";
$vc->data['work_city'] = "Piketon";
$vc->data['work_state'] = "Ohio";
$vc->data['work_postal_code'] = "45661";
$vc->data['office_tel'] = $row_Recordset1['phone'];
$vc->data['email1'] = $row_Recordset1['email'];
$vc->data['url'] = "http://www.scoca-k12.org/departments/department.php?did=".$row_Recordset1['did'];
$vc->data['photo'] = $row_Recordset1['binphoto'];
$vc->data['note'] = "Visit us on the web at http://www.scoca-k12.org";
Below is a screenshot of the vCard from Outlook (click the image to view full size):
In a later post I’ll show the end result of the three versions of the Staff calendar that I created.
