Many developers who come to PHP from the Visual Basic set of languages wish to take their experience of database interfacing with them. In the VB world, ActiveX Data Objects (ADO) is used to interact with a database; since PHP has no native library for ADO, this can present a problem.
With this in mind, an ADO library was written for use by PHP developers many years ago, which would allow developers to directly port their existing code and interfaces to PHP. There are a few problems with this approach, as can be expected:
- The ADO library is written in PHP, and because it was written some time ago, the language used within the library is specific to PHP 4. Since this version of PHP has reached the end of its support lifecycle, it's highly unlikely that any issues that arise with the ADO interface will be fixed.
- Further, because the library is written in PHP, it's incredibly slow to load and execute, introducing multiple additional layers between the PHP business logic layer and the database.
With the advent of PHP 5, a native database access layer was introduced to the core language: PHP Data Objects (PDO). Since this layer interfaces directly with the PHP core, it can operate on a much more efficient level, and therefore loads and runs much more quickly. Furthermore, since it's a current extension to PHP, it is maintained and kept secure.
In the ideal case, any applications using ADO under PHP would be redeveloped to use PDO. For large applications, however, this is infeasible: some kind of layer must be introduced over PDO, to "fake" the functionality of ADO on behalf of the application. The following is just such a layer.
ADODB-PDO.php: PDO wrapper to provide an ADODB interface
define('ADODB_FETCH_NUM', PDO::FETCH_NUM);
define('ADODB_FETCH_ASSOC', PDO::FETCH_ASSOC);
/**
* Connection and query wrapper
*/
class ADODB_PDO
{
/** PDO connection to wrap */
private $_db;
/** Connection information (database name is public) */
private $connector;
private $dsn;
private $host;
private $user;
private $pass;
public $database;
/** Debug flag, publically accessible */
public $debug;
/** PDO demands fetchmodes on each resultset, so define a default */
private $fetchmode;
/** Number of rows affected by the last Execute */
private $affected_rows;
/**
* Constructor: Initialise connector
* @param connector String denoting type of database
*/
public function __construct($connector='mysql')
{
$this->connector = $connector;
}
/**
* Connect: Establish connection to a database
* @param host String
* @param user String [optional]
* @param pass String [optional]
* @param database String [optional]
*/
public function Connect($host, $user='', $pass='', $database='')
{
$this->host = $host;
$this->user = $user;
$this->pass = $pass;
$this->database = $database;
switch($this->connector)
{
case 'mysql':
$this->dsn = sprintf('%s:host=%s;dbname=%s',
$this->connector,
$this->host,
$this->database);
$this->_db = new PDO($this->dsn, $this->user, $this->pass);
$this->_db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
$this->fetchmode = ADODB_FETCH_ASSOC;
break;
}
}
/**
* SetFetchMode: Change the fetch mode of future resultsets
* @param fm Integer specified by constant
*/
public function SetFetchMode($fm)
{
$this->fetchmode = $fm;
}
/**
* Insert_ID: Retrieve the ID of the last insert operation
* @return String containing last insert ID
*/
public function Insert_ID()
{
return $this->_db->lastInsertId();
}
/**
* GetAll: Retrieve an array of results from a query
* @param sql String query to execute
* @param vars Array of variables to bind [optional]
* @return Array of results
*/
public function GetAll($sql, $vars=null)
{
$st = $this->DoQuery($sql, $vars);
return $st?$st->fetchAll():false;
}
/**
* CacheGetAll: Wrapper to emulate cached GetAll
* @param timeout int count of seconds for cache expiry
* @param sql String query to execute
* @param vars Array of variables to bind [optional]
* @return Array of results
*/
public function CacheGetAll($timeout, $sql, $vars=null)
{
return $this->GetAll($sql, $vars);
}
/**
* Execute: Retrieve a resultset from a query
* @param sql String query to execute
* @param vars Array of variables to bind [optional]
* @return ADODB_PDO_ResultSet object
*/
public function Execute($sql, $vars=null)
{
$st = $this->DoQuery($sql, $vars);
$this->affected_rows = $st->rowCount();
return $st?new ADODB_PDO_ResultSet($st):false;
}
/**
* CacheExecute: Wrapper to emulate cached Execute
* @param timeout int count of seconds for cache expiry
* @param sql String query to execute
* @param vars Array of variables to bind [optional]
* @return ADODB_PDO_ResultSet object
*/
public function CacheExecute($timeout, $sql, $vars=null)
{
return $this->Execute($sql, $vars);
}
/**
* Affected_Rows: Retrieve the number of rows affected by Execute
* @return The number of affected rows
*/
public function Affected_Rows()
{
return $this->affected_rows;
}
/**
* GetRow: Retrieve the first row of a query result
* @param sql String query to execute
* @param vars Array of variables to bind [optional]
* @return Array of data from first result
*/
public function GetRow($sql, $vars=null)
{
$st = $this->DoQuery($sql, $vars);
return $st?$st->fetch():false;
}
/**
* GetOne: Retrieve the first value in the first row of a query
* @param sql String query to execute
* @param vars Array of variables to bind [optional]
* @return String data of the requested value
*/
public function GetOne($sql, $vars=null)
{
$st = $this->DoQuery($sql, $vars);
return $st?$st->fetchColumn():false;
}
/**
* GetAssoc: Retrieve data from a query mapped by value of first column
* @param sql String query to execute
* @param vars Array of variables to bind [optional]
* @return Array of mapped data
*/
public function GetAssoc($sql, $vars=null)
{
$out = array();
$st = $this->DoQuery($sql, $vars);
if($st)
{
if($st->columnCount() > 2)
{
while($row = $st->fetch())
{
$rowidx = array_shift($row);
$out[$rowidx] = $row;
}
}
else if($st->columnCount == 2)
{
while($row = $st->fetch())
{
$rowidx = array_shift($row);
$out[$rowidx] = array_shift($row);
}
}
else $out = false;
}
else $out = false;
return $out;
}
/**
* GetCol: Retrieve the values of the first column of a query
* @param sql String query to execute
* @param vars Array of variables to bind [optional]
* @return Array of column data
*/
public function GetCol($sql, $vars=null)
{
$out = array();
$st = $this->DoQuery($sql, $vars);
if($st)
{
while($val = $st->fetchColumn()) $out[] = $val;
return $out;
}
else return false;
}
/**
* MetaColumns: Retrieve information about a table's columns
* @param table String name of table to find out about
* @return Array of ADODB_PDO_FieldData objects
*/
public function MetaColumns($table)
{
$out = array();
$st = $this->DoQuery('select * from '.$table);
for($i=0; $i<$st->columnCount(); $i++)
$out[] = new ADODB_PDO_FieldData($st->getColumnMeta($i));
return $out;
}
/**
* qstr: Quote a string for use in database queries
* @param in String parameter to quote
* @return String quoted by database
*/
public function qstr($in)
{
return $this->_db->quote($in);
}
/**
* quote: Quote a string for use in database queries
* @param in String parameter to quote
* @return String quoted by database
*/
public function quote($in)
{
return $this->_db->quote($in);
}
/**
* DoQuery: Private helper function for Get*
* @param sql String query to execute
* @param vars Array of variables to bind [optional]
* @return PDOStatement object of results, or false on fail
*/
private function DoQuery($sql, $vars=null)
{
$st = $this->_db->prepare($sql);
$st->setFetchMode($this->fetchmode);
if(!is_array($vars)) $vars = array($vars);
return $st->execute($vars)?$st:false;
}
}
/**
* Resultset wrapper
*/
class ADODB_PDO_ResultSet
{
/** PDO resultset to wrap */
private $_st;
/** One-time resultset information */
private $results;
private $rowcount;
private $cursor;
/** Publically accessible row values */
public $fields;
/** Public end-of-resultset flag */
public $EOF;
/**
* Constructor: Initialise resultset and first results
* @param st PDOStatement object to wrap
*/
public function __construct($st)
{
$this->_st = $st;
$this->results = $st->fetchAll();
$this->rowcount = count($this->results);
$this->cursor = 0;
$this->MoveNext();
}
/**
* RecordCount: Retrieve number of records in this RS
* @return Integer number of records
*/
public function RecordCount()
{
return $this->rowcount;
}
/**
* MoveNext: Fetch next row and check if we're at the end
*/
public function MoveNext()
{
$this->fields = $this->results[$this->cursor++];
$this->EOF = ($this->cursor == $this->rowcount) ? 1 : 0;
}
}
/**
* Table field information wrapper
*/
class ADODB_PDO_FieldData
{
public $name;
public $max_length;
public $type;
/**
* Constructor: Map PDO meta information to object field data
* @param meta Array from PDOStatement::getColumnMeta
*/
public function __construct($meta)
{
$lut = array(
'LONG' => 'int',
'VAR_STRING' => 'varchar'
);
$this->name = $meta['name'];
$this->max_length = $meta['len'];
$this->type = $lut[$meta['native_type']];
}
}
/**
* NewADOConnection: Thin wrapper to generate a new ADODB_PDO object
* @param connector String denoting type of database
* @return ADODB_PDO object
*/
function NewADOConnection($connector)
{
return new ADODB_PDO($connector);
}