dcsimg

Getting to Know MDB2

Take the pain out of PHP database programming with a database abstraction layer.

If you’re an old hand at database programming in PHP, you’ve likely come to the conclusion that there’s nothing fun about putting data into, or retrieving data from a database. If you’re a database newbie, you’re probably still confused about all that” magic” and how best to harness it to do your bidding.

Whether inexperienced and confused, or old and jaded, the PHP Extension and Application Repository (PEAR) MDB2 library is worth your time. A replacement, and worthy successor, to PEAR’s original DB library, MDB2 adds some polish to the previous library’s feature list, streamlines other features, and provides a robust and universal database layer to PEAR’s arsenal of tools.

Overview

A database abstraction layer isn’t a new or revolutionary concept. It won’t automagically convert SQL written using the proprietary extensions of one database flavor to work on another. It certainly won’t save you from your own mistakes.

What it will do is make life easier for you as a developer if you decide to change database platforms midstream. In the process it will also give you some handy functionality which makes returning data from your queries a straightforward and standardized process.

MDB2 traces its roots back to Metabase, an early, and still maintained, database abstraction layer for PHP. MDB2′s immediate predecessor MDB was created to bring the features and codebase of the Metabase abstraction layer into line with PEAR’s coding conventions. Design issues, which hampered the development of MDB into a full-fledged DB abstraction layer without breaking backwards compatibility, eventually forced the” friendly fork” that bore MDB2. Currently, MDB2 is the official DBAL offered in PEAR’s PECL collection.

Getting Started with MDB2

Installing MDB2, like most PEAR packages, is a snap. Simply install the package using the standard PEAR command line utility.

pear install MDB2

Much like PERL’s DBI interface, MDB2 uses a modular system of database specific drivers to handle the nuts and bolts of DB interaction. You’ll need to install a module for each type of database with which you plan to connect. To install the driver for your database platform, add the modular driver for that system by name.

pear install MDB2_Driver_<platform>

Common MDB2 database platforms are mysql (MySQL), pgsql (PostgreSQL), oci8 (Oracle), and sqlite (SQLite), with a few other less widely used interfaces available in addition.

Connections

Creating database connections with MDB2 is best done at the beginning of your program. MDB2 features several connection types– but the most commonly used is the factory method, which only creates a connection to the database when it needs to do so.

Also available are the simple connect and singleton connection methods. The connect method forces the creation of a connection, whether it’s needed or not and, like the factory connection method, will use persistent connections when available. The singleton connection type is for single use, throwaway type connections, and is somewhat rarely used.

Here’s an example of a connection to a MySQL database, using the factory method:

require_once("MDB2.php");
$dsn = 'mysql://username:password@host/database';
$dbh =& MDB2::factory($dsn);

Alternately, you can specify your data source as an array, rather than a standard DSN string:

$dsn = array(
'phptype'  => "mysql",
'username' => "username",
'password' => "password",
'hostspec' => "host",
'database' => "database"
);

One convenient thing about PEAR is the standardization of error handling across various libraries. MDB2 offers error handling just as you’d expect a proper PEAR package to do, thus catching a connection error before further execution is as simple as asking the isError method if your resulting database handle is, in fact, an error condition.

if (PEAR::isError ($dbh)) {
    die(" Error: ". $dbh-& gt;getMessage());
}
'database' => "database"
);

If the handle you’ve just created doesn’t throw an error, you’re ready to get down to business, and execute some SQL.

Non-Returning Statements (DDL, Inserts and Updates)

As you’re probably already aware, putting information in the database or adding and changing tables is the easy part of SQL programming. Since there’s no pesky result set to iterate over, you can simply execute the query and go about your merry way:

