Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Nov 9, 1999

Dabbling in Live Databases: Pt 3

By Aaron Weiss

GUFE (goofy): The Generic but Usable Front End

Yadda, yadda, yadda ... for the past two installments we've been chattering away about databases, getting all high falutin' and theoretical. That's all fine and good, but after a while of heavy pondering you need something concrete between your fingers, to knead the dough and stomp in the mud. After all, would Einstein's theory of relativity have become so popular if he had nothing tangible to show for it, namely his hair? This month we go concrete, using Perl to build a visual front end to the live databases we have been dabbling with these past two months. It's simple, it's attractive, and it's GUFE - "goofy" - the Generic but Usable Front End. Why "generic"? Because you can basically apply this front end to any SQL compliant database with very little modification. Dabblers should be familiar with using DBI to query live databases from Perl, as we've seen in the past two installments, as well as with using CGI with Perl and web templates.

Let's Talk GUFE

The beauty of the DBI module in Perl, as we've seen with both MS Access databases under Windows and MySQL databases under Linux, is that you can use the same core code to query a database regardless of its vendor format, so long as you have installed an appropriate DBD module for that vendor. In the last two articles we've worked with one simple but prototypical sample database, named Clients, which has contained two tables: billing and invoices.

We've worked closely with the guts of DBI and performing queries of this database, but these queries have remained theoretical ... GUFE, on the other hand, is a Perl script which bridges the gap between the database and the web browser, essentially querying the database and formatting the results in a web browser using CGI.

GUFE screen shot
Click for full size image

The GUFE interface is relatively simple, as you can see in the screenshot above. The yellow (upper) portion of the page presents a view of the selected table based upon the query criteria. The default view simply presents the entire database table including all rows and fields, the equivalent of the SQL statement,

select * from billing

for example. Each field label in the top row of the table is a hyperlink -- clicking a field label generates a toggle sort of the entire table on that field; thus, clicking the "ClientName" label will sort the table in ascending order alphabetically on the ClientName field. A second click of the same label will re-sort the table in a descending fashion. At the bottom of the yellow region there are links to any other tables which are part of this database, allowing you to switch the view to a different table, thus allowing you to query any table in the database. Our example Clients database has only two tables of course.

The blue (lower) portion of the page presents several criteria fields, with which you can query the currently selected table. Because GUFE is a teaching tool for this article, rather than a production-quality script, the criteria implementation is far from airtight. For instance, you the user must enclose string values in single quotes in the criteria fields, and you will generate errors if you mismatch conditions and field types--in other words, attempting to character match against an integer field in the database table. Multiple criteria fields can be "AND" or "OR" combined, but you cannot use this interface to AND some fields and OR others simultaneously. Accounting for these gaps in interface design would be necessary in a real-world production, but the extra code required would dilute the main goal of GUFE and leave the script more difficult to follow. (Also, it's hard to do!)

Why is GUFE generic? With the exception of the hyperlinked "Invoice" field, GUFE can be applied to any SQL compliant database. All of GUFE's output, including the list of available tables, the table display, and the criteria fields, are generated on-the-fly based on queries of the specified database. Thus, GUFE is not tightly married to our example Clients database, and should be easy for you to adapt to your own live database project.

You are, of course, welcome to play with GUFE using our fictional Clients database. In fact, we recommend doing so before and after reading the remainder of this article, as we walk-through the little scamp's inner workings.

MySQL Archives

Latest Forum Threads
MySQL Forum
Topic By Replies Updated
MySQL in high availability and transction secure banking application klamor 1 August 28th, 10:24 AM
MySQL rollback UAL225 1 August 28th, 10:15 AM
Browsing a DB file that uses MySql finleytech 1 July 26th, 10:51 AM
php cookie won't pass variable to next page ITdevGirl 0 June 22nd, 12:13 PM