PDO for Fun and Profit (mostly fun)

Connecting and retrieving information from the database is usually the least sexy part of writing an app and so gets left behind a lot. The following classes, which are going to be part of the forum script, (more or less) should take some of the dreariness out of CRUD; that’s Create, Read, Update and Delete. The cornerstones of our universe. Shout out to Kevin Worthington. (Do people still say “shout out”? Man, I feel old.)

Let’s begin with the (semi) universal PDO connector. If you use Postgres, you can include your entire connection string in one line including username and password :

//PostgreSQL :
define( 'DBH', 'pgsql:host=localhost;dbname=hitlist;user=MichaelCorleone;password=IKnowItWasYouFredo' );

However the MySQL PDO driver will complain if you just include a single string like that as it demands the username and password separately. So, you’ll have to do the following :

//MySQL PDO connection (usually)
define( 'DBH', 'mysql:host=localhost;dbname=hitlist' );
define( 'DB_USER', 'MichaelCorleone' );
define( 'DB_PASS', 'IKnowItWasYouFredo' );

$dbh = new PDO( DBH, DB_USER, DB_PASS );

This is annoying.

There’s also the problem of security. If you just leave the connection strings in your script and for some reason the script gets served in plain-text (this happens far more often than you might think) there go your keys to the castle. The safest place to store connection strings is in your php.ini.

You can put the DSN name in a php.ini config as follows :

php.dsn.mydsn ='mysql:host=localhost;dbname=hitlist;username=MichaelCorleone;password=IKnowItWasYouFredo'

Now it’s the same as the (sane) Postgresql driver. But now you need a way to grab this complete DSN and dissect the username and password as necessary.

I wrote a handy class for this.

<?php
/**
 * PDO Connector class
 * Modifies the DSN to parse username and password individually.
 * Optionally, gets the DSN directly from php.ini.
 *
 * @author Eksith Rodrigo <reksith at gmail.com>
 * @license http://opensource.org/licenses/ISC ISC License
 * @version 0.2
 */

namespace Models;
 
class Cxn {
	
	/**
	 * @var object PDO connection.
	 */
	protected $db;
	
	
	public function __construct( $dbh ) {
		$this->connect( $dbh );
	}
	
	
	public function __destruct() {
		$this->db = null;
	}
	
	
	public function getDb() {
		
		if ( is_object( $this->db ) ) {
			return $this->db;
		} else {
			die('There was a database problem');
		}
	}

	private function connect( $dbh ) {
		if ( !empty( $this->db ) && is_object( $this->db ) ) {
			return;
		}
		
		try {
			$settings = array(
				\PDO::ATTR_TIMEOUT		=> "5",
				\PDO::ATTR_ERRMODE		=> 
						\PDO::ERRMODE_EXCEPTION,
				\PDO::ATTR_DEFAULT_FETCH_MODE	=> 
						\PDO::FETCH_ASSOC,
				\PDO::ATTR_PERSISTENT		=> true
			);
			
			$this->_dsn( $dbh, $username, $password );
			if ( !defined( 'DBTYPE' ) ) {
				$this->setDbType( $dbh );
			}
			
			if ( 'mysql' === DBTYPE || 'postgres' === DBTYPE) {
				/**
				 * Might be slightly slower, but more secure to 
				 * disable emulation
				 */
				$settings[\PDO::ATTR_EMULATE_PERPARES] = false;
			}
			
			$this->db = new \PDO( $dbh, $username, $password, 
						$settings );
			
			
		} catch ( \PDOException $e ) {
			exit( $e->getMessage() );
		}
	}
	
