Download this (Unix text) file

#!/usr/bin/perl -w

# Show a postcard from the postcards table in the hcc database
# using CGI, Perl, and DBI.
#
# (C)1999 by Wayne Pollock, Tampa Florida USA.  All Rights Reserved.
#
# Adapted by Wayne Pollock from an article in "Linux Journal", October 1997
# (Issue #42), pp. 106-111, "Integrating SQL with CGI, Part 1" by
# Reuven M. Lerner.  The Linux Journal is published by SSC, Inc., Seattle, WA.
# For reprint permissions contact linux@ssc.com.

# Correct use of this CGI script is to display an HTML form, where the
# action URL of this script.  The form should have a single input field,
# "keywords", which should be an ID number for a postcard, e.g., "12345".
#
# Note the use of the HTML tag "<ISINDEX>" as a quick-and-dirty form!

# Show which modules to use: (Perl has literally hundreds of modules!)
use strict;
use diagnostics;
use CGI;
use DBI;

# Define the database we want to talk to:
# (Syntax: DBI:<driver>:<database_name>:<host>:<port>
# where <driver> is 'mysql', <database_name> is 'hcc',
# and the <host> and <port> are optional (defaults to the
# standard port on 'localhost' if omitted).  Note that mySQL
# comes "out of the box" with a "test" database that has
# no security restrictions.  To use it, create a table 'postcards'
# and change "hcc" to "test".
my $db = 'DBI:mysql:hcc';

# Define the directory where the graphic files are
# (Relative to the DocumentRoot of the web server):
my $graphics_dir = "/graphics";

# Declare some global variables:
my ($sender_name, $sender_email, $recipient_name,
    $graphic_name, $postcard_text) = ("", "", "", "", "");

# Create a CGI object:
my $cgi = new CGI;

# Send the content type header for the reply:  (This is always the same
# whether we return the postard or an error message.)
print $cgi->header( "text/html" );

# Get the postcard ID number from the form data:  (If missing,
# $id will be set to undef.)
my $id = $cgi->param( "keywords" );

if ( (not defined $id) or ($id =~ /\D+/) )
{
   print $cgi->start_html( -title => "Enter Postcard ID" );
   print "<P> Please provide a postcard ID number in order ";
   print "to fetch a card!\n";
   print "<ISINDEX>\n";
   print $cgi->end_html;
   exit;
}

# Connect to the database (via sockets):  (dbh: DataBase Handle)
# (The arguments to connect are database, username, password.
# The last two arguments are optional and default to 'undef'.)
my $dbh = DBI->connect( $db, 'www', '' )
   or die "Error on connect, DB=\"$db\": $DBI::errstr ($DBI::err)";

# Create the SQL Query:
my $sql = "select sender_name, sender_email, ";
$sql .= "recipient_name, graphic_file, ";
$sql .= "postcard_text from postcards, graphics ";
$sql .= "where id_number = $id and graphic_id = graphics.id";

# Create the SQL Statement:  (sth: STatement Handle)
my $sth = $dbh->prepare( "$sql" )
   or die "Error on prepare, SQL=\"$sql\": $DBI::errstr ($DBI::err)";

# Execute the query, and make sure it worked:
$sth->execute
  or die "ERROR with SQL command \"$sql\" on \"$db\": $sth->errstr ($sth->err)";

# Report an error if no rows (or more than one row) of data are returned:
if ( $sth->rows != 1 )
{  print $cgi->start_html( -title => "Invalid Postcard ID!" );
   print "<P> The ID number you provided does not match any postcard ";
   print "currenlty in the hcc database.&nbsp; Please try again.\n";
   print "<ISINDEX>\n";
   print $cgi->end_html;
   exit;
}

# Fetch the data from the returned result set into variables:
($sender_name, $sender_email, $recipient_name,
 $graphic_name, $postcard_text) = $sth->fetchrow;

# Send the results back as HTML:
print $cgi->start_html( -title => "Postcard for $recipient_name" );
print "<IMG ALIGN=\"LEFT\" SRC=\"$graphics_dir/$graphic_name\"\n";
print "<H2 ALIGN=\"CENTER\">Message from $sender_name ";
print "(<A HREF=\"mailto:$sender_email\">$sender_email</A>): </H2>\n";
print "<P> $postcard_text\n";
print "<BR CLEAR=\"ALL\"><P><HR><P> Sent through the HCC.COM postcard system.&nbsp;\n";
print "Why not <A HREF=\"/cgi-bin/send-postcard.pl\">send your own postcard?</A> <BR>\n";
print "&copy;1999 by HCC.COM.&nbsp; All Rights Reserved.\n";
print "<BR>Contact:&nbsp; ";
print ( '<A HREF="mailto:webmaster@hcc.com">webmaster@hcc.com</A>', "\n" );
print $cgi->end_html;

# Clean up the statement handle (release memory):
$sth->finish;

# Disconnect fron the database:
$dbh->disconnect;

# All done, time to exit Perl:
exit;