$sql = "INSERT (firstname, lastname, job_title) INTO employees VALUES ('Grant', 'Robertson', 'Superhero')";
$result = $dbh->query($sql);
if(PEAR::isError($result)) {
    die("Failed to execute query : " . $result->getMessage();
}

Obviously, our insert statement affected only one row, the row we just added. Update statements can affect many rows at a time, and sometimes it’s useful to know just how many rows our query touched. MDB2′s exec() handles this event by returning the number of rows affected by a statement, rather than a result handle.

Returning Data

Among MDB2′s most endearing features are its varied methods for returning data from queries. For example, if you need to return a single column from one row (a common task when storing things like configuration data in the database) queryOne is a handy tool.

$one = $dbh->queryOne("SELECT value FROM configuration WHERE name = 'baseurl'");

MDB2 executes the query and, rather than return a result set for you to work with, simply returns the value of the single field you’ve requested. While this is often useful, MDB2′s real advantages come into play when returning data from queries with a wider scope.

MDB2 offers a choice of how you’d like the data you’ve queried for returned. By setting FetchMode, you can tell MDB2 to return data as an enumerated list, an associative array, or an object.

$mdb2->setFetchMode(MDB2_FETCHMODE_ORDERED);
$mdb2->setFetchMode(MDB2_FETCHMODE_ASSOC);
$mdb2->setFetchMode(MDB2_FETCHMODE_OBJECT);

As you might expect, setting the FetchMode to associative returns the data as an associative array where the values are named for their fields. If we have a table containing employee data, we’d likely have field names such as firstname, lastname, department, employee_number, and start_date. Selecting all the fields from the employee table with our FetchMode set to return an associative array returns a result set you can access easily.

$result = $dbh->query("select * from employee");
while ($row = $result->fetchRow()) {
 echo $result['lastname'];
}

Alternately, if you’ve set FetchMode to return objects, you would access the same data in a slightly different way.

while ($row = $result->fetchRow()) {
 echo $result->lastname;
}

The FetchModes we’ve looked at aren’t just friendly to work with, they result in code that’s easy to read. When you return to this program to make changes later, you’re far more likely to easily understand what your code is doing, even if you failed to leave yourself useful comments.

In our example above, we used fetchRow to return a single row of data from our result set. Fetching a single row works much as you’d expect, advancing one row in the result set for each time it’s called, and remembering where you are as you work through your results. MDB2 provides another way to fetch your results, fetchAll, which returns your entire result set in one large multi-dimensional array. fetchAll also has the advantage of immediately and implicitly freeing the resources taken by the result.

$rows = $result->fetchAll()

Data returned by fetchAll is slightly counterintuitive, until you understand its structure. The first key in the array is the row number of the result set, the second field is the identifier for the value. If we’ve set FetchMode to return associative arrays, our data from the employee table might look something like this example.

Array ( [0] => Array (
 [firstname] => John
 [lastname] => Smith
 [department] => Sales
 [employee_number] => 01234567
 [start_date] => 1999-12-23
 )
 [1] => Array (
 [firstname] => Jane
 [lastname] => Doe
 [department] => Shipping
 [employee_number] => 01234786
 [start_date] => 2001-02-27
 )
)

Since MDB2 keeps track of your place in the result set, jumping to the next row each time fetchRow is called, and jumping to the end (and freeing the result) when fetchAll is used, it’s likely you’ll never have to manually advance or move through your results. If you need to jump around within the result set you’ve created, MDB2′s nextResult() and seek ($rownum) provide the functionality you need.

Working with Prepared Statements

Using prepared statements has several advantages. If your database backend supports them, they can save overhead consumed through frequent statement execution by parsing the statement once, rather than every time it’s needed. Additionally, prepared statements can enhance security by protecting against certain kinds of database injection attacks when the values used in a query comes from an untrusted source. While prepared statements take a small amount of extra effort on your part, once you’ve become accustomed to working with them they become easier to understand.

To prepare a statement in MDB2, you can either use unnamed or named placeholders to represent the unknown values, as shown in this example:

$statement = $mdb2->prepare("INSERT INTO employees VALUES (?, ?, ?, ?, ?)");
$statement = $mdb2->prepare("INSERT INTO employees VALUES (:firstname, :lastname, :department, :employee_number, :start_date");

To use your prepared statement, you simply call the execute() method of the statement you created, passing an array of the values used in the query.

$statement->execute($data);

If you’ve used unnamed values in your prepared statement, the array you pass to execute() must match the order of the fields in your original statement. If you’ve used named placeholders, you can pass execute() an associative array and it will sort out the details of which value goes where.

Another way to use prepared statements involves binding the variables you’re using in the statement with bindParam(). In order to use bindParam(), you must use statements with named placeholders.

$statement->bindParam('firstname', $firstname);

Once you’ve bound a variable to a placeholder, it’s not necessary to bind it again each time you execute the statement. Each time the statement is executed, the current value of the bound variable will be used.

Last Thoughts

Becoming a wizard with MDB2 helps to make simple database backed PHP tasks super-simple, and difficult tasks more manageable. We’ve only touched upon the most commonly used functionality provided by MDB2 in this article, and much of the productivity enhancing features require further explanation outside of the scope of what we’ve covered.

Hopefully, these examples have shown you that once you’ve mastered the use of a database abstraction layer, you’ll never think about communicating with the database directly again. Fight the urge to re-write the wheel with your own data interfaces and spend a few minutes learning your way around MDB2; It’s time well spent, and can pay productivity dividends well into the future.

Fatal error: Call to undefined function aa_author_bios() in /opt/apache/dms/b2b/linux-mag.com/site/www/htdocs/wp-content/themes/linuxmag/single.php on line 62