Access your MySQL Database with Perl

One of the most common Perl-related questions at the SitePoint Forums is, “How do I access my database with Perl?” Perl can work with nearly every type of database on the market, but today I’ll use MySQL as an example.

For the purposes of this demonstration, let’s say we have a database named “shoes” with one table named “subscribers”. Here’s the layout of the table:

create table subscribers (
uid int not null primary key auto_increment,
username varchar(32) not null,
emailaddr varchar(32) not null
);
We will connect to MySQL with the DBI modules and the DBD::mysql database driver. These modules don’t come with the standard Perl distribution, so you’ll have to obtain them yourself. If you maintain your own server, follow the instructions below, but if you have a Web host, they should be able to take care of the installation for you, if they don’t already have the modules installed.

Install the Modules

To install the DBI and DBD::mysql modules on a Unix-like OS or Perl on Windows installed as part of Cygwin, run the following commands.

Note that you must have MySQL already installed or you will not be able to install the DBD::mysql module. Also, if you haven’t installed any other modules this way, you’ll be asked for configuration options. Luckily, these are self-explanatory. Once you’ve finished entering the configuration values, the module installation will start.

perl –MCPAN –e 'install DBI'
perl –MCPAN –e 'install DBD::mysql'
You’ll see several screens of text fly by, but eventually it should return you to the friendly command prompt.

There will rarely be any errors, because these modules have been tested by thousands of people. However, if you are unlucky enough to encounter errors, a quick search on Google will probably find you the solution.

Work with the Database

Now that the modules and database drivers are installed, we can progress to the actual Perl code to work with the database. For now we’ll just connect to the database.

# set the data source name
# format: dbi:db type:db name:host:port
# mysql's default port is 3306
# if you are running mysql on another host or port,
#    you must change it
my $dsn = 'dbi:mysql:shoes:localhost:3306';
 
# set the user and password
my $user = 'user';
my $pass = 'pass';
 
# now connect and get a database handle  
my $dbh = DBI->connect($dsn, $user, $pass)
 or die "Can't connect to the DB: $DBI::errstrn";
OK, there were a lot of new things in that code. First, we defined the DSN, or data source name. This tells the DBI where to find your database server, and what database you will operate on. Then, we set the username and password to connect to the database as. Finally, we call DBI->connect to actually connect to the database (don’t forget to enter your own username and password!), and if the connection was unsuccessful, we print out an error. From that command, we get a database handle, which we will use to run queries on the database.

The execution of a query takes two steps: preparation and execution. First, you must prepare the query like this:

my $sth = $dbh->prepare('insert into subscribers(username, emailaddr)
values "jim", "jim@microsoft.com")');

Then you can execute it:

$sth->execute();
Because we ran an INSERT query, there weren’t any results to retrieve. But what if we used SELECT? How would we get the data? Like this:

my $sth = $dbh->prepare("select username, email from subscribers");
$sth->execute;
 
while(@row = $sth->fetchrow_array()) {
 print "$row[0]: $row[1]<br>";
}
As you can see, $sth->fetchrow_array returns an array of the results. If you like, you could write the loop like this to make it a bit more readable:

while(my($username, $email) = $sth->fetchrow_array()) {
 print "$username: $email<br>";
}
Instead of assigning the values to a normal array, this assigns the rows’ values each to a different variable.

Build the Web App

Now let’s use the DBI to create a (somewhat) useful Web application to manage catalog subscribers of a fictitious shoe store. There will be two pages, view.pl and add.pl, and the database schema will be the same as the one at the top of this guide. Again, remember that you have to enter your own username and password in these scripts instead of the default “user” and “pass”.

Here is add.pl:

use CGI;  

use DBI;  

   

print CGI::header();  

   

my $username = dbquote(CGI::param('name'));  

my $email    = dbquote(CGI::param('email'));  

   

unless($username) {  

  print <<PAGE;  

<h1>Add a Subscriber</h1>  

<form action=add.pl method=post>  

Name: <input type=text name=name><br>  

Email: <input type=text name=email><br>  

<input type=submit value="Add Subscriber">  

</form>  

PAGE  

   

exit;  

}  

   

# connect  

my $dbh = DBI->connect("dbi:mysql:shoes:localhost:3306", "user", "pass");  

   

# prepare the query  

my $sth = $dbh->prepare("insert into subscribers(username, emailaddr)  

 values('$username', '$email')");  

   

# execute the query  

$sth->execute();  

   

print <<PAGE;  

<h1>User Added</h1>  

The user $username was just added.  Want to   

<a href=add.pl>add another</a>? PAGE  

   

sub dbquote {  

  my($str) = @_;  

    

$str =~ s/"/\"/g;  

  $str =~ s/\/\\/g;  

  $str =~ s/'/\'/g;  

   

  return $str;  

} 

And here is view.pl:

use DBI;  

use CGI;  

   

print CGI::header();  

   

print "<h1>Subscribers</h1>";  

   

# connect  

my $dbh = DBI->connect("dbi:mysql:shoes:localhost:3306", "user", "pass");  

   

# prepare the query  

my $sth = $dbh->prepare("select username, emailaddr from subscribers");  

   

# execute the query  

$sth->execute();  

   

while(my ($username, $email) = $sth->fetchrow_array()) {  

  print "$username: $email<br>n";  

} 

That's it!  If you're after more information, try these resources:

• Programming the Perl DBI (O’Reilly)
  
• Perl in a Nutshell (O’Reilly)
  
• The DBI documentation (perldoc DBI)

Frequently Asked Questions (FAQs) about Accessing MySQL Database with Perl