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?