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:
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);
$sth = $dbh->prepare(qq{
SELECT COUNT(message_id) FROM perl_gb
});
$sth->execute();
$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.
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.
- 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.
