Database Abstraction With PHP - Independence Day
( Page 4 of 11 )

In order to demonstrate how the abstraction layer works, I'll use it to rewrite the previous example - take a look:

<?php
// uncomment this to see plaintext output in your browser
// header("Content-Type: text/plain");

// include the DB abstraction layer
include("DB.php");

// connect to the database
$dbh = DB::connect("mysql://john:doe@localhost/db287");

// execute query
$query = "SELECT * FROM cds";
$result = $dbh->query($query);

// iterate through rows and print column data
// in the form TITLE - ARTIST
while($row = $result->fetchRow())
{
	echo "$row[1] - $row[2]\n";
}

// get and print number of rows in resultset
echo "\n[" . $result->numRows() . " rows returned]\n";

// close database connection
$dbh->disconnect();

?>

This output of this snippet 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 I use. I'll show you how in a minute - but first, a quick explanation of the functions used above:

1. The first step is, obviously, to include the abstraction layer in your script. The class file can usually be found in the PEAR installation directory on your system; you can either provide include() with the full path, or (if your PEAR directory is included in PHP's "include_path" variable) just the class file name.

<?
// include the DB abstraction layer
include("DB.php");
?>

2. Next, open up a connection to the database. This is accomplished by statically invoking the DB class' connect() method, and passing it a string containing connection parameters.

<?

$dbh = DB::connect("mysql://john:doe@localhost/db287");







Reading this may make your head hurt, but there *is* method to the madness
- roughly translated, the line of code above attempts to open up a
connection to the MySQL database named "db287", on the host named
"localhost", with the username "john" and password "doe".

In case you're wondering, the format of the connection string is:





type(syntax)://user:pass@protocol+host/database

Supported database types include "fbsql", "ifx", "mssql", "oci8", "pgsql", "ibase", "msql", "mysql", "odbc" and "sybase".

Here are a few examples of how this might be used:

mysql://john:doe@dbserver/mydb

mysql://john:doe@some.host.com:123/mydb

pgsql://john@dbserver/mydb

pgsql://dbserver/mydb

odbc://john:doe@some.host.com/mydb

If all goes well, the connect() method will return an object that can be used in subsequent database operations.

This also means that you can open up connections to several databases simultaneously, using different connect() statements, and store the returned handles in different variables. This is useful if you need to access several databases at the same time; it's also useful if you just want to be a smart-ass and irritate the database administrators. Watch them run as the databases overheat! Watch them scurry as their disks begin to thrash! Watch them gibber and scream as they melt down!

3. Once the connect() method returns an object representing the database, the object's query() method can be used to execute SQL queries on that database.

<?
// execute query
$query = "SELECT * FROM cds";
$result = $dbh->query($query);
?>

Successful query execution returns a new object containing the results of the query.

4. This object exposes methods and properties that can be used to extract specific fields or elements from the returned resultset.

<?
// iterate through rows and print column data
// in the form TITLE - ARTIST
while($row = $result->fetchRow())
{
	echo "$row[1] - $row[2]\n";
}
?>

In this case, the object's fetchRow() method is used, in combination with a "while" loop, to iterate through the returned resultset and access individual fields as array elements. These elements are then printed to the output device.

Once all the rows in the resultset have been processed, the object's numRows() method is used to print the number of rows in the resultset.

<?
// get and print number of rows in resultset
echo "\n[" . $result->numRows() . " rows returned]\n";
?>

5. Finally, with all the heavy lifting done, the disconnect() method is used to gracefully close the database connection and disengage from the database.

<?
// close database connection
$dbh->disconnect();
?>

In the event that someone (maybe even me) decides to switch to a different database, 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 new connection parameters. Everything else would stay exactly the same, and my 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. Simple, huh?

>>> More PHP Articles          >>> More By icarus, (c) Melonfire