How to use a database with Perl
Connecting to a database is a very common task for cgi scripts but it can be a very involved process. Below we’ll step through connecting to, inserting into and querying a database and displaying back the results.
Connecting to the database
If you haven’t already created a MySQL database you can follow the How to create a MYSQL database guide. For the purpose of this example we’ll use the database name my_database, remember to replace this with your actual database name.
To make connecting to and using the database easier we’re going to use the Perl DBI module. Lets start at the top of our script with the location of the Perl interpretor and then we tell Perl we want to use the DBI module and then create a DBI object.
#!/usr/bin/perl
use DBI;
my $database_name = "my_database";
my $database_host = "mysql.lcn.com";
my $database_user = "username";
my $database_password = "password";
my $dbh = DBI->connect("dbi:mysql:$database_name:$database_host", $database_user, $database_password) or die "Unable to connect: $DBI::errstrn";
The DBI module is object-orientated, this means all of the DBI functions and data are accessed through an instance of DBI, in our script this instance is called $dbh, short for database handle.
Lines 4 to 7 – Defines local variable to store our connection data, make sure to update them with your actual database details.
Line 9 – Creates the DBI object by calling the connect function using the variables we defined, if the connection fails the script will die.
Create a table
Lets create a simple contacts table and put some details in it. We’ll store first name, surname and email address.
my $sql = "CREATE TABLE IF NOT EXISTS contacts ( id INT NOT NULL AUTO_INCREMENT,PRIMARY KEY(id), first_name VARCHAR(64), surname VARCHAR(64), email VARCHAR(255))"; my $sth = $dbh->prepare($sql); $sth->execute() or die "SQL Error: $DBI::errstrn";
Lines 10 to 15 – We build a string of SQL to create our table. On line 18 we prepare our SQL statement and assign it to a new local varible $sth, short for statement handle.
Line 18 – We call the execute function on our $sth object and pass it our SQL string to actually create the table on the database.
Insert data into the database
Next we’ll put some data into our new table.
$sth = $dbh->prepare("INSERT INTO contacts(first_name, surname, email) VALUES(?,?,?)");
$sth->execute("Nick", "Jones", "nick.jones@foo.com");
$sth->execute("Matt", "Smith", "matt.smith@bar.com");
$sth->execute("Matt", "Brown", "matt.brown@baz.com");
Line 20 – Here we prepare our new SQL statement, notice at the end of the statement we have a set of question marks. These are used as place-holders for the actual values we will put in when we execute the statement.
Lines 21 to 23 – Executes our SQL statement passing in three values for first name, surname and email to replace the place-holders.
Query the database
Now that we’ve added some data into our database lets query it for all contacts whose first name is Matt.
$sth = $dbh->prepare("SELECT first_name, surname, email FROM contacts WHERE first_name = ?");$sth->execute("Matt");
Again, we prepare our SQL statement and use a place-holder for the value of our WHERE clause which tells the database to return all contact where the first_name field equals the value we pass in when we execute our query.
Display back database results
Finally, we want to display this data back in a web page.
print "Content-type: text/plain\n\n";
print &rlaquo;<HTML_PAGE;
<html>
<head>
<title>Database Test</title>
</head>
<body>
<h1>Database Test</h1>
<table>
<tr>
<th>First Name</th>
<th>Surname</th>
<th>Email</th>
</tr>
HTML_PAGE
while( $row = $sth->fetchrow_hashref ) {
print "<tr><td>$row->{first_name}</td>";
print "<td>$row->{surname}</td>";
print "<td>$row->{email}</td></tr>";
}
print <<HTML_PAGE;
</table>
</body>
</html>
HTML_PAGE
First print back the HTTP header to let the web browser know what type of content to expect. Then we print out the first part of the HTML page. We use a while loop to iterate over all the results returned by calling the fetchrow_hashref function on our $sth object, for each result we print out the field data and HTML for a table cell.
The final script
This example script shows a very basic way to get form contents emailed to you, it doesn’t however have the refinements of a professional script, e.g. input validation. Below is the finished script. We’ve added some comments (lines beginning with #) to help make it clearer.
#!/usr/bin/perl
use DBI;
# Define database details
my $database_name = "my_database";
my $database_host = "mysql.lcn.com";
my $database_user = "username";
my $database_password = "password";
# Connect to database and create DBI object
my $dbh = DBI->connect("dbi:mysql:$database_name:$database_host", $database_user, $database_password) or die "Unable to connect: $DBI::errstrn";
# Create contacts table
my $sql = "CREATE TABLE IF NOT EXISTS contacts (
id INT NOT NULL AUTO_INCREMENT,
PRIMARY KEY(id),
first_name VARCHAR(64),
surname VARCHAR(64),
email VARCHAR(255))";
my $sth = $dbh->prepare($sql);
$sth->execute() or die "SQL Error: $DBI::errstrn";
# Insert data into the table
$sth = $dbh->prepare("INSERT INTO contacts(first_name, surname, email) VALUES(?,?,?)");
$sth->execute("Nick", "Jones", "nick.jones@foo.com");
$sth->execute("Matt", "Smith", "matt.smith@bar.com");
$sth->execute("Matt", "Brown", "matt.brown@baz.com");
# Select data from the database
$sth = $dbh->prepare("SELECT first_name, surname, email FROM contacts WHERE first_name = ?");
$sth->execute("Matt");
# Print out HTTP header
print "Content-type: text/html\n\n";
print <<HTML_PAGE;
<html>
<head>
<title>Database Test</title>
</head>
<body>
<h1>Database Test</h1>
<table>
<tr>
<th>First Name</th>
<th>Surname</th>
<th>Email</th>
</tr>
HTML_PAGE
# Loop over database query results
while( $row = $sth->fetchrow_hashref ) {
print "<tr>$row->{first_name}</td>";
print "<td>$row->{surname}</td>";
print "<td>$row->{email}</td></tr>";
}
print <<HTML_PAGE;
</table>
</body>
</html>
HTML_PAGE
Replace the example database name, username and password, save this script as database_test.cgi and upload it to the cgi-bin on your web hosting. Make sure you set the file permissions for the script to 755 as described above.
Now you’re ready to test your database script. Load up your database test in your browser, http://www.domain.com/cgi-bin/database_test.cgi. If everything works you should see a html page show a table of contacts. If not, try checking out our guides below.