PDO Database Class
Posted: Wed Nov 24, 2010 12:07 pm
Ive tried to make my own PDO database class as seen below.
Example Usage:
Im not sure how good it is at handling errors or doing other things like truncate etc.. Would someone be able to help me improve my script/code or provide me with a decent pdo database class?
Code: Select all
<?
/**
* News Muncher Database Class
*
* The Database class is meant to simplify the task of accessing
* information from the website's database.
*
* Written by: Ben Web Developer.com
* Last Updated: 25 October 2010
* Version: 0.1.0
*/
class Database {
/**
* The pdo database object
*
* @access private
* @var object
*/
private $dbh;
/**
* The mysqli prepared statement database object
*
* @access private
* @var object
*/
private $stmt;
/**
* Whether there is an active transaction in process
*
* @access private
* @var bool
*/
private $active_transaction;
/**
* Whether to show SQL/DB errors
*
* @since 0.1.0
* @var bool
*/
var $show_errors = false;
/**
* Amount of queries made
*
* @since 0.1.0
* @var int
*/
var $num_queries = 0;
/**
* Get insert id from last query
*
* @access public
* @since 0.1.0
* @var int
*/
public function getLastInsertId() {
return $this->dbh->lastInsertId( );
}
/**
* Get numbger of effected rows from last query
*
* @access public
* @since 0.1.0
* @var int
*/
public function getAffectedRows() {
return $this->stmt->rowCount();
}
/**
* Query the database
*
* @access public
* @since 0.1.0
* @var bool
*/
private function query($sql, $params) {
try {
// Connect to database
$this->dbh = new PDO("mysql:host=".DB_SERVER.";dbname=".DB_NAME.";", DB_USER, DB_PASS);
}
catch (PDOException $e) {
// Show errors if enabled
if($this->show_errors)
echo $e->getMessage();
// Log errors
$this->dbh = NULL;
return false;
}
try {
$this->active_transaction = false;
// Set the PDO error mode to exception
$this->dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Begin the transaction
$this->dbh->beginTransaction();
$this->active_transaction = true;
// Prepare the sql statement
$this->stmt = $this->dbh->prepare($sql);
// Bind the parameters
$this->stmt = $this->bind_params($this->stmt, $params);
// Execute the prepared statement
$this->stmt->execute();
// Commit the transaction
$this->dbh->commit();
$this->active_transaction = false;
$dbh = NULL;
$stmt = NULL;
return true;
}
catch (PDOException $e) {
// Roll back the transaction if something goes wrong
try {
if($this->active_transaction)
$this->dbh->rollBack();
}
catch (PDOException $e) {
// Show errors if enabled
if($this->show_errors)
echo $e->getMessage();
$dbh = NULL;
$stmt = NULL;
return false;
}
// Show errors if enabled
if($this->show_errors)
echo $e->getMessage();
$dbh = NULL;
$stmt = NULL;
//Log errors
return false;
}
}
/**
* Dynamically bind parameters
*
* @access private
* @since 0.1.0
* @var object
*/
private function bind_params($stmt, $params) {
foreach($params as $key => &$value)
{
$stmt->bindParam($key, $value);
}
return $stmt;
}
}
Code: Select all
$query = $db->query('INSERT INTO xxx (username) VALUES (:username)', array(':username' => 'test1'));