Aug 18 2008

[PHP] PDO: Wrapping and making sweet

Category: Articles,PHPFractalizeR @ 12:18 am

Now we need to implement FL_DB_Recordset class. It will implement Countable and Iterator interfaces (so we would be able to use count() function on all recordset to get the total number of objects and to iterate it using foreach). We pass a PDO_Statement instance to this class’ constructor and statement parameters also. You will find out the reason a bit later.

For a class to implement Iterator interface it should implement the following methods:

  • void rewind() – should return an object to starting state, from which first row can be read by calling current()
  • mixed current() – should return the current value of iteration or false if we are beyond the last value of iteration
  • mixed key() – should return the current key of iteration
  • mixed next() – should move to next value of iteration and return it or just return false, if no next value exist
  • bool valid() – should return true, if we can call current() to get current value of iteration, false, if we are beyond the last value of iteration.

Class implementation of FL_DB_Recordset is pretty straitforward, so I will just list it as it is:

<?php
 
/**
 * Class representing recordset of database
 *
 */
class FL_DB_Recordset implements Iterator, Countable {
 
	/**
	 * PDO statement object
	 *
	 * @var PDOStatement
	 */
	private $_statement = null;
 
	/**
	 * Query parameters
	 *
	 * @var array
	 */
	private $_params = array ();
 
	/**
	 * Object representing the current row in resultset
	 *
	 * @var FL_DB_RecordsetObj
	 */
	private $_currentRowObj = null;
 
	/**
	 * Index of the current row
	 *
	 * @var int
	 */
	private $_currentRowIndex = 0;
 
	/**
	 * Constructor
	 *
	 * @param PDOStatement $statement Statement, that should be wrapped to this recordset
	 * @param array $params Query parameters
	 */
	public function __construct(PDOStatement $statement, array $params) {
		$this->_statement = $statement;
		$this->_params = $params;
	}
 
	/**
	 * Reexecutes recordset query with the same parameters
	 * This function is called inside @see rewind()
	 *
	 */
	public function refresh() {
		$this->_statement->execute($this->_params);
		if ($this->_statement->errorCode() !== '00000') {
			throw new FL_DB_Exception($this->_statement->errorInfo());
		}
	}
 
	/**
	 *
	 * @see Iterator::current()
	 */
	public function current() {
		return $this->_currentRowObj;
	}
 
	/**
	 *
	 * @see Iterator::key()
	 */
	public function key() {
		return $this->_currentRowIndex;
	}
 
	/**
	 *
	 * @see Iterator::next()
	 */
	public function next() {
		$this->_currentRowObj = $this->_statement->fetchObject('FL_DB_RecordsetObj');
		if ($this->_statement->errorCode() !== '00000') {
			throw new FL_DB_Exception($this->_statement->errorInfo());
		}
		$this->_currentRowIndex ++;
		return $this->_currentRowObj;
	}
 
	/**
	 *
	 * @see Iterator::rewind()
	 */
	public function rewind() {
		$this->refresh();
		$this->_currentRowIndex = 0;
		$this->_currentRowObj = $this->_statement->fetchObject('FL_DB_RecordsetObj');
		if ($this->_statement->errorCode() !== '00000') {
			throw new FL_DB_Exception($this->_statement->errorInfo());
		}
 
	}
 
	/**
	 *
	 * @see Iterator::valid()
	 */
	public function valid() {
		return $this->_currentRowObj !== false;
	}
 
	/**
	 *
	 * @see Countable::count()
	 */
	public function count() {
		return $this->_statement->rowCount();
	}
 
	/**
	 *
	 */
	function __destruct() {
		$this->_statement->closeCursor();
	}
}
?>

Helper classes

Now all is rest (if you need this of course) is to implement two helper methods for INSERT and UPDATE (I use these two often and want a convinient way to call them).

