CGI and Perl

Create an HTML Index from a Database

Listing 8.3 uses DBI to query the database and extract the call_num, title, and subtitle fields. As it extracts the data from the database, the fields are embedded into HTML to create an index of all titles currently in the A/V library. Figure 8.1 shows the HTML output generated by this program. There are four main steps in the program in Listing 8.3.

  1. line 11 The driver (DBD) for mSQL is installed.

  2. line 12 DBI is instructed to connect to the database named AV_LIBRARY.

  3. line 14 After you connect to the database, a prepare function must be invoked to set up return values before the execute statement is invoked.

  4. line 17 The execute statement pulls the values out of the database, and in this example embeds them in an HTML list.

Listing 8.3. Program that writes HTML from the database

1:   #!/usr/local/bin/perl
 2:   #
 3:   #  Build catalog.html from the catalog table in the AV_LIBRARY database
 4:   #
 5:   #    by Tom White
 6:   #
 7:   #    $Id: build_ful.pl,v 1.2 1996/08/28 13:39:51 avlibrar Exp avlibrar $
 8:   use DBI;
 9:   $target = "/home1/ces/avlibrar/public_html/catalog.html";
 10:  open (NEWF,">$target.new") || die "Could not open $target: $!";
 11:  $drh = DBI->install_driver( `mSQL' ) || die "Could not install: $DBI::errstr";
 12:  $dbh = $drh->connect( ``, `AV_LIBRARY' )
 13:            || die "Could not connect: $DBI::errstr";
 14:  $sth = $dbh->prepare( "SELECT call_num, title, subtitle FROM catalog" )
 15:            || die "Could not start select: $DBI::errstr";
 16:  &header;
 17:  $sth->execute || die "Could not select: $DBI::errstr";
 18:  while ( ($call_num, $title, $subtitle) = $sth->fetchrow ) {
 19:    print NEWF qq!  <li><tt><a href="display.cgi?cn=$call_num">$call_num</a></tt>
 20:             $title\n!;
 21:    if ( $subtitle ) {
 22:      print NEWF " $subtitle\n";
 23:    }
 24:  }
 25:  $sth->finish || die "Could not finish select: $DBI::errstr";
 26:  &footer;
 27:  # $dbh->disconnect || die "Disconnect failed: $DBI::errstr";
 28:  close NEWF;
 29:  rename "$target.new", $target || die "Could not rename: $!";
 30:  exit;
 31:  sub header {
 32:    print NEWF <<EOD;
 33:  <HTML>
 34:  <HEAD>
 35:    <TITLE>MCES's Audiovisual Reference Room - Full Listing</TITLE>
 36:    <LINK REV="MADE" HREF="mailto:avlibrar\@ces.msstate.edu">
 37:    <META NAME="MarkUp" CONTENT="Tom White">
 38:  </HEAD>
 39:  <BODY>
 40:  <h1><img src="/pics/ces-b1mt.gif" width=499 height=53
 41:           alt="Cooperative Extension Service -
 42:                Mississippi State University">
 43:  </h1>
 44:  <h2>
 45:    Audiovisual Reference Room
 46:  </h2>
 47:  <h3>
 48:    Full Catalog Listing
 49:  </h3>
 50:  <ol>
 51:  EOD
 52:  }
 53:  sub footer {
 54:    my @loctime;
 55:    my $lastupdate;
 56:    @loctime = localtime;
 57:    $lastupdate = sprintf("%02d-%02d-%02d", ($loctime[4]+1),
 58:                    ($loctime[3]), ($loctime[5]));
 59:    print NEWF <<EOD;
 60:  </ol>
 61:  <HR>
 62:    [<a href="./">Audiovisual Reference Room</a>]
 63:    <br>
 64:    [<a href="/ces.html">Cooperative Extension</a>]
 65:    [<a href="http://www.msstate.edu/">Mississippi State</a>]
 66:    [<a href="http://www.msstate.edu/web/search.htm">Search MSU's Web</a>]
 67:    <br>
 68:    <FONT SIZE="-1">
 69:    For information about this page, contact
 70:       <a href="mailto:avlibrar\@ces.msstate.edu">avlibrar\@ces.msstate.edu</A>.
 71:    <br>
 72:    <b>Last modified:</b> $lastupdate
 73:    <br>
 74:    <A HREF="http://www.msstate.edu/web/disclaim.htm">Mississippi State University is
 75:  an equal opportunity institution.</A>
 76:    </FONT>
 77:  </BODY>
 78:  </HTML>
 79:  EOD
 80:  }

Output from program in Listing 8.3

Figure 8.1. Output from program in Listing 8.3.