Click here to Skip to main content
15,867,686 members
Articles / Web Development / CSS

Perl Guestbook

Rate me:
Please Sign up or sign in to vote.
2.50/5 (8 votes)
24 Mar 2004CPOL5 min read 500K   952   12   12
This article shows how to create a guestbook script using Perl. Data is stored in a MySQL table rather than in a flat file. DBI is used to connect to the database.

Sample Image - perlguestbook.gif

Sample Image - perlguestbook.gif

Introduction

There are a lot of Perl guestbooks out there, many of which store data in a flat file. Most of the guestbook scripts that use MySQL as a backend are written in PHP. This guestbook is for all you Perl users out there who want a guestbook that stores data in a database rather than in a flat file.

I also wrote this script to learn a little about DBI, the database independent interface for Perl. Though DBI provides an abstract interface to multiple database engines, I have used MySQL-specific SQL syntax. Therefore, this code isn't fully portable across different databases.

Also, though I will mention some DBI specifics, a full-blown DBI tutorial is beyond the scope of this article. For more information, visit the DBI homepage[^].

Features

  • Page navigation links when more than one page of entries exists (default is 10 entries per page)
  • Data is stored in a MySQL database
  • Displays post date, guest's name, location, email address, URL and message. Also tracks guest IPs.

MySQL Client Configuration File

In order to connect to a MySQL server from a Perl script, you must provide a valid MySQL username, password and hostname (among other things). For security purposes, I have chosen to store these data items in a file outside the document root of the web server. I called the folder includes. For example, if your document root is located at /home/joeuser/www/, you would store the file in /home/joeuser/includes/. Wherever you decide to put it, you must specify the path to it in the actual script.

The name of this file is perlgb.cnf, and it must have the following format:

[client]
host=localhost
user=your_username
password=your_password

host is usually localhost, unless you are connecting to a separate database server. user is your MySQL username and password is your MySQL password. You must edit these values to suit your configuration.

The perl_gb MySQL Table

perl_gb contains all the data associated with guestbook entries. The table contains the following fields:

message_id A unique, auto-incrementing, non-null integer
post_date The date and time that an entry is posted
first_name A guest's first name
last_name A guest's last name
city The city a guest lives in
state The state a guest lives in
country The country a guest lives in
email_addr A guest's email address
url A guest's URL (optional)
ip_address IP address of the remote user
comments Comments left by a guest

Note that all fields except url are required.

The Script

The following sections explain how I used DBI to retrieve and store data in MySQL.

Setting Up the Connection to MySQL

To connect to MySQL, we use the DBI->connect() method.

Connection Parameters

DBI needs four connection parameters to connect to MySQL: username, password, host and database name. The first three will be grabbed from the MySQL client configuration file, perlgb.cnf. You must specify the database name in the script, as shown below:

PERL
my($host_name, $user_name, $password) = (undef, undef, undef);
my($db_name) = "your_db_name";

Constructing the Data Source

After specifying the connection parameters, the next step is to construct the data source, represented by the $dsn variable in the code below. The type of database engine you use determines the format of the data source.
Note: The capitalization of DBI doesn't matter, but mysql must be in all lower-case.

PERL
my($dsn) = "DBI:mysql:$db_name";
$dsn .= ":hostname=$host_name" if $host_name;
$dsn .= ";mysql_read_default_file=/path/to/perlgb.cnf";

If you don't specify a hostname, MySQL defaults to localhost. Note that in the last line, you must specify the path to your perlgb.cnf file.

Connecting to the Server

RaiseError => 1 tells DBI to check for database-related errors and to print a message and exit whenever it detects one.

PERL
my(%attr) = (RaiseError => 1);
my($dbh) = DBI->connect($dsn, $user_name, $password, \%attr);

If the call to connect() succeeds, a connection handle is returned. Following convention, I have named this $dbh. We can use this handle to run queries against tables in the database we specified.

Running a Query Against MySQL using DBI

Retrieving Data

To retrieve data from MySQL, you must create a statement handle. Following convention, I have named this $sth in the code. Passing a SQL SELECT query to $dbh->prepare() returns a statement handle for our use. We must call $sth->execute() in order to actually run the query. In the example below, we are counting the total number of guestbook entries in the perl_gb table.

PERL
my($sth, $count);

# Issue the query
$sth = $dbh->prepare(qq{
    SELECT COUNT(message_id) FROM perl_gb
});
$sth->execute();

