|
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
});
$sth->execute();
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
$sth->fetchrow_array()
will return the rest of the result set.
my $sth = $dbh->prepare(qq{
select nickname, favorite_number from users
});
$sth->execute();
while (my ($nickname, $favorite_number) =
$sth->fetchrow_array()) # keep fetching until
# there's nothing left
{
print "$nickname, $favorite_number\n";
}
$sth->finish();
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
}
$sth->finish();
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:
@{matrix[$i]}
Or, you can access a specific row and column ($i,
$j) in the table with:
$matrix[$i][$j]
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:
$dbh->disconnect();
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.
For more in-depth information regarding Perl DBI and MySQL refer to the book
MySQL (OTHER
NEW RIDERS) by Paul Dubois. Great reviews on Amazon convinced me to buy
it and while I can't quite rave about the book, because it's hard to find what
you want most of the time, it seems very competently written. Of course, I have
no frame of reference since this is the only MySQL book I've looked at.
 For
the best all-around CGI book out there, you must get the excellent CGI
Programming with Perl (2nd Edition) published by O'Reilly. You should know
some Perl before starting in on this but you'll learn more than a little bit
about everything CGI by the time you've finished. Plenty of relevant examples
and excellent sections on Security, DBI, and Maintaining State make this book
a must have. Disregard the bad reviews on Amazon unless you're the kind
of person who likes to type in the examples from the book, in which case, you
will have a tough time. Use it instead to learn the concepts and then
write your own code!
|