	/**
	 * Extract the username and password from the DSN and rebuild the 
	 * connection string
	 * 
	 * @param string $dsn Full connection string or DSN identifyer in php.ini
	 * @param string $username Optional username. If empty, it will extract from DSN
	 * @param string $password Also optional and will extract from DSN as above
	 */
	private function _dsn( &$dsn, &$username = '', &$password = '' ) {
		
		/**
		 * No host name with ':' would mean this is a DSN name in php.ini
		 */
		if ( false === strrpos( $dsn, ':' ) ) {
			
			/**
			 * We need get_cfg_var() here because ini_get doesn't work
			 * https://bugs.php.net/bug.php?id=54276
			 */
			$dsn = get_cfg_var( "php.dsn.$dsn" );
		}
		
		/**
		 * Some people use spaces to separate parameters in DSN strings 
		 * and this is NOT standard
		 */
		$d = explode( ';', $dsn );
		$m = count( $d );
		$s = '';
		
		for( $i = 0; $i < $m; $i++ ) {
			$n = explode( '=', $d[$i] );
			
			/**
			 * Empty parameter? Continue
			 */
			if ( count( $n ) <= 1 ) {
				$s .= implode( '', $n ) . ';';
				continue;
			}
			
			/**
			 * Username or password?
			 */
			switch( trim( $n[0] ) ) {
				case 'uid':
				case 'user':
				case 'username':
					$username = trim( $n[1] );
					break;
				
				case 'pwd':
				case 'pass':
				case 'password':
					$password = trim( $n[1] );
					break;
				
				/**
				 * Some other parameter? Leave as-is
				 */
				default:
					$s .= implode( '=', $n ) . ';';
			}
		}
		
		$dsn = rtrim( $s, ';' );
	}
	
	/**
	 * Sets the DBTYPE defined variable.
	 * Useful for database specific SQL.
	 * Expand as necessary.
	 */
	private function setDbType( $dsn ) {
		
		if ( 0 === strpos( $dsn, 'mysql' ) ) {
			
			define( 'DBTYPE', 'mysql' );
			
		} elseif ( 0 === strpos( $dsn, 'postgres' ) ) {
			
			define( 'DBTYPE', 'postgres' );
			
		} elseif ( 0 === strpos( $dsn, 'sqlite' ) ) {
			
			define( 'DBTYPE', 'sqlite' );
			
		} else {
			define( 'DBTYPE', 'other' );
		}
	}
}

Using this is pretty simple :

$cxn = new Cxn( 'mydsn' ); // Where php.dsn.mydsn is in php.ini
$db = $cxn->getDb();
$st = $db->prepare( 'SELECT * FROM whacks' );

$st->execute();
$whacks = $st->fetchAll();

Of course you want your family to be completely clean in a few years or Kay will try to leave you and take the kids with her. To prevent this and hide any whacks you do commit from Kay, we need some OOP and I’ve used this class on a previous project (not specifically to whack anyone) where I needed object/model separation. Hence the namespace will again be “Models”, same as above.

<?php
/* 
Example MySQL schema: 
CREATE TABLE whacks (
	id int(10) unsigned NOT NULL auto_increment,
	name varchar(100) NOT NULL,
	method varchar(100) NOT NULL,
	reason text NOT NULL,
	created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at datetime NOT NULL,
	whacked_at timestamp NOT NULL,
	PRIMARY KEY (id)
);
*/

namespace Models;

class Whack extends base {
	
	/**
	 * @var string Keep your friends close, but your enemies closer 
	 */
	public $name	= '';
	
	
	/**
	 * @var string Stupid thugs. People behaving like that with guns.
	 */
	public $method	= '';
	
	
	
	/**
	 * @var string I don't feel I have to wipe everybody out, Tom. 
	 *		Just my enemies.
	 */
	public $reason = '';
	
	
	/**
	 * @var string He's has been dying from the same heart attack 
	 *		for the last twenty years.
	 */
	public $whacked_at= '';
	
	
	public function __construct( array $data = null ) {
		
		if ( empty( $data ) ) {
			return;
		}
		
		foreach ( $data as $field => $value ) {
			$this->$field = $value;
		}
	}
	
	
	public function save() {
		$row		= 0;
		$params = array(
			'name'		=> $this->name,
			'method'	=> $this->method
		);
		
		
		/**
		 * I don't want anything to happen to him while my mother's alive
		 */
		if ( !empty( $this->whacked_at ) ) {
			$this->whacked_at = parent::_myTime( $this->whacked_at );
			$params['whacked_at'] = $this->whacked_at;
		}
		
		/**
		 * Sal, Tom, the boss says he'll come in a separate car.
		 */
		if ( isset( $this->id ) ) {
			$params['id'] = $this->id;
			parent::edit( 'whacks', $params );
		} else {
			parent::put( 'whacks', $params );
		}
	}
	
	
	/**
	 * I want you to find out what he's got under his fingernails.
	 */
	public static function find( $filter = array() ) {
		$sql = "SELECT id, name, method, reason, created_at, whacked_at 
				FROM whacks";
		
		// Filtering cleanup
		$filter = parent::filterConfig( $filter );
		$params = array();
		
		// Get by id
		if ( $filter['id'] > 0 ) {
			$sql .= " WHERE id = :id";
			$param['id'] = $filter['id'];
		}
		
		// Get by name
		if ( isset( $filter['name'] ) ) {
			if ( false !== strrpos( $sql, 'WHERE' ) ) {
				$sql .= " OR";
			} else {
				$sql .= " WHERE";
			}
			$sql .= " name = :name";
			$param['name'] = $filter['name'];
		}
		
		// Get 10, 20 results etc...
		if ( isset( $filter['limit'] ) ) {
			$sql .= " LIMIT :limit";
			$param['limit'] = $filter['limit'];
		}
		
		// Pagination
		if ( isset( $filter['offset'] ) ) {
			$sql .= " OFFSET :offset";
			$param['offset'] = $filter['offset'];
		}
		
		// Many whacks. Family's been busy.
		if ( isset( $filter['limit'] ) && $filter['limit'] > 1 )
			return parent::find( $sql, $params );
		}
		
