Accessing the MySQL Database with Perl

MySQL database schemas can be accessed from Perl programs. Perl has an extension, DBI, that is a database-independent interface for Perl. This Note shows you how to use DBI to connect to MySQL databases.

For information about the School's MySQL database server and to learn about your MySQL account and database schema, see MySQL in the School of Computer Science & Informatics

MySQL from Perl with DBI

DBI provides the programmer with a consistent interface into different databases through the use of DBI drivers. The driver for MySQL is called DBD::mysql.

For details, search for DBI or DBD::mysql at http://search.cpan.org/.

The following program, which uses the School's MySQL Sample Database, shows how to issue an SQL SELECT command to retrieve data from a database, and how to collect and print the retrieved data.

#!/usr/bin/perl
use DBI;

# connect to MySQL...
$driver= "mysql";
$dsn = "DBI:$driver:database=sample;host=csmysql.cs.cf.ac.uk";
$dbh = DBI->connect($dsn, "exampleuser", "passabc");

# prepare and execute the SQL statement
$sth = $dbh->prepare("SELECT s.name, o.osname, p.pname
       FROM systems s, opsystem o, people p
       WHERE s.oscode=o.oscode and s.ownercode = p.pcode");
$sth->execute;

# retrieve the results
print "Name\tOwner\tOperating System\n";
while(  my $ref = $sth->fetchrow_hashref() ) {
	print $ref->{'name'};
	print "\t";
	print $ref->{'pname'};
	print "\t";
	print $ref->{'osname'};
	print "\n";
}
exit;

When this program is run, it produces the following output:

Name    Owner   Operating System
green   John    MacOSX
blue    Jane    Windows
red     Jane    Linux
brown   Arthur  Linux
pink    Sally   Linux

MySQL through the Web with Perl::CGI

You can use the Perl CGI interface, in addition to DBI, to write a CGI script which can be run on your web site. For example, here is a Perl CGI script which prompts for a MySQL account name and password. Once it has these, it runs the SELECT command to retrieve the data.

#!/usr/bin/perl
use CGI;
use DBI;

# start a new CGI session
$q = new CGI;

# output the CGI header
print $q->header;

# output some HTML to set up the page
print "<title>Perl CGI using DBI to acccess MySQL</title>\n";
print "<h1>Perl CGI using DBI to acccess MySQL</h1>\n";

# are the user name and password assigned?
if( $q->param('username') ne "" && $q->param('password') ne "" ) {
	# yes. Let's see if they work
	$driver= "mysql";
	$dsn = "DBI:$driver:database=sample;host=csmysql.cs.cf.ac.uk";
	$dbh = DBI->connect($dsn, $q->param('username'), $q->param('password'));
	# if the user name and password are bad, then $dbh is null
}

if( $q->param('username') eq "" || $q->param('password') eq ""
                                                || $dbh eq "" ) {
	# the username and password are empty, or didn't work
	# prompt for them...
	print "<p>Enter the MySQL user name and password</p>\n";
	print $q->start_form;
	print "<table border=0>\n";
	print "<tr>\n";
	print "<td width=\"200\">\n";
	print "MySQL user name:\n";
	print "</td>\n";
	print "<td>\n";
	print $q->textfield('username');
	print "</td>\n";
	print "</tr>\n";

	print "<tr>\n";
	print "<td width=\"200\">\n";
	print "Password:\n";
	print "</td>\n";
	print "<td>\n";
	print $q->textfield('password');
	print "</td>\n";
	print "</tr>\n";

	print "<tr>\n";
	print "<td width=\"200\">\n";
	print $q->submit(-name=>'Action', -value=>'Login');
	print "</td>\n";
	print "<td>\n";
	print "&nbsp";
	print "</td>\n";
	print "</tr>\n";

	print "</table>\n";
	print $q->end_form;

	# and exit the CGI script
	exit;
}

# else we have logged in to MySQL
# run the SQL command
$sth = $dbh->prepare("SELECT s.name, o.osname, p.pname
         FROM systems s, opsystem o, people p
         WHERE s.oscode=o.oscode and s.ownercode = p.pcode");
$sth->execute;

# retrieve and output the result
print "<p>The database contains the following:</p>\n";
print "<table cols=3 border=1>\n";
print "<tr>\n";
print "<th>Name</th>\n";
print "<th>Owner</th>\n";
print "<th>Operating System</th>\n";
print "</tr>";
while(  my $ref = $sth->fetchrow_hashref() ) {
        print "<tr>\n";
        print "<td>", $ref->{'name'}, "</td>\n";
        print "<td>", $ref->{'osname'}, "</td>\n";
        print "<td>", $ref->{'pname'}, "</td>\n";
        print "</tr>\n";
}
print "</table>\n";
exit;

The script needs to be in a CGI location on the Web server. For example, create it in a file mysqltest in your cgi-bin directory on the project.cs.cf.ac.uk web site. Make sure the file protection mode is set to “executable” and access it using a web browser.

The web location will be something like https://project.cs.cf.ac.uk/SurnameA/cgi-bin/mysqltest.

Type exampleuser as the user name and passabc as the MySQL password into the fields, and click on Login. If you have typed correctly the following is displayed.