Aug 18 2008

[PHP] PDO: Wrapping and making sweet

Category: Articles,PHPFractalizeR @ 12:18 am

Now PDO is most promising database access mechanism in PHP5. However, using PDO natively is not always convinient. In this article I will suggest my own lightweight wrapper for PDO calls, which simplyfies things for me. I am PHP5 fan, so we will use all object oriented technologies this language can give us in constructing our PDO wrapper.

Basic things to do

Exception object

Due to this database wrapper being a part of my php library, I created my own exception class, that descends from my FL_Exception, which prints and keeps stack trace, makes logging on demand etc.  I don’t use PDO_Exception class, however, one can use it, of course. So, my database exception class is like this:

<?php
 
/**
 * Database to deal with database exceptions
 *
 */
class FL_DB_Exception extends FL_Exception {
 
	/**
	 * SQLState of exception
	 *
	 * @var string
	 */
	public $SQLState ;
 
	/**
	 * Error message description, that comes from driver
	 *
	 * @var string
	 */
	public $driverErrorMessage;
 
	/**
	 * Driver-specific error code
	 *
	 * @var string
	 */
	public $driverErrorCode;
 
	public function __construct(array $errorInfo) {
		$this->SQLState = $errorInfo [0];
		$this->driverErrorMessage = $errorInfo [2];
		$this->driverErrorCode = $errorInfo [1];
		parent::__construct(
			"Database error [{$errorInfo[0]}]: {$errorInfo[2]}, driver error code is $errorInfo[1]");
	}
}
 
?>

As you can see, it is pretty simple. Now let’s start with database wrapper:

Constructor

<?php
/**
* PDO Wrapper class
*
*/
class FL_DB_Connection {
/**
* PDO object
*
* @var PDO
*/
private $_pdoObject = null;
/**
* Default constructor
*
* @param string $connectionString PDO connection string ('mysql:dbname=testdb;host=127.0.0.1')
* @param string $username Username
* @param string $password Password
* @param string $driverOptions Various driver specific options
*/
public function __construct($connectionString, $username, $password, $driverOptions = array()) {
$this->_pdoObject = new PDO($connectionString, $username, $password, $driverOptions);
}

That was just a simple constructor.

Simple statements and selects

Now let’s think, what database statement types we use. To me, they fall into several groups:

  1. Special statements (BEGIN TRANSACTION, COMMIT TRANSACTION, ROLLBACK TRANSACTION, getting last inserted id)
  2. Statements, that do not return any result set (INSERT, UPDATE, GRANT etc.)
  3. Statements, that return a single value (SELECT COUNT(*) FROM users)
  4. Statements, that return a single row (SELECT MIN(temp) as min, MAX(templ) AS max FROM temperature)
  5. Statements, that return several rows

First group is the easiest to implement. Just stubbs:

<?php
	/**
	 * Function begins a transaction
	 *
	 */
	public function beginTransaction() {
		$this->_pdoObject->beginTransaction();
	}
 
	/**
	 * Function commits transaction
	 *
	 */
	public function commitTransaction() {
		$this->_pdoObject->commit();
	}
 
	/**
	 * Function rolls back transaction
	 *
	 */
	public function rollbackTransaction() {
		$this->_pdoObject->rollBack();
	}
 
