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?
Next: Different Strokes >>
More PHP Articles
More By icarus, (c) Melonfire
/ 49 
