Case Study: Database

Here's an example of our latest approach to database programming. It's a far cry from dBASE II (and Clipper) and even quite a leap from the C database programming we did. The backend, for example, doesn't rely on a proprietary database application but on the freely available MySQL. The user interface takes advantage of AJAX programming in Spry for data validation and reports but uses PHP as an intermediary between HTML and MySQL. In a nutshell, we've harness HTML with free Unix software to build a library catalog without paying for proprietary software.

Startup Page

The Startup Page doubles as the entire documentation for the project. It's modeled after our site to show how easily you can take advantage of CSS styling.

Instead of a traditional menu system, we tapped into Spry's tabbed menus. Since these are all loaded when the page is first retrieved, there's no delay when clicking on the tab.

During development, we tried using a mouse rollover instead of a click to activate each tab, but that was just a little too wild and crazy. You had to avoid the tabs when moving your mouse around the screen to stay on the current tab.

Each of the section headings (in navy blue) is also a link to that option. So you have multiple ways to get where you want to go.

And an email link to technical support is in the last paragraph.


The tabbed menus make it easy to see what the program can do.

Add a Title

Data entry needs all the help it can get to ensure accurate information.

The application uses a complex multi-field indexing scheme to guarantee an entry is unique.

Our data entry screen has a clean layout with bold field headings right aligned to the data entry fields. Most of the fields have ghosted entry help after them, but the Title field uses a Spry Hint to suggest how to enter the data.

That hint disappears the second you cursor to the field. And when you change a field, validation occurs flashing a red warning if a required field has been skipped or some other problem encountered.

Just above the entry form, you'll see the last entry. That confirms the table is acquiring the data you are entering. And the popuup menu retains the last entered value, so if you are working in one category, you save some time.


The data entry form provides hints, full data validation and auto suggestion for previously filled-in fields.

Edit a Title

Editing a title works much like entering one, which saves a lot of time learning how to edit an entry.

But you do have to retrieve just a unique title to edit it. The ID is always unique and the title may be.


Use either field to find a title.


It's no fun entering a lot of data unless you can use it. And to use data, you have to be able to ask questions the database can answer.

This application provides three ways to do that: a page that helps you construct a legal query even if you know nothing about SQL queries, a page that lets you write any SQL command you like (not just a query, actually) and a page that simply reports common queries.

Here the application is helping you build a query based on any field using two operators (these are all text fields so values are either exact or contained in the field.

In addition, you can specify how the report is arranged using the Order option.

The blue box provides helpful information at first, but it doesn't get in the way. And when you click the Display Titles button, the helpful text is replaced with the data itself.


Pull-down menus contain all the possibilities and the help information explains just what to do.


The Query form works in much the same way.

A free-form query can be entered in multiple lines. The ghosted help below the field reminds you what the field names are.

Here the help information has already been replaced with the report. Scroll bars are added where necessary.

You can easily print these reports using the browser's Print command. A CSS directive eliminates everything but what you see in the blue box when printing. And, of course, you can use the same Print command to print to a PDF.


The help information is replaced by the report without redrawing the page.


Some reports should appear on your dashboard rather than your monthly statement.

In most applications, these are the only kind of report you see. The Ad Schedule, the Monthly Statement, etc. don't require you to concoct a query to see them. They're routine.

Here we've highlighted three: Program Information, General Information and Collection Categories.

Program Info tells you instantly the version of the applicatin that's currently running and the last revision date.

General Info reports the MySQL database and table information, as well as the total number of records.

Finally, a running total of each category in the collection is displayed with both the number of titles in the collection and the percent of the collection each category represents.


Totals and percentages are calculated instantly using an SQL query.

Back to the Home page