When I’m writing more basic PHP applications without larger libraries I still use this very small class library that makes PDO chainable. I’m going to go over it to provide an example of how objects get extended and why.
This uses two classes, one which extends PDO_Statement and one which extends PDO itself. We’ll jump right in with the statement.
<?php
namespace MyNamespace;
/**
* @author Michael Morris
*/
class Statement extends \PDOStatement {
/**
* The database object that created us.
* @var Database
*/
protected $db = null;
/**
* Protected is NOT an error here, the
* PDOStatement object is a PHP internal
* construct.
*
* @param PDO $db
*/
protected function __construct( $db ) {
$this->db = $db;
}
/**
* Replacement for the execute statement.
*
* In addition to being chainable, the
* input array isn't required to have
* leading colons on the names - they'll
* be added by the class.
*
* We may also provide a non-array
* argument. If this is done the
* argument gets wrapped by an array.
*
* @param mixed input parameter(s).
* @see PDOStatement::execute()
* @return this
*/
public function parse( $params = null ) {
if (is_array($params) ||
(is_object($params) && $params instanceof \Traversable )
) {
$params = $this->prepareArray($params);
}
elseif ( !is_null($params) && !is_array($params)) {
$params = [strval($params)];
}
$this->execute( $params );
return $this;
}
/**
* Prepares an array for use as a
* query parameter set.
*
* @param array $params
* @return array
*/
protected function prepareArray( $params ) {
$return = [];
// No pass by reference here because
// we're changing keys.
foreach ( $params as $key => $value ) {
if (!is_numeric($key)
&& strpos($key, ':') !== 0
) {
$key = ':' . $key;
}
$return[$key] = $value;
}
return $return;
}
/**
* A chainable #closeCursor.
* @see PDOStatement::closeCursor()
* @return this
*/
public function closeQuery() {
$this->closeCursor();
return $this;
}
/**
* A chainable #bindValue;
* @see PDOStatement::bindValue()
* @return this
*/
public function bindVal($parameter, $value, $data_type = \PDO::PARAM_STR ) {
parent::bindValue($parameter, $value, $data_type);
return $this;
}
/**
* Bind an array to the statement.
*
* If the keys are named you must use
* named placeholders in your statement.
* If the keys are not named you must use
* question mark placeholders.
*
* @param array $array
*/
public function bindArray( $array ) {
$array = $this->prepareArray($array);
foreach ( $array as $key => $value ) {
$this->bindValue(
is_numeric($key) ? $key+1 : $key,
$value,
is_int($value) ? \PDO::PARAM_INT : \PDO::PARAM_STR
);
}
return $this;
}
/**
* Return a single value, or a single row,
* inferred from your query's structure.
*
* @return string
*/
public function result( $params = null ) {
if (!is_null($params)) {
$this->parse($params);
}
return $this->columnCount() == 1 ?
$this->fetchColumn() :
$this->fetch( \PDO::FETCH_ASSOC );
}
/**
* Return all results for a query.
*
* The return is inferred from your query.
*
* Query with 1 col, return that column.
* Query with 2 cols, return first col
* as key, second as value.
* Otherwise return results indexed on
* first column.
*
* If you don't want this behavior
* (indexing the first column as a key)
* you can still call the native PDO
* fetchAll statement off this object.
*/
public function results( $params = null ) {
if (!is_null($params)) {
$this->parse($params);
}
return $this->columnCount() == 1 ?
$this->fetchAll( \PDO::FETCH_COLUMN ) :
$this->fetchAll(\PDO::FETCH_GROUP | \PDO::FETCH_UNIQUE|(
$this->columnCount() == 2 ? \PDO::FETCH_COLUMN : \PDO::FETCH_ASSOC)
);
}
}
Note that the above is placed in its own namespace. Also, it doesn’t touch the existing PDO statements so it will stay out of the way of any class that wants to use a PDO object. The convenience methods allow for quickly pulling data from queries. Write operations are represented for a reason - they involve a level of validation I don’t want to get into here.
Course, to make the above useful, we have to also extend the base PDO class a bit. Here we go…
<?php
namespace MyNamespace;
/**
* Extends the PDO with some convenience.
*/
class Database extends \PDO {
/**
* {@inheritdoc}
*/
public function __construct( array $config ) {
assert (
((isset($config['database']) && $config['database']) ||
(isset($config['dsn']) && $config['dsn'])) &&
isset($config['user']) && isset($config['password'])
);
if (isset($config['dsn'])) {
// If a DSN is set use it without question.
parent::__construct($config['dsn'], $config['user'], $config['password']);
} else {
// Otherwise hash the vars we were given into a DSN and pass that.
$params = array(
'driver' => isset($config['driver']) ? $config['driver'] : 'mysql',
'database' => $config['database'],
'user' => $config['user'],
'password' => $config['password'],
'server' => isset($config['server']) ? $config['server'] : 'localhost',
'port' => isset($config['port']) ? $config['port'] : '3306'
);
// Start underlying PDO library.
parent::__construct("{$params['driver']}:dbname={$params['database']};host={$params['server']};port={$params['port']}",
$params['user'],
$params['password']
);
}
// Bind our custom statment object.
$this->setAttribute(
static::ATTR_STATEMENT_CLASS,
[__NAMESPACE__ . '\\Statement', [$this]]
);
}
/**
* Quickly return a single value, or a single row,
* as determined by your query structure.
* @param SQL string
* @param mixed
* @return string
*/
public function result( $sql, $params = null ) {
return $this->prepare($sql)
->parse($params)
->result();
}
/**
* Return multiple results. See Statement::results()
*
* @param SQL String
* @param mixed
* @return array
*/
public function results( $sql, $params = null ) {
return $this
->prepare($sql)
->parse($params)
->results();
}
}
The upshot of this pair of extensions again is read queries become lightning fast to write - almost too easy to be honest since you can choke your system by loading a result set that runs you out of memory if you aren’t careful. Want all results off the personnel table?
<?php $personnel = $db->results("SELECT * FROM personnel"); ?>This does assume you built the table with the index as the first column which is common practice. If you didn’t hilarity will ensue.
At the end of the day these classes make PDO more pleasant to work with but they aren’t a panacea by any means. My full version of them has a few more tricks up its sleeve but since I got involved with Drupal its fell by the wayside. It’s presented here in the hopes that it will be useful, and as an instructional guide on extending classes - both how and why. Note the following:
- Since PDO is a class that gets used by other products a lot, I intentionally avoided overwriting any existing method.
- PHP 5.6’s splat operator is a cleaner way of passing along arguments from these new functions to their antecedents - but the application of those features is an exercise left to the reader.
- This code was adjusted to fit cleanly into this post without debugging. You’ve been warned.