<?php
/**
* PDO database wrapper for MySQL.
*/
/**
* Simple database wrapper making use of PHP's PDO.
*
* This wrapper simply abstracts away the PDO interface, as well as providing a few nice functions.
* For example, an 'INSERT' query builder is included.
*/
class Database
{
/**
* Hostname of the machine with the database.
*
* @var string
*/
protected $host;
/**
* Username to use to connect to the database.
*
* @var string
*/
protected $username;
/**
* Password to use to connect to the database.
*
* @var string
*/
protected $password;
/**
* Name of database to use.
*
* @var string
*/
protected $database;
/**
* Handle to the database.
*
* @var PDO
*/
protected $handle = null;
/**
* Constructor - sets up variables and connects to the database.
*
* An exception is thrown if any parameters are missing.
*
* @param string $host hostname to connect to
* @param string $username database username
* @param string $password database password
* @param string $database name of database to use
* @param bool $autoConnect automatically connect to database
* @see connect
*/
public function __construct($host, $username, $password, $database, $autoConnect = true)
{
$this->host = $host;
$this->username = $username;
$this->password = $password;
$this->database = $database;
if ($autoConnect)
$this->connect();
}
/**
* Destructor - disconnect from the database and free associated resources.
*
* @see disconnect
*/
public function __destruct()
{
$this->disconnect();
}
/**
* Connects to the database.
*
* This method attempts to instantiate a new instance of PDO to connect to the database, using the credentials provided in the constructor.
* If successful, the handle is set, otherwise an exception is thrown.
*
* @see __construct
* @see $handle
*/
public function connect()
{
try {
$this->handle = new PDO('mysql:host=' . $this->host . ';dbname=' . $this->database, $this->username, $this->password);
/* turn on exception throwing */
$this->handle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (Exception $e) {
throw new DatabaseException('Could not connect to the database: ' . $e->getMessage());
}
}
/**
* Disconnects from the database.
*
* This method simply sets the handle to null, causing disconnection from the database, and the freeing of associated resources.
*
* @see __destruct
* @see $handle
*/
public function disconnect()
{
$this->handle = null;
}
/**
* Performs a query against the database.
*
* This method performs a database query, and returns an object of results which can be iterated over.
*
* For example:
* <code>
* <?php
* $result = $database->query('SELECT * FROM user;');
* foreach ($result as $user)
* echo $user->username;
* ?>
* </code>
* Both traditional and prepared statements are supported.
* Prepared statements are automatically escaped and hence guarded against SQL injection by PDO.
* For examples of prepared statements, see execute(), as usage is identical.
*
* @param string $sql Query to execute.
* @return PDOStatement|bool Results object/false.
* @see execute
*/
public function query($sql)
{
/* fatal error: cannot use func_get_args() as function argument */
$argv = func_get_args();
$query = $this->handle->prepare($sql);
$query->setFetchMode(PDO::FETCH_OBJ);
$data = $this->makeDataArray(func_num_args(), $argv);
$result = $query->execute($data);
if ($result === false)
return false;
/* return the fetched object, so we can use the data right away */
else if ($query->rowCount() == 1)
return $query->fetch(PDO::FETCH_OBJ);
else
return $query;
}
/**
* Executes a database query.
*
* This method simply executes a database query and returns the number of rows affected. If there was some error, false is returned.
* This is in contrast to query(), which returns an object of results.
* Both traditional and prepared statements are supported.
* Prepared statements may either specify an 'param' => 'value' array, or by specifying an unlimited number of arguments.
* Prepared statements are automatically escaped and hence guarded against SQL injection by PDO.
*
* Example:
* <code>
* <?php
* $database->execute('DELETE FROM bank WHERE userid = :userid;', $userID);
*
* $data = array('points' => 50, 'name' => 'Frank', 'color' => 'red', 'apple' => 'Golden Delicious');
* $database->execute('UPDATE users SET points = :points, name = :name, color = :color, apple = :apple', $data);
* ?>
* </code>
*
* @param string $sql Query to execute.
* @return int|bool Number of rows affected/success.
*/
public function execute($sql)
{
/* fatal error: cannot use func_get_args() as function argument */
$argv = func_get_args();
$data = $this->makeDataArray(func_num_args(), $argv);
$query = $this->handle->prepare($sql);
if ($query->execute($data) === false)
return false;
else
return $query->rowCount();
}
/**
* Inserts data to the database.
*
* This method simplifies the process of inserting data into the database.
* An array in column => value form is passed, which is converted to (column) VALUES (value) pairs.
* This is done using PDO prepared statements, so all data is automatically escaped eliminating SQL injection.
*
* @param string $table Name of table to insert to.
* @param array $data An array of data in column => value form.
* @return bool Success.
*/
public function insert($table, $data)
{
$columns = array();
$placeholders = array();
foreach ($data as $key => $val) {
$columns[] = $key;
$placeholders[] = ":$key";
}
$columns = implode(', ', $columns);
$placeholders = implode(', ', $placeholders);
$sql = "INSERT INTO $table ($columns) VALUES ($placeholders);";
$query = $this->handle->prepare($sql);
foreach ($data as $key => $val)
$query->bindValue(":$key", $val);
return $query->execute();
}
/**
* Begins a database transaction.
*/
public function beginTransaction()
{
$this->handle->beginTransaction();
}
/**
* Commits a database transaction.
*/
public function commit()
{
$this->handle->commit();
}
/**
* Rolls back a database transaction.
*/
public function rollBack()
{
$this->handle->rollBack();
}
/**
* Creates a data array for a prepared statement.
*
* This method takes a func_get_args() result and converts it to an array that can be used in PDOStatement::execute.
*
* @param int $argc Argument count.
* @param array $argv Array of arguments.
* @return array Data array.
* @see query
* @see execute
*/
protected function makeDataArray($argc, $argv)
{
$data = array();
/* prepared statement with array */
if ($argc == 2 && is_array($argv[1])) {
$data = $argv[1];
/* prepared statement with params */
} else {
for ($i = 1; $i < $argc; $i++)
$data[] = $argv[$i];
}
return $data;
}
}
/**
* Empty exception for Database.
*
* @see Database
*/
class DatabaseException extends Exception
{
}
?>
Refactorings
No refactoring yet !
Tom
October 28, 2009, October 28, 2009 14:03, permalink
1 rating.
Login to rate!
Hi nice class.
Maybe I'm not reading it right, but what happens if you do:
$result = $yourPDO->query("SELECT * FROM article;");
foreach($result as $anArticle) {
echo $anArticle->title;
}
...when there is only a single article in the [article] table?
I'd expect my SELECT statement to return an iterable result set of articles, but it looks like you'd only return a single (and non-foreach-able) object?
I'd suggest adding a flag to the query method to specify whether the caller is expeting a single result or not. Alternatively add a querySingle($sql) method as below?
Thus allowing the called to specify whether it expects a result set or a single object.
//um... this is not tested :)
/**
* Performs a query against the database and returns a single result object.
*
* This method performs a database query, and returns a single object. If the SQL results in multiple rows,
* the first row will be used.
*
* For example:
* <code>
* <?php
* $result = $database->querySingle('SELECT * FROM user WHERE id=69;');
* if ($result) {
* echo $user->username;
* }
* ?>
* </code>
* Both traditional and prepared statements are supported.
* Prepared statements are automatically escaped and hence guarded against SQL injection by PDO.
* For examples of prepared statements, see execute(), as usage is identical.
*
* @param string $sql Query to execute.
* @return object|bool Results object/false.
* @see execute
*/
public function querySingle($sql)
{
/* fatal error: cannot use func_get_args() as function argument */
$argv = func_get_args();
$query = $this->handle->prepare($sql);
$query->setFetchMode(PDO::FETCH_OBJ);
$data = $this->makeDataArray(func_num_args(), $argv);
$result = $query->execute($data);
if ($result === false)
return false;
/* return the fetched object, so we can use the data right away */
else
return $query->fetch(PDO::FETCH_OBJ);
}
Ralph
October 26, 2010, October 26, 2010 09:46, permalink
No rating.
Login to rate!
Why not simply fetchAll(PDO::FETCH_OBJ)? It returns either a foreach-able associative array, or an empty array if no result, or false if error.
public function query($sql)
{
/* fatal error: cannot use func_get_args() as function argument */
$argv = func_get_args();
$query = $this->handle->prepare($sql);
$query->setFetchMode(PDO::FETCH_OBJ);
$data = $this->makeDataArray(func_num_args(), $argv);
$result = $query->execute($data);
if ($result === false)
return false;
return $query->fetchAll();
}
Having tried PDO many moons ago and giving up on it, I recently gave it another shot. Fortunately, it has much improved since I last played with it, and I really quite like it. As such, I wrote this wrapper for it.
Let me know what you think.