PHP and MySQL

Russell J.T. Dyer

One of the most popular programming language and database engine combinations for the Web is PHP with MySQL. This combination works well for many reasons: primarily the speed, the stability, and the simplicity of both applications. The two are used extensively on a variety of Web sites. As a systems administrator, you can use the pair for storing data on users, hardware, and system documentation, for example. In this article, I will explain the basics of how to connect to MySQL and how to query MySQL with PHP.

Connecting to MySQL

The first task for a PHP script that will interface to MySQL is to make a connection to it, to establish a MySQL session. For the examples in this article, I will use a database called sys_admin that I will assume has already been created in MySQL. If you're unfamiliar with how to create databases in MySQL, check out my MySQL column archives at UnixReview.com (http://www.unixreview.com/mysql/). To connect to the database sys_admin, I could begin a PHP script like so:

<?php

require_once('DB.php');

$user = 'username';
$pw = 'password';
$host = 'localhost';
$db = 'sys_admin';

$dbh = DB::connect("mysql://$user:$pw@$host/$db");

if(DB::iserror($dbh)) {
   die($dbh->getMessage());
}

?>

This section of PHP code starts by calling the PEAR DB library, which typically comes with the basic installation of PHP. If not, you can get a copy from http://pear.php.net. PEAR, which stands for "PHP Extension and Application Repository", provides libraries of common functions. The DB library provides database functions. The stanza that follows establishes the variables necessary for connecting to the database. After that, a database handle ($dbh) is created -- PHP connects to MySQL by giving the user and database variables. If it's unsuccessful, the script will die with an error message. If the database handle is successful, though, the database sys_admin will be selected for use. Each PHP script that is to interface with MySQL will begin with a section of code like this one.

Querying Data

In my fictitious database, there's a table called computers that contains information on the office computers and servers. To retrieve a list of servers and some basic information on them, I'll begin a PHP script by connecting to MySQL, as shown in the last script excerpt. That will be followed by the start of a Web page and then the invoking of an SQL statement to retrieve and display the data. This can be achieved with code such as the following:

...  // Connect to MySQL

<html>
<body>

<h2>List of Servers</h2>

<?php

$sql = "SELECT comp_id, netbios,
        location, op_sys
        FROM computers
        WHERE type = 'server'";

$q = $dbh->query($sql);

if(DB::iserror($q)) {
   die($q->getMessage());
}

while($q->fetchInto($row)) {
   list($comp_id, $netbios, $location, $op_sys) = $row;

   print 
       "<a href='details.php?comp_id=$comp_id'>$netbios</a>
       - $location ($op_sys)<br/>";
}

$dbh->disconnect();

?>

</body>
</html>

After connecting to MySQL (substituted with ellipses here) and starting the Web page, I then set up a variable ($sql) for the SQL statement. Using object-oriented programming methods, the database is queried with the SQL statement. In the next line or so of code, I have an if statement to see whether an error occurred. If so, the script will terminate and display an error message.

Assuming PHP was successful in querying the database, I have a while statement loop through each row of data retrieved from MySQL. With each pass, using the function fetchInto(), PHP will temporarily store the columns of data for each row in an array ($row). Within the code block of the while statement, the list() function parses the elements of the array $row into the respective variables. I've named these variables to match their column counter parts. This is not necessary, though; they may be named anything. I could even use the array as it is and reference the appropriate sequence number to retrieve data. For instance, for the operating system value, I could use $row[3] since it's the fourth in the sequence (0 is first). Naming the variables as I've done here, though, makes it easier to read the code and easier for others to follow it later.

The second line of code within the while statement displays the data in a format necessary for the Web page. The NETbios name is wrapped in a hyperlink with a reference to another PHP script (details.php), which will retrieve all of the details for the particular server selected by a user. That server will be identified by the computer identification number (i.e., comp_id), which is a key column for the PHP script details.php that I would create later. The value for comp_id typically will be placed automatically in a variable by the same name ($comp_id), regardless of what I name that variable in this script. It's based on the name given in the hyperlink or anchor tag. This will happen if the php.ini configuration file has register_globals set to on. On Unix and Linux systems, this file is located in the /etc directory. On a Windows system, it's usually found in the c:\windows directory. If not, then the value can be referenced using the $_POST associative array in which all values that are posted or passed to the next script are stored. In this, case I could retrieve it by using $_POST['comp_id'].

Conclusion

I've only covered how to connect to MySQL with PHP and how to query a database using a SELECT statement. However, with these components you can invoke any SQL statement with PHP. There are several other DB functions that you may want to look into for fine-tuning a script. If you're new to combining PHP with MySQL, however, you can initially limit yourself to the basics presented here and learn more when necessary.

Russell Dyer is a Perl programmer, a MySQL developer, and a Web designer living and working on a consulting basis in New Orleans. He is also an adjunct instructor at a technical college where he teaches Linux and other open source software. He can be reached at russell@dyerhouse.com.