What you should know

Basic Perl programming How the DBI Module and CGI modules work

Introduction
CGI is one of the ways that a site can generate dynamic HTML (DHTML) pages. These pages can contain simple information like the current date, or these can contain information taken directly from a database.

Use-ing Modules
To generate DHTML pages from a database, you will need two modules: DBI and the optional CGI modules. The DBI module is used to access the database while the CGI module assists you in generating your HTML page. To make things easier for you, I would suggest that you include the CGI module in your script.

Basic Assumptions
To help us in our topic, we will assume that we want to generate a page that lists all the people registered to our site.

• We will extract the Name column from the members table of the registration database.

• We will also assume that we are using a MySQL database.

• The database user is sample and the password is pwdsample.

Generating HTML code

Now that we have a template to read the database, the next thing to solve is to generate the HTML code to display the records. We do this using the CGI module.

To generate the HTML code, we need to first generate the header and then generate the HTML portion.

use CGI;
use strict;
my $c = new CGI;
print   $c->header,
        $c->start_html();

Getting the Records
Next, we have to do is to write a code that will: connect to the database prepare and execute the SQL statement to read the database records we need read the results of the SQL statement. To simplify things, we will use the fetchrow_array method of the DBI module. As we read each row from the database, we will display the row on the HTML page. To do this, we will have to code this:

use DBI;
# Connect to the Database
my $db = DBI->connect(
        'DBI:mysql:database=registration:host=localhost', 
        'sample', 
        'pwdsample');
# prepare SQL statement
my $sth = $db->prepare('SELECT Name from members order by Name');
# Execute SQL statement
if ($sth->execute() ) {
# Fetch a row at a time
        my @fld;
        while (@fld = $sth->fetchrow_array() ) {
                print "$fld[0]<br />\n";        
        }
}

Ending the HTML Page

To end the HTML, we generate the end part of the HTML page:

print $c->end_html;