Home ›

Web Reporting with MySQL, CSS and Perl

Issue 149

From Issue #149
September 2006

Aug 28, 2006  By Paul Barry
 in
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
Submitted by Clement Yap (not verified) on Tue, 06/16/2009 - 21:53.

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
Submitted by ayesha (not verified) on Thu, 02/19/2009 - 09:59.

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
Submitted by Tommy Burle (not verified) on Thu, 12/21/2006 - 14:59.

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
Submitted by Ashok (not verified) on Tue, 11/14/2006 - 10:53.

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
Submitted by barryp on Mon, 02/05/2007 - 06:20.

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

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

Paul Barry

White Paper
More Resources
Fabric-Based Computing Enables Optimized Hyperscale Data Centers

Today’s modular x86 servers are compute-centric, designed as a least common denominator to support a wide range of IT workloads. Those generic, virtualized IT workloads have much different resource optimization requirements than hyperscale and cloud applications. They have resulted in a “one size fits all” enterprise IT architecture that is not optimized for a specific set of IT workloads, and especially not emerging hyperscale workloads, such as web applications, big data, and object storage. In this report, you will learn how shifting the focus from traditional compute-centric IT architectures to an innovative disaggregated fabric-based architecture can optimize and scale your data center.

Learn More

Sponsored by AMD

White Paper
More Resources
Red Hat White Paper: Using an Open Source Framework to Catch the Bad Guy

Built-in forensics, incident response, and security with Red Hat Enterprise Linux 6

Every security policy provides guidance and requirements for ensuring adequate protection of information and data, as well as high-level technical and administrative security requirements for a system in a given environment. Traditionally, providing security for a system focuses on the confidentiality of the information on it. However, protecting the data integrity and system and data availability is just as important. For example, when processing United States intelligence information, there are three attributes that require protection: confidentiality, integrity, and availability.

Learn more about catching the bad guy in this free white paper.

Learn More

Sponsored by DLT Solutions