Access your MySQL Database with Perl

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)
  Go to page: 1 2

  If you liked this article, share the love:

  • Save to Delicious
  Print-Friendly Version Email to a Friend Suggest an Article