Interfacing MySQL with PHP and Perl DBI

Summary

Perl and PHP can both be used to write dynamic Web pages that can connect and communicate with a MySQL server. PHP has built-in functions for the task, while Perl has a module called DBI for interfacing with databases like MySQL. This article demonstrates some simple examples of how to use PHP and Perl as separate front-ends for MySQL.

Note: You must install and start your MySQL server before you can retrieve data from it.

Included on this page:

MySQL Scripting With PHP
MySQL Scripting With Perl DBI
Resources

MySQL Scripting With PHP

Here is an example PHP script that connects to your MySQL server and prints out the first row of the user table from the mysql database:

<html>
<body>
<?php

// connect and select a database 
$link = mysql_connect("host.u.washington.edu:port number", "root", "rootpassword")
or die ("Couldn't connect:  Check to make sure that:<br>" .
		"<ul><li>your MySQL server is running</li>" .
		"<li>you used the correct hostname (<tt>vergil/ovid</tt>)<li>" .      
		"<li>you didn't forget the 'u' in the hostname</li>" .
		"<li>you added a colon with your port number after the hostname</li>" .
		"<li>you used the username 'root'</li>" .       
		"<li>you used the correct root password</li>" .
		"<li>you didn't forget to close a set of quotation marks</li><br><br>");
print "Connected successfully.<br>";
$db = "mysql";
mysql_select_db($db) or die("Could not select the database '" . $db . "'.  Are you sure it exists?");

// perform an SQL query 
$query = "SELECT * FROM user";
$result = mysql_query($query) or die("Query failed");

// print the result of the first row (row counting starts at zero)
printf("Host: %s<br>\n", mysql_result($result, 0, "Host"));                  
printf("User: %s<br>\n", mysql_result($result, 0, "User"));
printf("Grant privilege: %s<br>\n", mysql_result($result, 0, "Grant_priv"));

// free result set 
mysql_free_result($result);

// close the connection 
mysql_close($link);

?>
</body>
</html>

Be sure to replace host with either vergil or ovid, port number with the port your MySQL server is running on, and rootpassword with the root password you selected when you set up your MySQL server.

Save the file as "mysql-test.php" in your public_html directory. When you view the file in a Web browser, you should see something similar to the following:

Connected successfully.
Host: localhost
User: root
Grant privilege: Y

This tells you that your script successfully connected to your MySQL server. It then selected the database "mysql" that you configured when you installed MySQL, and then output the host, user, and grant privilege of the first row in the "user" table. Not very exciting, but if you haven't created any databases yet, this default mysql database is the only one you've got. To create your own, see Using SQL (Structured Query Language) with the Command-Line MySQL Client.

MySQL Scripting With Perl DBI

DBI (Database Interface) is a Perl module that was written to simplify interfacing with databases such as MySQL. The DBI module is already installed on the Web Development systems.

Here is an example CGI program written with Perl DBI; the script connects to your MySQL database and prints out the privileges of each user in the user table from the mysql database.

You can copy and paste the following sample program into a file called "example.cgi" and upload the file to a location in your Web directory. Be sure to make the appropriate substitutions for the highlighted portions of the script.


#!/usr/local/bin/perl

use DBI;

print "Content-type:text/html\n\n";

$db_handle = DBI->connect("dbi:mysql:database=mysql;host=hostname.u.washington.edu:port number;user=root;password=rootpassword")
or die "Couldn't connect to database: $DBI::errstr\n";

$sql = "SELECT * FROM user";
$statement = $db_handle->prepare($sql)
or die "Couldn't prepare query '$sql': $DBI::errstr\n";

$statement->execute()
or die "Couldn't execute query '$sql': $DBI::errstr\n";
while ($row_ref = $statement->fetchrow_hashref())
{
  print "User <b>$row_ref->{User}</b> has privileges on <b>$row_ref->{Host}</b>.<br>";
}

$db_handle->disconnect();

Tip: Do not put quotation marks around any single element in the parameter to DBI->connect; there should only be one set of quotation marks around the entire argument. Be sure to replace hostname with either vergil or ovid, port number with the port your MySQL server is running on, and rootpassword with the root password you selected when you set up your MySQL server.

Save the file as "mysql-pl-test.cgi" in your public_html directory, and set its file permissions to 755. When you view the file in a Web browser, you should see something similar to the following:

User root has privileges on localhost.
User root has privileges on %.washington.edu.

This tells you that the DBI module successfully connected to your MySQL server, selected the database "mysql" that you configured when you installed MySQL, and output the privileges of all users in the "user" table.

Resources

The scripts in this article demonstrate the most basic MySQL connection and query functions. For more information about PHP and DBI module functions, refer to the following links.

PHP
PHP.net has official MySQL Documentation; the Webmonkey PHP/MySQL Tutorial is a gentle and methodical introduction to PHP scripting for MySQL.

Perl DBI
Perl.com has a very good Short Guide to DBI.