Web Reporting with MySQL, CSS and Perl

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

To run the script, you need to do two things: put the script in a place where your Web server can find it and put an SQL query into a file. On my Fedora Core 3 system running Apache 2, the /var/www/cgi-bin/ directory is used to hold the Web server's CGI scripts. So, I simply copy the CGI script into that location and make it executable:

cp  runquery.cgi  /var/www/cgi-bin/
chmod  +x  /var/www/cgi-bin/runquery.cgi

The above directory may not be the location used by your distribution for Web pages, so be sure to check first. As for a query, here's the contents of the file conditions.sql:

select player.name as 'Player',
       condition.name as 'Medical Condition'
from   player, condition
where  player.medical_condition = condition.id and
       player.medical_condition != 1;

The above SQL query joins the player and condition tables in order to list the names of each player together with his medical condition, assuming he has one. This query file also needs to be copied to the CGI directory on the Web server:

cp  conditions.sql  /var/www/cgi-bin/

To execute the query from the CGI script, type the following into your browser's address bar, substituting localhost with the name of your Web server:


http://localhost/cgi-bin/runquery.cgi?            \
          title=Results&query=conditions.sql

This URL produces the output shown in Figure 1, which, despite being a little plain, looks okay—but it could be nicer.

Figure 1. A Functional but Plain HTML Report

Making Things Look Nicer with CSS

To produce a report with an improved look and feel, I created a small cascading style sheet (CSS), called reports.css, to improve the general appearance of the produced report:

body {
    font-family:       sans-serif;
}

table {
    font-family:       sans-serif;
    background-color:  LIGHTYELLOW;
}

table th {
    background-color:  LIGHTCYAN;
    font-size:         75%;
}

h3 {
    font-family:       sans-serif;
    color:             BLUE;
}

As stylesheets go, mine is pretty simple. I declare a font for the text in my main body and then I fiddle with the font and background color of any tables that I put on my HTML page. The table headings are shown at 75% of the user's normal text size with a different background color from the data in the table. I then declare that my level 3 headings are colored blue.

The CSS file needs to be copied into the Web server's root directory so that my Web pages can find it:

cp  reports.css  /var/www/html

To use the CSS file, I changed the print start_html line from runquery.cgi to refer to the stylesheet, as follows:

print start_html( -title => $title,
                  -style => { -src => "/reports.css" } );

Reloading the query produces the output shown in Figure 2. It may not win me a Web design award, but it does look a whole lot better than the plain results shown in Figure 1.

Figure 2. A Much Improved HTML Report

Creating the Web Interface

This part of my solution was easy. All I needed was a simple Web page describing a list of reports. As with the generated reports, I use my simple stylesheet to improve the look of the reports page. Here's the HTML I used:


<HTML>
<HEAD>
    <TITLE>Soccer Club Reporting System</TITLE>
    <LINK rel="stylesheet" type="text/css"
          href="/reports.css" />
</HEAD>
<BODY>
<H3>Soccer Club Reporting System</H3>
Choose from one of these reports:
<OL>
    <LI>List players that have a
    <a href="/cgi-bin/runquery.cgi?
    title=Players with a Medical Condition&
    query=conditions.sql">Medical Conditions</a>
    <LI>List all players,
    <a href="/cgi-bin/runquery.cgi?
    title=Listing of all Players (Youngest First)&
    query=desc_dob.sql">youngest first</a>
</OL>
Return to the <A HREF="/Club">Soccer Club</A>
database system.
</BODY>
</HTML>

As shown above, each report is executed with two parameters: title, which provides a report description, and query, which identifies the SQL query file to run through MySQL. With my Web page created, I copied it into the root directory of the Soccer Club Web site:

cp  Reports.html  /var/www/html/Club/

When loaded into a Web browser, the reporting Web interface looks like that shown in Figure 3.

Figure 3. The Reporting Web Interface

At this point, I think I'm done. I have a simple Web interface to a standard report producing mechanism. If I write more queries, I can put them into their own SQL query file, copy the file to my cgi-bin directory and update my HTML reports Web page to invoke the query as required. My solution is quick-and-dirty and more than good enough.

Or is it?

The security of my solution is very, very poor. I need to worry about two things, protecting my CGI script and SQL query files from user tampering and protecting my system from the CGI script.

______________________

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

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix