Click here to Skip to main content
Click here to Skip to main content

Perl Guestbook

, 24 Mar 2004
Rate this:
Please Sign up or sign in to vote.
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 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:

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.

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.

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.

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.

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.

Web sites

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)

About the Author

Jon Sagara
Software Developer (Senior) Sagara Software, Inc.
United States United States
Jon graduated from Cal Poly with a B.S. Computer Engineering. He is currently building a Silverlight-based report scheduling interface for a pharmaceutical reporting company.
 
When he's not fooling around with computers or reading, he's busy spending time with his super wife, Kelly, his two boys, and their rambunctious dog, Homer.
 
Visit my blog
 
---
 
View my old profile pictures
Follow on   Twitter

Comments and Discussions

 
GeneralNEED HELP Pinmemberkavi_198019-Mar-06 21:51 
GeneralRe: NEED HELP Pinmemberjc_cpu25-May-06 16:33 
Generalhelp needed Pinmembertechankit1-Apr-05 23:17 
GeneralRe: help needed PinmemberJon Sagara3-Apr-05 13:02 
Generalperl forums PinsussAnonymous5-Aug-03 18:16 
QuestionWeb server? PinmemberKant22-Mar-03 7:44 
AnswerRe: Web server? PinmemberJon Sagara22-Mar-03 8:26 
GeneralJust one question Pinmemberyarp14-Oct-02 0:14 
GeneralRe: Just one question PinmemberJon Sagara14-Oct-02 5:33 
GeneralRe: Just one question Pinmemberyarp14-Oct-02 7:21 
GeneralRe: Just one question PinmemberJon Sagara14-Oct-02 8:23 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140709.1 | Last Updated 25 Mar 2004
Article Copyright 2002 by Jon Sagara
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid