Archive for Programming

Learn How To Program

(If you already know how to program, then learn how to program better.)

drupal icon

We often joke that independent schools aspire to be like small colleges. Witness the naming of University High School, College Preparatory School, and University Prep. Here’s a good way to emulate our tertiary friends — develop in-house programming expertise. As colleges and public school systems have learned, in-house programming expertise helps an educational institution develop best fit solutions at reasonable cost. We work in the field of technology. We should learn the tools of the trade and become producers, not just managers.

We are attempting to build the site development capacity to produce our next public-facing web site. Last year, we met with three local Drupal development companies in order to gain some perspective on the size and complexity of our challenge. We learned two critical lessons from these meetings. Local development companies preferred to take on the entire project rather than assist us with doing it ourselves, and we needed more in-house development expertise if we expected to pull off this project ourselves.

We have succeeded in improving our development expertise this year. I have accepted the challenge to master Drupal, especially version 6 and custom module development. I have learned through experimentation, books, forums, and specialized distributions. That’s the beauty of open source. The process is social and constructive. It’s easy to build up momentum. I build test sites and share my experiences through forums, Twitter, and on-campus conversations.

We have also increased our development capacity through hiring. In filling our open database specialist position, we did not count on gaining a programmer, but we were able to do so. Programming is a particularly useful skill in database management, since schools have seen so much growth in database-enabled web site services, such as grades, attendance, directories, reporting, and so on. Add our computer science teacher, and we may now have the capacity to build and support our own Drupal-based site. A particularly compelling web site project might even entice our computer science students to contribute.

The economic crisis has increased the importance of self-sufficiency. A first-class web site is integral to meeting our communication goals, but we should not pay FinalSite or Whipple Hill $50,000 for it. Developing a sophisticated site in Drupal or Plone will provide us with the capabilities and future flexibility that the school needs at a more reasonable cost. The effort we now spend building this expertise will be worth many thousands of dollars in the future.

Can you do it? Anyone can improve from their current skill level and add value to their work. Not so long ago, I moved from full-time teacher to half-teacher, half-academic technology specialist. My colleague in the IT department wrote the school’s first Perl script to collect student course requests. I was fascinated and studied the syntax in the script to write my own first, simple web applications. Two years and one school later, I installed and then learned to modify YaBB, a Perl-based discussion forum system. I then wrote a series of increasingly more important web applications for our school: student announcements system, single sign-on code snippet, community service hours tracker, alumni notes interface, and admission inquiry system. I am still a relatively amateur programmer, but I continue to learn from my peers and improve the quality of my work.

My next challenge is to move from custom, standalone development to configuring and adding functionality to a popular content management system. I now have an expert programmer as an office colleague with whom I can partner and from whom I can learn. If my dream comes true, I will gain collaborators at other schools who wish to share Drupal configuration tricks and develop new functionality for their public-facing web sites. Stay tuned to find out what degree of success we attain.

Converting Excel dates and times to SQL

Dates and times look great in Excel until you try to concatenate them within a formula. Then you see Excel’s messy raw date and time values. I have found it necessary to use the YEAR, MONTH, DAY, HOUR, and TIME functions to extract these values and then format them for a SQL query.

For example:

date and time

First attempt (didn’t work)
="INSERT INTO `tour_dates` SET `datetime`='"&D2&" "&E2&"';"

The result:

INSERT INTO `tour_dates` SET `datetime`='38260 0.375';


Second attempt (worked!)

="INSERT INTO tour_dates SET `datetime`='"&YEAR(D2)&"-"&MONTH(D2)&"-"&DAY($D2)&" "&HOUR(E2)&":"&MINUTE(E2)&":00"&"';"

The result:

INSERT INTO tour_dates SET `datetime`='2008-10-1 9:0:00';


Template Adusted

I have adjusted this blog template so that posts appear before sidebar content, especially for mobile devices. If this does not display properly in your browser, please let me know. It should look something like this.

School Archives in Drupal

Our alumni office is digitizing all of the items in the school archive this summer to better document what we have and make the collection more available to the school community. I have begun to configure Drupal to store this collection of images and descriptive information. This way, we have control of our data, we spend no cash on a commercial solution, and we can customize this as much as we want/can.

I could use the feedback of more experienced Drupal folk on this design. Many thanks!

