Using Perl DBI to interface to MySQL

Why Perl? Why not PHP? After all, PHP4 is supposed to be fast now that it's using a new interpreter.

Because Perl is still what's most widely used. I like going with the industry standard. Perl has massive support online and the most books written about it. There is CPAN, the Comprehensive Perl Archive Network, where you'll find modules to do almost anything you'd want. DBI gives you an interface that'll work with the "primitive" CSV format (comma separated value text files) all the way up to the highest end Oracle RDBMS.

Let's start by connecting to the database:

use DBI;

my $dsn = 'DBI:mysql:my_database:localhost';
my $db_user_name = 'admin';
my $db_password = 'secret';
my ($id, $password);
my $dbh = DBI->connect($dsn, $db_user_name, $db_password);

Let's assume we've received as form input a nickname and password from a login screen. So right now,

$input_nickname = 'Cowlick' and $input_password = 'udder'

We want to verify that the entered password matches what we have in our database.

my $sth = $dbh->prepare(qq{
select id, password from users
where nickname = $input_nickname

Notice there is no command-terminating semi-colon.

How do we get the results? Since we only expect one row,

($id, $password) = $sth->fetchrow_array();
$sth->finish(): # we're done with this query
if ($input_password eq $password) # case-sensitive
... # login successful

What if our result is more than one row? Successive calls to


will return the rest of the result set.

my $sth = $dbh->prepare(qq{
select nickname, favorite_number from users
while (my ($nickname, $favorite_number) =
$sth->fetchrow_array()) # keep fetching until
# there's nothing left
print "$nickname, $favorite_number\n";

If we want to save the entire result set first for processing later,

my (@matrix) = ();
while (my @ary = $sth->fetchrow_array())
push(@matrix, [@ary]); # [@ary] is a reference

A reference, for C programers, can be thought of as a pointer. The Matrix is now an array of array references, or a two-dimensional array.

You can access row $i with:


Or, you can access a specific row and column ($i, $j) in the table with:


For MySQL operations that don't return a result you can use the do method instead of prepare then execute.

$dbh->do("insert into message_votes 
(message_id, user_id, vote) values (1, 3, 'good')");

When you're done with the database:

That should be enough to get you started. You can see that using Perl DBI is a matter of calling a method with the MySQL command as a string.