<?php
	/**
	 * Quick insert routine
	 *
	 * @param string $tablename Name of the table to which to insert the data
	 * @param array $data Assoc array of fieldName => fieldValue to insert into table
	 * @param array $driverOptions Optional driver options
	 * @return integer A number of rows affected by last operation
	 * 	 */
	public function insert($tablename, array $data, array $driverOptions = array()) {
		//Forming initial SQL skeleton INSERT INTO table(field1, field2,...) VAlUES(
		$sql = 'INSERT INTO ' . $tablename . '(' . implode(', ', array_keys($data)) . ') VALUES (';
 
		//Now making a parameter for each field (field1 => :field1...)
		foreach ( $data as $fieldName => $fieldValue ) {
			$sqlFieldParams [] = ':' . $fieldName;
		}
 
		//Listing params
		$sql .= implode(', ', $sqlFieldParams) . ')';
 
		//Calling our execute by passing parameters
		return $this->execute($sql, $data, $driverOptions);
	}
	/**
	 * Quick update routine
	 *
	 * @param string $tablename Name of the table to which to insert the data
	 * @param array $data Assoc array of fieldName => fieldValue to insert into table
	 * @param array $driverOptions Optional driver options
	 * @return integer A number of rows affected by last operation
	 * 	 */
	public function update($tablename, array $data, $criteria, array $driverOptions = array()) {
		$sql = 'UPDATE ' . $tablename.' SET ';
		foreach ( $data as $fieldName => $fieldValue ) {
			$sqlFieldParams [] = $fieldName . '=:' . $fieldName;
		}
		$sql .= implode(', ', $sqlFieldParams) . ' WHERE ' . $criteria;
 
		return $this->execute($sql, $data, $driverOptions);
	}

You should pay attention, to the fact, that no field names are escaped here (in MySQL you can do that by using “`” sign. If you need that, you can add that as well as parameter names normalization (removing special characters from parameter names, that are allowed in field names, but prohibited as parameters. For me it is not much important because I never use “strange” field names and always try to follow simple naming conventions.

Tips & Tricks

I don’t like to copy&paste my classes into each new project. That makes maintaining and bug hunting complex for me. So, I left my FL_DB_Connection and the rest in a piece. For actual database communication in each project I create a singleton class like this:

<?php
 
/**
 * Singleton wrapper for database class
 *
 */
class FR_DB extends FL_DB_Connection {
 
	private static $_instance = null;
 
	/**
	 * Singleton pattern function
	 *
	 * @return FR_DB
	 */
	public static function getInstance() {
		if (null == FR_DB::$_instance) {
			FR_DB::$_instance = new FR_DB();
		}
		return FR_DB::$_instance;
	}
 
	public function __construct() {
		global $FR_DB_DRIVEROPTIONS;
		parent::__construct(FR_DBCONNSTR, FR_DB_USER, FR_DB_PASSWORD, $FR_DB_DRIVEROPTIONS);
	}
}
?>
[/php]
I connect to database by $dbConn = FR_DB::getInstance().
Constants like FR_DBCONNSTR are db connection settings from current project.
Some examples (code parts from my projects)
[codesyntax lang="php]
<?php
		$dbConn = FR_DB::getInstance();
		$userData = $dbConn->getOneRow('SELECT * FROM user WHERE user_id=?',
			$sessData->user_id);
 
		$update ['user_account_status'] = self::ACCOUNTSTATUS_SUSPENDED;
		$update ['user_closure_reason'] = date('d-m-Y') . ': ' . $adminReason;
		$update ['user_closure_reason_public'] = date('d-m-Y') . ': ' . $publicReason;
		$dbConn->update('user', $update, 'user_id=' . $this->getId());
 
		$seeding = $dbConn->getOneValue(
			'SELECT COUNT(DISTINCT peer_info_hash) FROM peer USE INDEX (user_id) WHERE peer_expire_time > NOW() AND user_id=?' .
				 $additionalCondition, $this->getId());
UPDATE 02.05.2011: Please take a look at my GitHub repository where enhanced version of what we created here is published: https://github.com/FractalizeR/phpSweetPDO

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.