	/**
	 * Returns the last inserted ID into the database
	 *
	 */
	public function getLastInsertId($sequenceName="") {
		$this->_pdoObject->lastInsertId($sequenceName);
	}

Now let’s think about statements of the second group. They accept zero, one or many parameters. So, we need to make convinient all three types of calls.

<?php
	/**
	 * Execute a statement without returning any data
	 *
	 * @param string $sql SQL statement
	 * @param array $params Parameters. A single value or an array of values
	 * @param array $driverOptions specific options to apply to this operation
	 * @return integer A number of rows affected by last operation
	 */
	public function execute($sql, $params = array(), array $driverOptions = array()) {
		$statement = $this->_pdoObject->prepare($sql, $driverOptions);
		if (! $statement) {
			throw new FL_DB_Exception($statement->errorInfo());
		}
		$paramsConverted = (is_array($params) ? ($params) : (array ($params )));
		if ((! $statement->execute($paramsConverted)) and ($statement->errorCode != '00000')) {
			throw new FL_DB_Exception($statement->errorInfo());
		}
		return $statement->rowCount();
	}

You see, we have here only one required parameter – SQL query text. Optional $params can be a scalar value, or an array of values (we convert a scalar to array accepted by PDO at $paramsConverted variable). The only important moment here is comparing errorCode with “00000” after executing statement. In PDO, situation when SQL statement returns no result rows is considered a special error with code “00000”. For us, it is general case in which we should not throw an exception (unlike all other errors).

Ok, let’s move to the third group. The code will be actually the same as we already saw. Pretty easy.

<?php
	/**
	 * Function returns a single result from a query or false if no
	 * rows were selected by query
	 *
	 * @param string $sql
	 * @param array|mixed $params
	 * @param array $driverOptions
	 * @return unknown
	 */
	public function getOneValue($sql, $params = array(), array $driverOptions = array()) {
		$statement = $this->_pdoObject->prepare($sql, $driverOptions);
		if (! $statement) {
			throw new FL_DB_Exception($statement->errorInfo());
		}
		$paramsConverted = (is_array($params) ? ($params) : (array ($params )));
		if ((! $statement->execute($paramsConverted)) and ($statement->errorCode() !== '00000')) {
			throw new FL_DB_Exception($statement->errorInfo());
		}
		return $statement->fetchColumn(0);
	}

By calling fetchColumn we just return the very first field of the first row of result recordset. Pretty easy.

Getting complex

The FL_DB_RecordsetObj and getOneRow()

Now, let’s proceed to fourth group. Again, everything will be the same except one small, but very important moment. Watch:

<?php
	/**
	 * Function returns a single row from a query or false if no
	 * rows were selected by query
	 *
	 * @param string $sql
	 * @param array|mixed $params
	 * @param array $driverOptions
	 * @return unknown
	 */
	public function getOneRow($sql, $params = array(), array $driverOptions = array()) {
		$statement = $this->_pdoObject->prepare($sql, $driverOptions);
		if (! $statement) {
			throw new FL_DB_Exception($statement->errorInfo());
		}
		$paramsConverted = (is_array($params) ? ($params) : (array ($params )));
 
		if ((! $statement->execute($paramsConverted)) and ($statement->errorCode() !== '00000')) {
			throw new FL_DB_Exception($statement->errorInfo());
		}
 
		return $statement->fetchObject('FL_DB_RecordsetObj');
	}

You see, in last line we return a fetchObject result. We call fetchObject with the name of some class. Why do we do that? fetchObject (I like OO approach even in accessing databases) returns the next row  in a recordset as object. If we specify a class name, it returns an instance of specified class. Each field of that object corresponds to a field in resultset. But… what happens, when we try to access the field on object, that is not set? Correct! It just returns null without reporting any error or throwing any exception. So, if, for example we misspell some field, our script will continue working as no error occured. It is not what you want, is it?

FL_DB_RecordSetObj

That’s why we create a special very simple class FL_DB_RecordSetObj:

<?php
 
/**
 * Class to represent a row, read from DB
 * Just a stubb throwing exception when attempt is made
 * to get non-existing field's value
 *
 */
class FL_DB_RecordsetObj {
	public function __get($name) {
		throw new FL_Exception('Attempt to read unexistent recordset field: '.$name);
	}
}
?>

As you know, __get magic method is called, when we try to access a property on the object, that does not exist. In our case we just throw an exception. Voila!

All is left to do is to implement a method handling the fifth group (statements, that return several rows).

 

FL_DB_RecordsetObj

I usually think of recordset as of iteratable object. And I like to iterate recordset rows with a foreach statement. I think this is very natural. Ok, here is our select method:

<?
	/**
	 * Executes a select statement and returns data for it.
	 *
	 * @param string $sql
	 * @param array|mixed $params
	 * @param array $driverOptions
	 * @return FL_DB_Recordset
	 */
	public function select($sql, $params = array(), array $driverOptions = array()) {
		$statement = $this->_pdoObject->prepare($sql, $driverOptions);
		if (! $statement) {
			throw new FL_DB_Exception($statement->errorInfo());
		}
		$paramsConverted = (is_array($params) ? ($params) : (array ($params )));
		return new FL_DB_Recordset($statement, $paramsConverted);
	}

Tags: , , , , , , , ,

6 Responses to “[PHP] PDO: Wrapping and making sweet”

  1. sv09 says:

    Have you considered putting this under a license like GPL or similar? I was thinking of using this for a small project of mine, but I’m uncertain if I can use the code?

  2. FractalizeR says:

    You are welcome to use the code for any of your needs.

  3. FractalizeR’s WebSite » phpSweetPDO on GitHub says:

    […] time ago I blogged about creating nice PDO wrapper. Recently I’ve created a repository for my small PDO wrapper on GitHub: […]

  4. FractalizeR says:

    I’ve released something a little more advanced to GitHub recently: https://github.com/FractalizeR/phpSweetPDO

    Please feel free to use. Code is under Apache license.

  5. Amendum says:

    ah dunno, where i must start using this excellent code. sorry for my stupidity 🙂

Leave a Reply

You must be logged in to post a comment. Login now.