Web Reporting with MySQL, CSS and Perl

Extending the Maypole Soccer Club Database System with a simple Web-based reporting mechanism.

In the March 2005 issue of Linux Journal, I used Maypole to create a Web-based database application in only 18 lines of Perl code. The functionality provided by Maypole is impressive except in one important area: reporting. Consequently, I started to research technologies for producing reports from my Soccer Club system. My goal was to provide a set of standard reports that could be executed from a Web interface.

Web Reporting? What to Do?

Web reports can be produced in a lot of ways using any of the many server-side programming technologies, such as PHP, JSPs, Perl scripts and the like. Standalone desktop reporting tools also are available, and it's even possible to use OpenOffice.org to report on a MySQL database. As my reporting requirements are basic, however, I wanted to keep my intellectual effort to the minimum. What I didn't mind doing was spending time crafting the SQL queries that I'd need to produce my reports. Once written, I wanted my SQL query to produce an HTML table of results.

I can do this with Perl, of course, using the DBI and DBD::mysql modules, hand-crafting program code to send the query to the database. I then could post-process the results with more code, before—ultimately—writing yet more code to create the table. For my simple requirements, this felt like too much work. What I really wanted was a quick-and-dirty solution. In the remainder of this article, I detail the Web reporting solution I designed.

MySQL to the Rescue!

While browsing Paul DuBois' excellent MySQL Cookbook, I discovered a command-line option for turning the results of a command-line query into an HTML table (recipe 1.23, page 33). By way of example, consider the following command line:


mysql  -e  "select name from player"              \
    -u  manager  -ppwhere  CLUB

which produces the following textual output when invoked:

+-------------+
|name         |
+-------------+
|Robert Plant |
|Tim Finn     |
|James Taylor |
|Bryan Adams  |
|Ian Gillen   |
|Mick Jagger  |
|Neil Young   |
|Bob Dylan    |
+-------------+

These results not only show the names of all of the players in the Soccer Club database, but they also appear to indicate that the club's players are named after some famous folk and rock singers. When re-run with the HTML creation option, like so:


mysql  -H  -e  "select name from player"          \
    -u  manager  -ppwhere  CLUB

the above command line produces the following, which, trust me, is an HTML table:


<TABLE BORDER=1><TR><TH>name</TH></TR><TR><TD>
Robert Plant</TD></TR><TR><TD>Tim Finn</TD></TR>
<TR><TD>James Taylor</TD></TR><TR><TD>Bryan Adams
</TD></TR><TR><TD>Ian Gillen</TD></TR><TR><TD>
Mick Jagger</TD></TR><TR><TD>Neil Young</TD></TR>
<TR><TD>Bob Dylan</TD></TR></TABLE>

It is possible to put the SQL query into a file and then refer to the file on the command line. For example—and assuming the above query is in a file called name.sql—this command line produces the same HTML table:


mysql  -H  -u  manager  -ppwhere  CLUB  <  name.sql

Knowing this much, I figured that if I could come up with a means of issuing the HTML-producing command line from a Web interface, I'd be most of the way toward providing my Web reporting solution. So, I wrote a small CGI script in Perl to execute the command line for me.

The CGI Script

The strategy employed by my simple CGI script is straightforward: after determining the name of the query to execute, a command line is constructed and then issued by the CGI script. Any results produced from executing the command line are put inside the body part of the HTML page that the CGI script produces.

After the usual Perl startup lines, the runquery.cgi script starts by defining a series of constant values:

#! /usr/bin/perl -w

use strict;

use constant MYSQL  => '/usr/bin/mysql';
use constant USERID => 'manager';
use constant PASSWD => 'pwhere';
use constant DBNAME => 'CLUB';

The location for the MySQL client on your computer may be different from where I have mine, so change the MYSQL constant value if need be. Also, note that I'm hard-coding the values for the database user (USERID), the password (PASSWD) and the database that is to be queried against (DBNAME). Although this may not be the best practice, I am going to explain it away by saying that this is the dirty part of my quick-and-dirty solution. With the constants defined, I indicate that I'm going to use the standard interface to Perl's CGI programming technology:

use CGI qw( :standard );

Two Perl scalars then are defined, taking their value from any parameters passed from a Web interface to the CGI script. The first parameter, called query, identifies the SQL file to use, while the second, called title, provides a report title to use when displaying results:

my $query = param( 'query' );
my $title = param( 'title' );

The script then creates the command line that runs the query through the MySQL client program. Note that Perl's dot operator is used to concatenate strings:


my $cmdline = MYSQL .
              ' -H -u ' .
              USERID .
              ' -p' .
              PASSWD .
              ' ' .
              DBNAME .
              "< $query ";

The script then starts to build an HTML page. The header function generates the correct Content-Type header, and the start_html function starts to create the HTML page using the value provided for the page's title:

print header;
print start_html( -title => $title );

The next line of code uses Perl's qx operator to execute the command line and return any resulting output from its execution to a variable, called $results:

my $results = qx/ $cmdline /;

The rest of the script adds an HTML level 3 heading to the Web page, together with the query results and an HTML link to the reports page. The end_html function finishes the HTML page generation and concludes the script:


print "<h3>$title</h3>";

print $results;

print p, "Return to the ",
      a( { -href => "/Club/Reports.html" },
      "List of Reports" );

print end_html;

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re:Web Reporting with MySQL, CSS and Perl

Clement Yap's picture

Hi Paul,

I thank you for your generous tip on how we can convert the mySQl results into html in a breezy way.

However I would like to know how I can produce results in html that looks much like command line client, that is the results is bordered by plus, pipe and dash symbols.

Pls advise
Thanks in advance
Clement

PERLwebinteface

ayesha's picture

hy its realy v informative..i actually have to create web interface using perl..have made a database of hospitalsin SQL and now have to create web interface using perl and html..could u plz help me in that

EXCELLENT HELP

Tommy Burle's picture

Hi Paul

Tommy Burke from your 2004 Networking class here. Stumbled across this when re-searching a current reporting project for work. It has greatly helped me in getting a headstart on the code and the above makes it a hell of a lot easier to work with the query results rather than parsing through each rown and formatting them into a html page.. :-)

Now if i could only get these damn admins to install CGI,DBI and DBD:Mysql faster!!

Once more an excellent piece of work by you

Regards
Tommy

Web Reporting with MySQL, CSS and Perl

Ashok's picture

Dear Barry,
The teaching style is simply marvelous.
You have consistently simplified a complex task by breaking it into small easy to follow steps.
Though I have no intentions of implementing such an arrangement, I read through the entire article as it easy easy to understand.
Congratulations.

Ashok

Nice comments, thanks!

barryp's picture

Shucks ... I'm blushing. Very pleased you liked it.

--
Paul Barry
IT Carlow, Ireland
http://glasnost.itcarlow.ie/~barryp

Paul Barry

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState