Home arrow PHP arrow Page 4 - Database Abstraction With PHP

Independence Day - PHP

One of the nicest things about Perl - the DBI module - finallymakes an appearance in PHP. Take a look at the PEAR database abstractionlayer, by far one of the coolest PHP widgets out there.

  1. Database Abstraction With PHP
  2. Alphabet Soup
  3. Sultans Of Swing
  4. Independence Day
  5. Different Strokes
  6. The Number Game
  7. Preparing For The Long Haul
  8. Commitment Issues
  9. No News Is Good News
  10. Catch Me If You Can
  11. Once Again, The Headlines
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 64
February 13, 2002

print this article


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:


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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: