Speaking Different Tongues
If you’ve been working with PHP for a while, you’re probably already aware of its support for a wide variety of database systems. MySQL, PostgreSQL, Oracle, SQL Server, dBase - these are just some of the databases that PHP supports, and it’s precisely this extensive support that makes PHP so popular as a tool for building data-driven Web applications.
There’s only one fly in the ointment. PHP provides a different set of functions to “talk” to each database, rather than a single, unified API. This means that if you ever switch from one database back-end to another, you’ll usually have to rewrite all your PHP code to use new functions…a task that’s guaranteed to take the sunlight out of your day.
Fortunately, PEAR has a solution to this problem. The PEAR DB class provides a database abstraction layer for RDBMS interaction, making it possible to switch from one database to another without having to re-code your application. Keep reading, and let me show you how it works.
Independence Day
I’ll start with the basics - what the heck is a database abstraction layer anyhow?
A database abstraction layer is essentially a database-independent software interface. As the name suggests, it’s a layer of abstraction over the actual database access methods and allows developers to deal with different databases without radically altering their code on a per-database basis.
By placing a layer of abstraction between the database and the developer, the database abstraction layer insulates the programmer from database implementation details. If you initially write a script to talk directly to, say, Oracle and later need to have it work with another database server, you will usually have to rewrite all the database-specific parts. If you use a database-independent API, you can port your script over with very little surgery required.
PHP’s database abstraction layer comes courtesy of PEAR, the PHP Extension and Application Repository. When you install PHP, a whole bunch of PEAR modules get installed as well; the DB class is one of them. You can also manually install the package from the official PEAR Web site, at http://pear.php.net/package/DB - simply unzip the distribution archive into your PEAR directory and you’re ready to roll! This tutorial uses PEAR DB v1.7.
Sweet Symbolism
Before getting started with the code, you’ll need to initialize the SQL table I’ll be using throughout this tutorial. Pop open your MySQL command-line client, and enter the following SQL commands:
CREATE TABLE `symbols` (
`id` int(11) NOT NULL auto_increment,
`country` varchar(255) NOT NULL default '',
`animal` varchar(255) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM;
INSERT INTO `symbols` VALUES (1, 'America', 'eagle');
INSERT INTO `symbols` VALUES (2, 'China', 'dragon');
INSERT INTO `symbols` VALUES (3, 'England', 'lion');
INSERT INTO `symbols` VALUES (4, 'India', 'tiger');
INSERT INTO `symbols` VALUES (5, 'Australia', 'kangaroo');
You can enter these commands either interactively or non-interactively through the MySQL client program. Read http://dev.mysql.com/doc/mysql/en/mysql.html for more information on how to use the MySQL client, and the tutorial at https://www.melonfire.com/archives/trog/article/speaking-sql-part-1 to understand what each of the SQL commands above does. SQL is a lot like spoken English, so it won’t take you very long to pick it up.
Once the data has been imported, run a quick SELECT query to check if everything is working as it should:
mysql> SELECT * FROM symbols;
+----+-----------+----------+
| id | country | animal |
+----+-----------+----------+
| 1 | America | eagle |
| 2 | China | dragon |
| 3 | England | lion |
| 4 | India | tiger |
| 5 | Australia | kangaroo |
+----+-----------+----------+
5 rows in set (0.06 sec)
You can do the same thing with PHP, as follows:
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// open connection
$connection = mysql_connect("localhost", "john", "doe") or die("Unable to connect!");
// select database
mysql_select_db("db2") or die("Unable to select database!");
// create and execute query
$query = "SELECT country, animal FROM symbols";
$result = mysql_query($query) or die("Error in query: $query. " . mysql_error());
// check for returned rows
if (mysql_num_rows($result) > 0) {
// print them
echo "`<table cellpadding=10 border=1>`";
while ($row = mysql_fetch_row($result)) {
echo "`<tr>`";
echo "`<td>`" . $row[0] . "</td>";
echo "`<td>`" . $row[1] . "</td>";
echo "</tr>";
}
echo "</table>";
} else {
// or print status message
echo "No rows found!";
}
// free result set memory
mysql_free_result($result);
// close connection
mysql_close($connection);
?>
</body>
</html>
Most of this should already be familiar to you. The script above connects to the database, executes a query, retrieves the result and iterates through it. Fairly simple, except for one glaring flaw: because it uses MySQL-specific functions throughout, it’s going to crash and burn the second you switch the data over to a PostgreSQL or Oracle RDBMS. Which is where the database abstraction layer comes in.
The Abstract Approach
In order to see how the database abstraction layer works, consider this next script, which rewrites the previous one using PEAR DB instead of PHP’s MySQL extension:
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// include DB
include("DB.php");
// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db2");
if (PEAR::isError($dbh)) {
die("Unable to connect. " . $dbh->getMessage());
}
// create and execute query
$query = "SELECT country, animal FROM symbols";
$result = $dbh->query($query);
if ($dbh->isError($result)) {
die("Error in query: " . DB::errorMessage($result) . ". Query was: $query");
}
// check for returned records
if ($result->numRows() > 0) {
// print them
echo "`<table cellpadding=10 border=1>`";
while ($row = $result->fetchRow()) {
echo "`<tr>`";
echo "`<td>`" . $row[0] . "</td>";
echo "`<td>`" . $row[1] . "</td>";
echo "</tr>";
}
echo "</table>";
} else {
// or print status message
echo "No rows found!";
}
// free result set memory
$result->free();
// close connection
$dbh->disconnect();
?>
</body>
</html>
This output of this script is equivalent to that of the previous one; however, since it uses database-independent functions to interact with the database server, it holds out the promise of continuing to work no matter which database is used.
The first step is, obviously, to include the abstraction layer class. Once this is done, open a connection to the database by invoking the class’ connect() method and passing it a DSN string containing connection parameters: the database type, user name and password, host name, and database name. Once a connection has been made, the connect() method will return an object representing the connection. The object’s query() method can then be used to execute SQL queries. Successful query execution returns a new result object (for SELECT queries) or a Boolean value indicating whether the query was successful (for non-SELECT queries). It’s then a simple matter to use the object’s fetchRow() method, in combination with a loop to iterate over the returned records and print each one. Once the heavy lifting is all done, the free() method frees up the memory associated with the result set, while the disconnect() method gracefully closes the database connection and disengages from the database.
In the event that a different database is used, the only change required in the script above would be to the line invoking connect() - a new connection string would need to be passed to the function with the type of the new database, and appropriate connection parameters. Everything else would stay exactly the same, and the code would continue to work exactly as before.
This is the beauty of an abstraction layer - it exposes a generic API to developers, allowing them to write one piece of code that can be used in different situations, with all the ugly bits hidden away and handled internally. Which translates into simpler, cleaner code, better script maintainability, shorter development cycles and an overall Good Feeling.
Different Strokes…
In the previous script, each result row was represented as an integer-indexed array by the fetchRow() method. However, this isn’t the only way to work with result rows; you can just as easily represent each row as an associative array, by passing the special DB_FETCHMODE_ASSOC argument to the fetchRow() method. Here’s an example:
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// include DB
include("DB.php");
// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db2");
if (PEAR::isError($dbh)) {
die("Unable to connect. " . $dbh->getMessage());
}
// create and execute query
$query = "SELECT country, animal FROM symbols";
$result = $dbh->query($query);
if ($dbh->isError($result)) {
die("Error in query: " . DB::errorMessage($result) . ". Query was: $query");
}
// check for returned records
if ($result->numRows() > 0) {
// print them
echo "`<table cellpadding=10 border=1>`";
while ($row = $result->fetchRow(DB_FETCHMODE_ASSOC)) {
echo "`<tr>`";
echo "`<td>`" . $row['country'] . "</td>";
echo "`<td>`" . $row['animal'] . "</td>";
echo "</tr>";
}
echo "</table>";
} else {
// or print status message
echo "No rows found!";
}
// free result set memory
$result->free();
// close connection
$dbh->disconnect();
?>
</body>
</html>
Here, the keys of the associative array are the column names of the result records. In the event that a column name is repeated - as might happen, for example, with a table join - the last one will be used.
It’s also possible to retrieve a row as an object, with the fields within it exposed as object properties, by replacing DB_FETCHMODE_ASSOC with DB_FETCHMODE_OBJECT. Take a look:
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// include DB
include("DB.php");
// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db2");
if (PEAR::isError($dbh)) {
die("Unable to connect. " . $dbh->getMessage());
}
// create and execute query
$query = "SELECT country, animal FROM symbols";
$result = $dbh->query($query);
if ($dbh->isError($result)) {
die("Error in query: " . DB::errorMessage($result) . ". Query was: $query");
}
// check for returned records
if ($result->numRows() > 0) {
// print them
echo "`<table cellpadding=10 border=1>`";
while ($row = $result->fetchRow(DB_FETCHMODE_OBJECT)) {
echo "`<tr>`";
echo "`<td>`" . $row->country . "</td>";
echo "`<td>`" . $row->animal . "</td>";
echo "</tr>";
}
echo "</table>";
} else {
// or print status message
echo "No rows found!";
}
// free result set memory
$result->free();
// close connection
$dbh->disconnect();
?>
</body>
</html>
In this case, each $row object is created with properties corresponding to the field names in that row. Row values can thus be accessed using standard object->property notation.
Note that if you have a large number of queries in your script, you can force a default mode for result retrieval by using the setFetchMode() method.
Of Rows And Columns
You can retrieve the number of rows and columns returned by a particular query with the numRows() and numCols() methods, as illustrated below:
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// include DB
include("DB.php");
// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db2");
if (PEAR::isError($dbh)) {
die("Unable to connect. " . $dbh->getMessage());
}
// create and execute query
$query = "SELECT * FROM symbols";
$result = $dbh->query($query);
if ($dbh->isError($result)) {
die("Error in query: " . DB::errorMessage($result) . ". Query was: $query");
}
// print number of returned rows and columns
echo "Query returned ". $result->numRows() . " row(s) and " . $result->numCols() . " column(s)";
// free result set memory
$result->free();
// close connection
$dbh->disconnect();
?>
</body>
</html>
The PEAR DB class also includes a way for you to find out more about the attributes of each column. The tableInfo() method returns an array for each field in the result set, providing meta-information about the field type, length and special flags. Here’s an example:
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// include DB
include("DB.php");
// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db2");
if (PEAR::isError($dbh)) {
die("Unable to connect. " . $dbh->getMessage());
}
// create and execute query
$query = "SELECT * FROM symbols";
$result = $dbh->query($query);
if ($dbh->isError($result)) {
die("Error in query: " . DB::errorMessage($result) . ". Query was: $query");
}
// get table information
print_r($result->tableInfo());
// free result set memory
$result->free();
// close connection
$dbh->disconnect();
?>
</body>
</html>
You probably already know you can limit the number of records returned by a MySQL query by adding the LIMIT clause. However, this LIMIT clause is non-standard SQL, and doesn’t work with all databases. That’s why the PEAR DB class includes a special LimitQuery() method just for this task. Depending on which database system is being used, the LimitQuery() method makes the necessary changes to the SELECT query to ensure that only the required subset of records is returned. Here’s an example of it in action:
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// include DB
include("DB.php");
// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db2");
if (PEAR::isError($dbh)) {
die("Unable to connect. " . $dbh->getMessage());
}
// create and execute query
$query = "SELECT country, animal FROM symbols";
// display 3 records starting from record #2
$result = $dbh->LimitQuery($query, 2, 3);
if ($dbh->isError($result)) {
die("Error in query: " . DB::errorMessage($result) . ". Query was: $query");
}
// check for returned records
if ($result->numRows() > 0) {
// print them
echo "`<table cellpadding=10 border=1>`";
while ($row = $result->fetchRow()) {
echo "`<tr>`";
echo "`<td>`" . $row[0] . "</td>";
echo "`<td>`" . $row[1] . "</td>";
echo "</tr>";
}
echo "</table>";
} else {
// or print status message
echo "No rows found!";
}
// free result set memory
$result->free();
// close connection
$dbh->disconnect();
?>
</body>
</html>
Being Prepared
So now you know how to execute a SELECT query to retrieve a result set from the database. However, you can also use the DB class for queries that don’t return a result set - for example, an INSERT or UPDATE query. The following example demonstrates how to INSERT a new record in a table:
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// include DB
include("DB.php");
// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db2");
if (PEAR::isError($dbh)) {
die("Unable to connect. " . $dbh->getMessage());
}
// create and execute query
$query = "INSERT INTO symbols (country, animal) VALUES('Singapore', 'merlion')";
$result = $dbh->query($query);
if ($dbh->isError($result)) {
die("Error in query: " . DB::errorMessage($result) . ". Query was: $query");
}
// close connection
$dbh->disconnect();
?>
</body>
</html>
A common requirement in this context involves executing a particular query multiple times with different values - for example, a series of INSERT statements with different values. The DB class lets you accomplish this easily through the use of prepared statements, which can save you time and also reduce overhead. Consider the following example, which demonstrates:
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// include DB
include("DB.php");
// initialize data array
$data = array(array('Singapore', 'merlion'), array('Norway', 'elk'));
// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db2");
if (PEAR::isError($dbh)) {
die("Unable to connect. " . $dbh->getMessage());
}
// prepare query
$query = $dbh->prepare("INSERT INTO symbols (country, animal) VALUES(?, ?)");
foreach ($data as $d) {
$result = $dbh->execute($query, $d);
if ($dbh->isError($result)) {
die("Error in query: " . DB::errorMessage($result) . ". Query was: $query");
}
}
// close connection
$dbh->disconnect();
?>
</body>
</html>
The prepare() function, which takes an SQL query as parameter, readies a query for execution, but does not execute it. Instead, prepare() returns a handle to the prepared query, which is stored and then passed to the execute() method, which actually executes the query.
Note the ? placeholder used in the query string passed to prepare() - this placeholder is replaced by an actual value each time execute() runs on the prepared statement. The second argument to execute() is an array containing the values to be substituted in the query string.
Finally, you can find out the number of rows affected by an INSERT, UPDATE or DELETE query by calling the class’ affectedRows() method, which returns the number of rows altered by the last query. Here’s an example:
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// include DB
include("DB.php");
// initialize data array
$data = array(array('Singapore', 'merlion'), array('Norway', 'elk'));
// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db2");
if (PEAR::isError($dbh)) {
die("Unable to connect. " . $dbh->getMessage());
}
// create and execute query
$query = "DELETE FROM symbols where id > 5";
$result = $dbh->query($query);
if ($dbh->isError($result)) {
die("Error in query: " . DB::errorMessage($result) . ". Query was: $query");
}
// get number of affected rows
echo $dbh->affectedRows() . " record(s) deleted";
// close connection
$dbh->disconnect();
?>
</body>
</html>
A Question Of Commitment
If your database system supports transactions (newer versions of MySQL do, while PostgreSQL and Oracle have long supported this feature), you can use built-in DB class methods to commit or rollback transactions.
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// include DB
include("DB.php");
// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db1");
if (PEAR::isError($dbh)) {
die("Unable to connect. " . $dbh->getMessage());
}
// turn off auto-commit
$dbh->autoCommit(false);
// create and execute query #1
$query = "INSERT INTO orders VALUES (2, 20)";
$result = $dbh->query($query);
if ($dbh->isError($result)) {
// in case of errors, rollback
echo("Error in query: " . DB::errorMessage($result) . ". Query was: $query");
$dbh->rollback();
$dbh->disconnect();
exit();
}
// create and execute query #2
$query = "UPDATE inventory SET qty = qty - 20 WHERE id = '2'";
$result = $dbh->query($query);
if ($dbh->isError($result)) {
echo("Error in query: " . DB::errorMessage($result) . ". Query was: $query");
$dbh->rollback();
$dbh->disconnect();
exit();
}
// no errors
// commit the transaction
$dbh->commit();
echo "Transaction successfully committed!";
// close connection
$dbh->disconnect();
?>
</body>
</html>
Here, you must first turn off auto-committal of data to the database, via the autoCommit() method. Once that’s done, you can go ahead and execute as many queries as you like, secure in the knowledge that no changes have (yet) been made to the database. If an error occurs during the query execution sequence, the rollback() method can be used to automatically revert the database to an earlier state. If no errors occur, the changes will be saved with commit().
Making Mistakes
All done? Nope, not quite yet - before you go out there and start building cool data-driven Web sites, you should be aware that the DB class come with error-handling functions which can speed up development time. Take a look at the following example, which contains a deliberate error in the SELECT query string:
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// include DB
include("DB.php");
// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db2");
if (PEAR::isError($dbh)) {
die("Unable to connect. " . $dbh->getMessage());
}
// create and execute query
$query = "RESELECT * FROM symbols";
$result = $dbh->query($query);
if ($dbh->isError($result)) {
// display an error message and exit
echo "An error occurred. `<br />`";
echo "You said: $query `<br />`";
echo "The database said: " . DB::errorMessage($result) . "`<br />`";
exit();
}
// free result set memory
$result->free();
// close connection
$dbh->disconnect();
?>
</body>
</html>
Here, the isError() method tests the result object to see if it is an error and, if so, displays an error message before exiting the script. Note the call to the static errorMessage() method, which provides a string description of the error as returned by the RDBMS.
The above error-handling technique works great so long as you only have a couple of queries running in each script. But what if you need to execute eight, ten or a hundred queries within a script? Well, instead of copying and pasting the error-checking code over and over again, you can use a default error handler to trap and resolve errors in a manner of your choosing.
This default error handling mechanism can be defined through the setErrorHandling() method, which accepts any one of the following five parameters:
· PEAR_ERROR_RETURN - do nothing · PEAR_ERROR_PRINT - print the error message but continue executing the script · PEAR_ERROR_TRIGGER - trigger a PHP error · PEAR_ERROR_DIE - terminate script execution · PEAR_ERROR_CALLBACK - call another function to handle the error
Here’s an example, which shows you how to use the PEAR_ERROR_CALLBACK argument to define your own error handler and log errors to a file:
`<html>`
`<head>``<basefont face="Arial">`</head>
`<body>`
<?php
// include DB
include("DB.php");
// open connection
$dbh = DB::connect("mysql://john:doe@localhost/db2");
if (PEAR::isError($dbh)) {
die("Unable to connect. " . $dbh->getMessage());
}
// set an error handler
$dbh->setErrorHandling(PEAR_ERROR_CALLBACK, "eh");
// create and execute query
$query = "RESELECT * FROM symbols";
$result = $dbh->query($query);
// free result set memory
$result->free();
// close connection
$dbh->disconnect();
// user-defined error handler
// logs errors to a file
function eh(&$obj)
{
// create a string to hold the error
$str = date("d-M-Y h:m:s", time()) . " " . $obj->code . " " . $obj->message . "\n";
// append error string to a log file
file_put_contents("error.log", $str, FILE_APPEND) or die("Could not open log file");
}
?>
</body>
</html>
In this script, the setErrorHandling() function is passed two arguments: the constant PEAR_ERROR_CALLBACK, and the name of the user-defined error handler. If an error occurs, this error handler is invoked with an object as argument; this object represents the error, while its properties hold detailed information on what went wrong. It’s fairly easy to extract the error information from this object and write it to a log file with file_put_contents(). Because the error handler is really a user-defined function, you can modify it to handle errors in just about any way you like, from sending email to displaying a customized message.
And that’s about all I have time for. I hope the preceding examples gave you some insight into the PEAR DB class, and that you will find a use for it in your next project. Till then…happy coding!
This article was first published on 09 Jul 2006.