# Read the results of the query, then clean up.
$count = $sth->fetchrow_array();
$sth->finish();
$count = "(Couldn't obtain count)" if !defined($count);

Calling $sth->fetchrow_array() returns the first row returned by the query. In this case, the query only returns one row with one field, so we can store the value in a scalar rather than an array. If the query returned a row with more than one field, we would have to store the row in an array variable.

To retrieve multiple rows, you must call $sth->fetchrow_array() in a loop. fetchrow_array() will return undef when there are no more rows to return.

$sth->finish() frees up any resources associated with the query we just ran.

Inserting Data

Inserting data using DBI is straightforward. For this, we use the do() method. do() prepares the statement and executes the query, all in one step. This allows us to skip the step of using a statement handler. do() returns the number of rows affected by the query we pass to it.

PERL
my($myQuery);
$myQuery  = "INSERT INTO perl_gb (first_name,last_name," .
  "city,state,country,email_addr,url,ip_address,comments)";
$myQuery .= " VALUES ($first_name, $last_name, " .
  "$city, $state, $country, $email_addr, $url," . 
  " $ip_address, $comments)";
my($rows) = $dbh->do(qq{$myQuery});

The Rest of the Script

The rest of the script is straightforward Perl. There is nothing tricky about the code, so I won't discuss it further.

Limitations

  • Designed for MySQL - MySQL-specific SQL extensions are used, so this won't run on other databases without modification.
  • Designed for Unix/Linux - However, with slight modification, it will run on Windows boxes.
  • No Admin Panel - I am content to modify the style by hand. However, if there is enough demand, I will build an admin panel.

References

Books

  • Christiansen, Tom, and Nathan Torkington. Perl Cookbook. Sebastopol, CA: O'Reilly & Associates, Inc., 1998.
  • DuBois, Paul. MySQL. Indianapolis, IN: New Riders Publishing, 2000.
  • Schwartz, Randal L., et al. Learning Perl on Win32 Systems. Sebastopol, CA: O'Reilly & Associates, Inc., 1997.
  • Wall, Larry, et al. Programming Perl. Sebastopol, CA: O'Reilly & Associates, Inc., 1996.

Websites

Acknowledgements

A long time ago, I grabbed home.gif and email.gif off of a web site, but I can't for the life of me remember where. If you know where they originated, I will gladly give credit where credit is due.

History

  • 1.00 - 25 Sep 2002: Initial public release
  • 1.01 - 16 Nov 2002: HTML in the URL is now escaped. Thanks to moliate [^] for pointing that out.
  • 1.01 - 25 Mar 2004: Removed the live demo link from this page because I changed to a Windows Web server. No code changes were made, hence the version number wasn't incremented.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer (Senior) Sagara Software, Inc.
United States United States
Jon is a senior software developer who loves using .NET to solve problems.

When he's not fooling around with computers or reading, he's busy spending time with his super wife, Kelly, and his three boys. He also likes to take his mountain bike for a spin.

Visit my blog

Comments and Discussions

 
GeneralNEED HELP Pin
kavi_198019-Mar-06 21:51
kavi_198019-Mar-06 21:51 
GeneralRe: NEED HELP Pin
jc_cpu25-May-06 16:33
jc_cpu25-May-06 16:33 
GeneralPerl Guestbook Pin
Danelkits8-Jan-15 0:13
Danelkits8-Jan-15 0:13 
Generalhelp needed Pin
techankit1-Apr-05 23:17
techankit1-Apr-05 23:17 
GeneralRe: help needed Pin
Jon Sagara3-Apr-05 13:02
Jon Sagara3-Apr-05 13:02 
Generalperl forums Pin
Anonymous5-Aug-03 18:16
Anonymous5-Aug-03 18:16 
QuestionWeb server? Pin
Kant22-Mar-03 7:44
Kant22-Mar-03 7:44 
AnswerRe: Web server? Pin
Jon Sagara22-Mar-03 8:26
Jon Sagara22-Mar-03 8:26 
GeneralJust one question Pin
yarp14-Oct-02 0:14
yarp14-Oct-02 0:14 
GeneralRe: Just one question Pin
Jon Sagara14-Oct-02 5:33
Jon Sagara14-Oct-02 5:33 
GeneralRe: Just one question Pin
yarp14-Oct-02 7:21
yarp14-Oct-02 7:21 
GeneralRe: Just one question Pin
Jon Sagara14-Oct-02 8:23
Jon Sagara14-Oct-02 8:23 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.