I created a custom content type and attached the following taxonomy categories. I am thinking that heavy use of taxonomy will allow for easier navigation of the database than custom fields/exposed filters.


The first one, “Category,” is an internal term used by the alumni office to identify to which broad part of the school program the item belongs.

I only added two custom fields to the content type.

custom fields

ImageField allows easy image upload. Additionally, all of the uploaded files end up in a subdirectory of /files, so that it will be easy to move the archive elsewhere should we decide to do so one day.

image upload

I configured ImageCache to automatically create a thumbnail from the uploaded image, display it in the node teaser view, and link it to the full-sized image.


(this is just a sample I was using)

For my last trick, I installed Auto Nodetitle and wrote a little PHP to automatically generate archive ID numbers. For some reason, this didn’t work properly when the title was hidden, but it is likely better to leave it visible so that the archivist may manually override the automatically generated value if necessary.

Here’s the code I used for Auto Nodetitle.

$token = '[title]';
if (empty($token)) {
$sql = "select `field_id_value` from content_type_archive order by `field_id_value`";
$result = db_query($sql);
while ($row = mysql_fetch_assoc($result)) {
return 'Catlin Gabel Archives: Item '.$newid;
} else {
return $token;

Finally, the office would like users to be able to search by decade. I am already capturing the date as a custom date field and class year as a taxonomy term. How would I set up a search of either of those fields by decade? Is there an easier way than setting up a calculated field and searching on that?

Curriculum Mapping

editing screen

Last summer, I migrated our school curriculum map from a commercial system to a homegrown one. This project required careful attention. Teachers have put years of effort into the task of articulating their curriculum in a standard format. 5,000 individual entries comprise the map, describing each course unit by several dimensions such as essential questions and habits of mind. Anyone can view the map on our public web site — it represents the core work that our school undertakes in a highly visible venue. The old system suffered from an outdated graphic design, lack of integration with other school information systems, and challenging user interface. Users had developed low expectations for the system, for which we were paying thousands of dollars each year in software maintenance fees.

I found it most challenging to build a new Perl script around the old system’s database architecture. I couldn’t throw it out altogether but rather had to come up with original ways to navigate information that was not provided to us in a normalized database structure. I found it unnerving to migrate a vast quantity of critical information, but I feel relieved that all of the recent feedback has been positive.

Essential to this process was good user testing, which I didn’t get until just last month. Happily, teachers identified and I resolved major bugs, and now updating the maps appears to be proceeding smoothly. I appreciated the eagerness of the teachers to invite me to their editing sessions so that I could see problems, fix them immediately, and add requested features within a day or two.

Positive feedback from a variety of sources suggests that the new system is a great improvement from the old. We now control the appearance, user interface, and data of the system.

Drupal Development Update

This article describes my efforts this weekend to build out a new Drupal site. I post it here as a record to myself, a resource for others, and a request for feedback from more experienced Drupal developers.

I built out a new Drupal site this weekend, a clone of a server-side include site that I launched a few years ago. The company, run by friends, wants to provide more direct insight into their trips through blog posts and photo galleries. Using Drupal may also prepare us for the next step beyond that, the development of an online community on the site for guides and customers. For me, I appreciated taking the next step in my knowledge of Drupal, moving beyond my intranet experience of last year to the public face of a small business. I also remain impressed with Drupal’s power and versatility. One can build so many different kinds of sites with Drupal, yet enough depth/specialization exists to do the job right no matter what the task. Amazing.

sample pic

I took two tries to get the theme mostly right. I find it challenging to merge the CSS styles of the original site with the built-in Drupal ones. It has helped a lot to start with a simpler theme (this time, Chameleon) than a richer one with more to correct. I still find CSS styling nonintuitive, what with the different techniques used to embed CSS styles in div, span, p, a, ul, and li tags.



I finally gained some understanding of how theme regions work. I saw first-hand for the first time region definitions inside a theme and began to clue in that I can place a region wherever I like on the page, create a new block with content specific to that region, and then only display the content on one or more pages that I want. For example, I currently have weather (using the plugin of the same name) loading in the right-hand column. I want to eventually display it in the main content area but mix it with other, editable content there.

Displaying Perl Output
I didn’t want to rewrite the Perl components of the reservations, calendaring, and book order system, so I went looking for a way to display Perl output within Drupal. I found a neat JavaScript trick but took a while longer to determine that each document.write instance in Perl had to output a single line of code — no line breaks. In the end, it worked — JavaScript calls the Perl script from a Drupal block, which chugs through its operations and then returns the HTML to Drupal using document.write.

Image Galleries
For image galleries, I have before tried image_gallery and embedded Menalto Gallery. Both have had their downsides. This time, I used image_gallery but laid Lightbox v2 on top, and boy does it look great. I also wanted to give Flash Gallery a shot, but it died with script errors during install. For this client, FTP transfers of files to the remote host will be fine, but eventually I would like to implement something like Image Publishing in order to uploads dozens or hundreds of images at a time into Drupal, as Gallery does so effectively.

Lightbox v2

PHP Memory Limit
PHP memory limit is giving me a hard time. Currently, I can only upload photo gallery images of less than 100kb without producing “out of memory” errors. This happens no matter whether I use image_import or create content->image to upload the image. I suspect that our web host may restrict PHP to 16MB. I have run into errors trying to push their config any higher. I am using every module I have installed, so reducing overhead doesn’t look promising.

memory error

Taxonomy and Content Types
It took me a good while to understand how Taxonomy (Categories) works when I first started with Drupal just over a year ago. Now, I have barely proficient, at least enough to determine a strategy for how to organize trips in this site. Using the Content Construction Kit (CCK) and Taxonomy, I will have created three new content types (trip, itinerary, and equipment list) and a taxonomy called “trip type.” I can now produce lists of trips for each category, which I will next link to their related itineraries and equipment lists. Having each trip be a separate node will also allow us to open comment-based dialogue about the trips.

Preserving Referring Links
I am going to edit the .htaccess file to automatically remove .shtml for any old-style requests. As long as I follow a similar naming convention in Drupal as I did with the old HTML pages, we won’t lose links from referring sites when the Drupal-based one launches.

Unexpected Line Breaks
As I migrated trip descriptions from the old site to the new, I encountered bizarre line breaks where I didn’t expect them. Turns out that I found a slight conflict between TinyMCE and Drupal’s automatic line breaks. Though it sounds counterintuitive, it seems that one must disable line breaks in the Drupal input type definitions in order to avoid these unwanted line breaks. TinyMCE handles the conversion of line breaks into HTML tags on its own.



The weather module works great. What a gift. And to think that, two years ago, I gave up on a Perl-based weather solution I was trying to install on this site.

Integrated New Features
Our friends at the guiding company will have to think about how much home page real estate they want to give to the blog and image galleries. Right now, the two new features they want are buried. It’s not easy to shove the introductory content aside in favor of more specific, time-sensitive material.

Temporary Files Location
The temporary directory setting in File System died quietly, without Drupal error. On this shared hosting system, I moved the temporary directory to within the Drupal file system, and now it works fine. In other words, don’t use /tmp.

File Storage
I originally wanted to be rigorous and keep the Drupal file storage system outside of the public HTML directory, even though I don’t yet have any private files to store there. It worked great while logged in as admin but not for an anonymous user. For example, photo gallery images would silently not display for anonymous users, despite the proper permissions settings. This appears to have something to do with anonymous users not being able to access scripts in system/. I migrated the file store to the public web directory, and now it works fine. I will eventually need to figure this out for other sites.

Additional Modules Installed
Lightbox v2

Optional Core Modules Enabled

Drupal Subscriptions module

This week, I introduced Drupal blogs to senior project students looking to publish weekly reports of their work to their project advisors and the school community. Drupal seems a little thin on email notification features. Comment Notify appears to only permit comment followup, and Subscriptions is known to be buggy. I installed Subscriptions — do you have first-hand experience with it? The similarly-named “Subscription” module seems dead.

Subscriptions installed and ran smoothly in my tests and seems to provide exactly the flexibility that I want for our users. Is this the best way to do this?

Update 10/25/08: We have been running Subscriptions for nine months now with no apparent ill effects. That said, we have not tracked whether it sends notifications every time that it should.

Simple electronic portfolio in Drupal

A beautiful Drupal moment — I’ve been meaning to create this for a while, an ultimately it took me less than an hour to tweak this as I wanted.

Employing my usual stepwise development process, I have just created a bare-bones electronic portfolio content type to serve as a prototype for testing with a small group of teachers. The content type includes the required title and body plus the optional link and attachment fields. This allows users to post a piece of work, introduction, or reflective statement, attach a piece of work such as a word document or image file, or link to a piece of work already posted somewhere else. In the future, I may add content type fields for Image, Audio, and Video, though it may be simpler for the user to link to them instead. I then created a view to show a user his/her portfolio items in a table view. The view only worked once I installed Content Access and allowed users to see portfolio content type items that they themselves had created. I should at some point make it easier for users to search Drupal for their own content using an autoselect field.

The electronic portfolio tool is designed to be versatile. You could use the text field either to include the body of a piece of work (such as a poem), an introduction to a piece, or a reflection on the work. The link and attachment options are especially valuable. You could attach a Word document or JPG image, or if the work is already posted somewhere, you could link to it (e.g., Gallery image). I can add any number of other text fields, including categories, and even image, audio, and video media fields if we want the portfolio to contain these items directly. Anyone here could use the tool: students to collect exemplary academic work, teachers to reflect on their professional practice or organize a self-evaluation.

I have asked a small number of interested teachers for their help to grow this tool to the point that it supports the electronic portfolio needs that they anticipate having in the future. As usual, my approach is to release a prototype, invite a few people to use it, develop the tool into a mature version, make it available to everyone, and then invite all to decide how much value it has and how much we should encourage other people to use it. I will seek conversations that allow me to learn more about their teaching objectives, needs from the tool, improvements that may be made, and examples of people experimenting with use of the tool.

At least one of the elements of this tool is borrowed from DrupalEd (thanks, Bill!). Why not use DrupalEd entirely instead of building out this site from scratch? First, I find it easier to build up from Drupal core than to tear down from a rich, unique distribution. I am starting in the place where all knowledge about how this system works is widely shared and then only adding and configuring modules and objects in a way that any user would. Second, DrupalEd supports user, group, and community-wide content. In our school, Moodle supports group content, Drupal supports community content, and the portfolio piece is the first part that is individual. We operate on slightly different assumptions of needs than a school starting from scratch and seeking a complete content management system.


Importing Web Data into Education Edge

It finally happened. After five years of working in Blackbaud schools, I have taken the plunge. It took a web application to do it, of course. Over the last few weeks, I have migrated the admission inquiry form from my previous school to this one, except this time I set up all the data import routines. The script collects basic applicant details and allows them to schedule a parent tour and school visit. It produces a set of import files that you may then use to get the web data into Education Edge. It’s pretty slick once we work out all the intricacies of our process and import exceptions.

Many of the error messages in Education Edge are pretty nondescript, but I am finally beginning to get familiar with the conventions. I realized today that “Action for Import ID” meant that I should find the Import ID for the applicant, an automatically generated string of numbers and dashes that ensures that the tour/visit action ends up in the correct applicant record. This was easy to pick up with “select import id from ea7records where userdefinedid = [applicant id].” “Wrong field type” means that whatever Education Edge shows in the user input screen for a field does not necessarily match the format it uses during an import! Some guesswork may be required. For example, “Faculty/Staff Member” just becomes “Faculty/Staff” for the purposes of import. I am still searching for the correct format of the “send reminder to” field. In the UI, it’s simply the logon username for that individual. However, that fails the import as “invalid.”

I didn’t think I would ever say this, but some aspects of Blackbaud are beginning to look extremely powerful, and dare I say … elegant? … at least until it breaks the next time!

Bookstore Script Goes Live

We have just launched a bookstore point of sale system that I wrote as a web script. Why not use a commercial point of sale product? We want our students to be able to self-checkout at a computer workstation without requiring the presence of a staff member. The main student-facing page is a simple screen that allows one to log in using web site credentials and then buy one or more books using a barcode scanner. Easy, right?


I didn’t appreciate until neck-deep in the project that this was really more of an accounting project than a sales front-end! The business office can download sales from the script’s admin interface. The download file includes the student’s Education Edge ID, the EE code for the book purchased, the price, and the date of the sale. It includes a few dates — date of sale, post date, and due date, which are part of the billing process. In order to know the retail price, the script needs to keep track of the purchase price, which changes during the year as new editions are ordered and retail prices change. In order to know the total value of inventory for accounting purposes, the script needs to keep track of inventory and book cost — the price the school paid for the book. My goodness! It took a while to get all of these details sorted out and debugged. We have 270 students each buying a handful of books. After a couple of years of selling books, we will surely come out ahead in terms of time spent.