		// One whack (note the 'true' for a single object)
		return parent::find( $sql, $params, true );
	}
	
	
	/**
	 * Tessio? I always thought it would be Clemenza.
	 */
	public static function delete( $params = array() ) {
		parent::delete( 'whacks', $params );
	}
}

You’ll notice there’s a base class this inherits from. The base is what’s referred to in all those “parent::” methods for creating (put), editing and, of course, deleting because no self-respecting member of the family will be too stubborn, lest he wishes to get gunned down on the causeway.

Here’s the parent class (You don’t need to manually assign the DSN to this; just have it as a defined variable) :

<?php
/**
 * Base object modal for finding, editing, deleting objects by table
 * This class handles connecting to a database if necessary and so it 
 * depends on the 'Cxn' class and requires DBH to be defined.
 * 
 * @author Eksith Rodrigo <reksith at gmail.com>
 * @license http://opensource.org/licenses/ISC ISC License
 * @uses Cxn
 */

namespace Models;

abstract class base {
	
	/**
	 * @var int Class object unique identifier (every class should have one)
	 */
	public $id;
	
	
	/**
	 * @var int Class object creation date. Should not modified.
	 */
	public $created_at;
	
	
	/**
	 * @var int Class object edited/saved date. Must be modified.
	 */
	public $updated_at;
	
	
	/**
	 * @var int Special status. Relevance will differ per object.
	 * @example A Post with status = -1 may be 'hidden' from view
	 */
	public $status;
	
	
	/**
	 * @var object PDO connection.
	 */
	protected static $db = null;
	
	
	/**
	 * @var int Total number of queries executed.
	 */
	public static $qcount = 0;
	
	
	/**
	 * Checks PDO connection or assigns to self::$db if it hasn't been set 
	 * and a new one has been passed.
	 * 
	 * @param object $db PDO connection
	 * @return bool True of the variable was set. False on failure.
	 */
	protected static function _db( $db = null ) {
		
		if ( is_object( self::$db ) ) {
			return true;
		} elseif ( is_object( $db ) ) {
			self::$db = $db;
			return true;
		}
		
		return false;
	}
	
	
	/**
	 * Clean up
	 */
	public function __destruct() {
		
		if ( is_object( self::$db ) ) {
			self::$db = null;
		}
	}
	
	
	/**
	 * Setup local Cxn instance with PDO 
	 * Note: This depends on the Cxn class and also 
	 * DBH (your connection string) being set
	 */
	public static function init() {
		
		if ( !self::_db() ) {
			$db = new Cxn( DBH );
			self::_db( $db->getDb() );
		}
	}
	
	
	/**
	 * Find objects using the given sql and parameters
	 * 
	 * @param string $sql Database query
	 * @param array $params Selector parameters in 'column' => 'value' format
	 * @return object Of the same type as the class calling this or null on failure
	 * @return mixed Single object or array of the same type as the class calling 
	 * 			this or null on failure
	 */
	protected static function find( $sql, $params, $single = false ) {
		
		self::init();
		
		$class	= get_called_class();
		$stmt	= self::$db->prepare( $sql );
		
		if ( $stmt->execute( $params ) ) {
			self::$qcount++;
			if ( $single ) {
				return $stmt->fetchObject( $class );
			} else {
				return $stmt->fetchAll( 
					\PDO::FETCH_CLASS, $class );
			}
		}
		return null;
	}
	
	
	/**
	 * Insert a single row into a table
	 * 
	 * @param string $table Database table name
	 * @param array $params Insert values in 'column' => 'value' format
	 * @return int The ID of the newly inserted row or 0 on failure
	 */
	protected static function put( $table, $params ) {
		
		self::init();
		
		$sql	= self::_insertStatement( $table, $params );
		$stmt	= self::$db->prepare( $sql );
		
		if ( $stmt->execute( $params ) ) {
			self::$qcount++;
			if ( 'postgres' === DBTYPE ) {
				return self::$db->lastInsertId( 'id' );
			}
			return self::$db->lastInsertId();
		}
		
		return 0;
	}
	
	
	/**
	 * Update records in a single table
	 * 
	 * @param string $table Database table name
	 * @param array $params Column parameters (id required)
	 * @return int Number of rows affected
	 */
	protected static function edit( $table, $params ) {
		
		if ( !isset( $params['id'] ) ) {
			return 0;
		}
		
		$id	= $params['id'];
		unset( $params['id'] );
				
		$sql = self::_updateStatement(
			$table, $params, "$table.id = :id"
		);
		
		self::init();
		$params['id']	= $id;
		$stmt		= self::$db->prepare( $sql );
	
		if ( $stmt->execute( $params ) ) {
			self::$qcount++;
			return $stmt->rowCount();
		}
		return 0;
	}
	
	
	/**
	 * Delete from a single table based on parameters
	 * 
	 * @param string $table Table name (only one) to delete from
	 * @param array $params Delete selectors
	 * 
	 * @example Deleting a post with ID = 223
	 * 		base::delete( 'posts', array( 'id' = 223 ) );
	 * 
	 * @return int Number of rows affected/deleted
	 */
	protected static function delete( $table, $params ) {
		
		self::init();
		
		$sql	= self::_deleteStatement( $table, $params );
		$stmt	= self::$db->prepare( $sql );
		
		if ( $stmt->execute( $params ) ) {
			self::$qcount++;
			return $stmt->rowCount();
		}
		return 0;
	}
	
	
	/**
	 * Add parameters to conditional IN/NOT IN ( x,y,z ) query
	 */
	protected function _addParams( $t, &$values, &$params = array(), &$in = '' ) {
		
		$vc = count( $values );
		for ( $i = 0; $i < $vc; $i++ ) {
			$in			= $in . ":val_{$i},";
			$params["val_{$i}"]	= array( $values[$i], $t );
		}
		$in = rtrim( $in, ',' );
	}
	
	
	/**
	 * Prepares parameters for SELECT, UPDATE or INSERT SQL statements.
	 * 
	 * E.G. For INSERT
	 * :name, :email, :password etc...
	 * 
	 * For UPDATE or DELETE
	 * name = :name, email = :email, password = :password etc...
	 */
	protected static function _setParams( 
		$fields = array(), 
		$mode = 'select', 
		$table = '' 
	) {
		$columns = is_array( $fields ) ? 
				array_keys( $fields ) : 
				array_map( 'trim', explode( ',', $fields ) );
		
		switch( $mode ) {
			case 'select':
				return implode( ', ', $columns );
				
			case 'insert':
				return ':' . implode( ', :', $columns );
			
			case 'update':
			case 'delete':
				$v = array_map( 
					function( $field ) use ( $table ) {
						
						if ( empty( $field ) ) { 
							return '';
						}
						return "$field = :$field";
					}, $columns );
				return implode( ', ', $v );
		}
	}
	
	
	/**
	 * Prepares SQL INSERT query with parameters matching field names
	 * 
	 * @param string $table Table name
	 * @param string|array $fields A comma delimited string of fields or 
	 * 		an array
	 * 
	 * @example field => value pairs (the 'field' key will be extracted as 
	 * 		the parameter)
	 * 		_insertStatement(
	 * 			'posts', 
	 * 			array( 
	 * 				'title' => 'Test title', 
	 * 				'author' => 'Guest'
	 * 			) 
	 * 		);
	 * 
	 * @return string;
	 */
	protected static function _insertStatement( $table, $fields ) {
		
		$cols = self::_setParams( $fields, 'select', $table );
		$vals = self::_setParams( $fields, 'insert', $table );
		return	"INSERT INTO $table ( $cols ) VALUES ( $vals );";
	}
	
	
	/**
	 * Prepares sql UPDATE query with parameters matching field names
	 * 
	 * @param string $table Table name
	 * @param string|array $fields A single field or comma delimited string 
	 * 		of fields or an array
	 * 
	 * @example field => value pairs (the 'field' key will be extracted as 
	 * 		the parameter)
	 * 		_updateStatement(
	 * 			'posts', 
	 * 			array( 
	 * 				'title' => 'Changed title', 
	 * 				'author' => 'Edited Guest' 
	 * 			),
	 * 			array( 'id' => 223 )
	 * 		);
	 * @return string;
	 */
	protected static function _updateStatement( 
		$table, 
		$fields = null, 
		$cond = ''
	) {
		$params = self::_setParams( $fields, 'update', $table );
		$sql	= "UPDATE $table SET $params";
		
		if ( !empty( $cond ) ) {
			$sql .= " WHERE $cond";
		}
		
		return $sql . ';';
	}
	
	
	/**
	 * Prepares sql DELETE query with parameters matching field names
	 * 
	 * @param string $table Table name
	 * @param string|array $fields A comma delimited string of fields or an 
	 * 		array of field => value pairs (the 'field' key will be 
	 * 		extracted as the parameter)
	 * @return string;
	 */
	protected static function _deleteStatement( 
		$table, 
		$fields = null, 
		$limit = null
	) {
		
		$params	= self::_setParams( $fields, 'delete', $table );
		$sql	= "DELETE FROM $table WHERE ( $params )";
		
		
		/**
	 	 * Limit must consist of an integer and not start with a '0'
	 	 */
		if ( null !== $limit && preg_match('/^([1-9][0-9]?+){1,2}$/', 
			$limit ) ) {
			$sql .= " LIMIT $limit";
		}
		
		return $sql . ';';
	}
	
	
	/**
	 * Pagination offset calculator
	 * Hard limit of 300 set for page since we rarely browse that many 
	 * casually. That's what searching is for ( also reduces abuse )
	 * 
	 * @param int $page Currently requested index (starting from 1)
	 * @param int $limit Maximum number of records per page
	 * @return int Offset
	 */
	protected static function _offset( $page, $limit ) {
		
		$page	= ( isset( $page ) )? ( int ) $page - 1 : 0;
		$limit	= ( isset( $limit ) )? ( int ) $limit : 0;
		
		if ( empty( $page ) || $page < 0 || 
			empty( $limit ) || $limit < 0
		) {
			return 0; 
		}
		return ( $page > 300 ) ? 0 : $page * $limit;
	}
	
	
	/**
	 * Convert a unix timestamp a datetime-friendly timestamp
	 * 
	 * @param int $time Unix timestamp
	 * @return string 'Year-month-date Hour:minute:second' format
	 */
	protected static function _myTime( $time ) {
		return gmdate( 'Y-m-d H:i:s', $time );
	}
	
	
	/**
	 * Sets filter configuration ( pagination, limit, id etc... )
	 */
	public static function filterConfig( &$filter = array() ) {
		
		$filter['id']		= isset( $filter['id'] ) ? 
						$filter['id'] : 0;
						
		$filter['limit']	= isset( $filter['limit'] ) ? 
						$filter['limit'] : 1;
						
		$filter['page']		= isset( $filter['page'] ) ? 
						$filter['page'] : 1;
						
		$filter['search']	= isset( $filter['search'] ) ? 
						$filter['search'] : '';
		
		$offset			= self::_offset( 
						$filter['page'] , 
						$filter['limit']
					);
		if ( $offset > 0 ) {
			$filter['offset'] = $offset;
		}
	}
}

This class was built to help me with a bunch of other classes as well, so I added a quick filterConfig there for a lot of the default parameters I’d use, including for offset calculation for pagination.

Putting it all together, you might use something like this to add a new entry :

 // Make sure, your full DSN is set in php.ini
// under pdo.dsn.mywhacks = 'sqlite:data/hitlist.sqlite' or something
define( 'DBH', 'mywhacks' );

$whack = new \Models\Whack();
$whack->name = "Carlo";
$whack->method = "garrote in the car";
$whack->reason = "Fingered Sonny for the Barzini people.";

// Baptism is already over so why wait? 
$whack->save();

To change something :

$fredo = \Models\Whack::find( array( 'name' => 'Fredo' ) );
$fredo->method = "shot while fishing";
$fredo->reason = "Almost got Michael killed. Took sides against the family.";

$fredo->whacked_at = time(); // Mamma's dead now
$fredo->save();

To delete :

\Models\Whack::delete( array( 'name' => 'Clemenza' ) );

And that’s about it.

Advertisement