While writing some PHP Training materials for Pale Purple, I thought I'd add an updated guide on PHP and database access. I've already done one on PEAR::DB, but PEAR::MDB2 is it's successor and has a slightly different API.... and as PEAR::DB is now deprecated, it's probably about time I rewrote it anyway.

What is PEAR::MDB2?

MDB2 is yet another database API for PHP. It's written in PHP, so can be used in a cross platform manner. Because it's written in PHP, it's not going to be as 'fast' as the PDO library, however it's portability may make up for that.

Compared to the native bundled legacy PHP libraries (mysqli_*, pgsql_*, sqlite_*, mysql_), changing which database you are using can be as simple as changing the connection parameters to your database. The PDO libraries offer the same advantage, however they require PHP5.

Getting started

It's recommended you install it using the Pear installer, like the following :

pear install MDB2
pear install MDB2_Driver_$db

Where $db would be one of e.g. mysql, pgsql, sqlite, ori8, mssql etc.

You'll note, that unlike PEAR::DB, it's necessary to download a specific driver for each database you wish to support.

Connecting to a database

One of the advantages of MDB2 over PEAR::DB is that it offers a number of different ways of providing a DB connection - either lazy (MDB2::factory()), optimistic (MDB2::connect()) or singleton (MDB2::singleton()) . I personally tend to just go with the lazy 'factory' method where the connection isn't created until it's used.

As with PEAR::DB, you specify which database to connect to using a JDBC style URL, which looks something like : $db_driver://$user:$password@$host/$database_name, so for example pgsql://web:password@dbserver/my_database would be a valid url.

<?php 
require_once("MDB2.php");
$con = MDB2::factory($url);

Error checking

As always, there is plenty of scope for something to go wrong. Perhaps the database server is offline, or your username/password are incorrect. In any case, error handling with MDB2 follows the PEAR convention of using PEAR::isError($return_value). So in our case, our code could now look a bit like :

<?php
require_once("MDB2.php");
$con = MDB2::factory($url);
if(PEAR::isError($con)) {
    die("Error while connecting : " . $con->getMessage());
}
// use $con

Security (SQL Injection)

When ever talking to a relational database, care has to be taken that any parameters passed into a query have been appropriately sanitised. Failure to sanitise data will make your site vulnerable to SQL injection, data loss or compromise. If you don't know what SQL Injection is, try reading the following article at lwn.net.

Issuing Queries on the database

Select queries (returning data)

<?php
// load library as above.
// connect to db as above. ($con = ... )
// check $con validity as per above example.

$sql = "SELECT * FROM the_table";
$resultset = $con->query($sql);
if(PEAR::isError($resultset)) {
    die('Failed to issue query, error message : ' . $resultset->getMessage());
}

while($row = $resultset->fetchRow(MDB2_FETCHMODE_ASSOC)) {
    foreach($row as $field => $value) {
        echo "$field / $value \n";
    }
}

Non returning queries (update, insert, delete)

This is largely the same as above, apart from the fact there is no need to do the while loop / fetchRow section, so the code becomes somewhat simpler and would look a bit like :

// load library
// connect to db, and check for errors
$sql = "UPDATE table SET column = 5 WHERE id = 1";
$result = $con->query($sql);
if(PEAR::isError($result)) {
    die("Failed to issue query, error message : " . $result->getMessage();
}

If you need to add in user supplied data into the query, you can use $save_var = $con->quote($var). An optional second parameter can be given to determine whether the data should be reformatted as a boolean, date, integer or text.

Prepared statements (or how to safely pass parameters into a database query)

A safer (and potentially better performing) approach to insert user supplied data into queries is to use Prepared Statements. These can be faster, as if the underlying database supports it, the statement itself can be compiled and cached, so saving the database engine from having to reparse the same SQL each time. They are safer, because SQL injection is not possible when using them - because the database knows what goes where. Some database engines (e.g. MySQL v4) do not support prepared statements, in which case, like PEAR::DB, PEAR::MDB2 will emulate the functionality in the background.

A prepared statement example:

<?php
// load library, get connection etc (as per above)

$sql = "UPDATE table SET column = ? WHERE id = ?";
$types = array('integer', 'text');
$statement = $con->prepare($sql, $types, MDB2_PREPARE_MANIP);
$data = array(5, 'blah');
$affected_rows = $statment->execute($data);
if(PEAR::isError($affected_rows)) {
    // die etc.
}

MDB2_PREPARE_MANIP is required if you wish to manipulate data, and not return any values. MDB2_PREPARE_RESULT is used if you wish to run a prepared statement and have data returned, for instance :

The $types array is not required (you could replace it with TRUE), as MDB2 can automatically handle it.

<?php
// load library, get db connection etc.
$types = array("text");
$statement = $con->prepare("SELECT * FROM table WHERE column = ?", $types, MDB2_PREPARE_RESULT);
$data = array('foo');
$resultset = $statement->execute($data);
if(PEAR::isError($resultset)) {
    die('failed... ' . $resultset->getMessage());
}
$statement->Free();
while($row = $resultset->fetchRow(MDB2_FETCHMODE_ASSOC)) {
    echo "Found : " . $row['column'] . "\n";
}

Suggestions for Usage

I'm not particularly keen on writing the same thing twice, so I tend to use functions like the following :

<?php
require_once("MDB2.php");
function connect() {
    $con = MDB2::factory($url);
    if(PEAR::isError($con)) {
        die("Error while connecting : " . $con->getMessage());
    }
    return $con;
}

Then something like the following to undertake queries :

function execute_query($sql, $values=array()) {
    $con = connect();
    $results = array();
    if(sizeof($values) > 0) {
        $statement = $con->prepare($sql, TRUE, MDB2_PREPARE_RESULT);
        $resultset = $statement->execute($values);
        $statement->free();
    }
    else {
        $resultset = $con->query($sql);
    }
    if(PEAR::isError($resultset)) {
        die('DB Error... ' . $resultset->getMessage());
    }

    while($row = $resultset->fetchRow(MDB2_FETCHMODE_ASSOC)) {
        $results[] = $row;
    }
    return $results;
}

Thanks for reading; feel free to leave comments/suggestions (or questions).

Technorati Tags: Documentation PHP SQL pear mdb2 php prepared statement howto tutorial sql