PDO Database Class

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
evans123
Forum Newbie
Posts: 18
Joined: Wed Nov 17, 2010 4:48 pm

PDO Database Class

Post by evans123 »

Ive tried to make my own PDO database class as seen below.

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;
	 }
	 
}
Example Usage:

Code: Select all

$query = $db->query('INSERT INTO xxx (username) VALUES (:username)', array(':username' => 'test1'));
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?
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: PDO Database Class

Post by alex.barylski »

Well what kind of enhanced functionality are you expecting a PDO class, default PDO is already an object.

Cheers,
Alex
evans123
Forum Newbie
Posts: 18
Joined: Wed Nov 17, 2010 4:48 pm

Re: PDO Database Class

Post by evans123 »

Im not sure on how to handle the errors and report back to the script which requested the sql task.
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: PDO Database Class

Post by alex.barylski »

OK what kind of errors do you want to handle? Most functions in PHP return boolean FALSE to indicate an error, most SQL API (MySQL, MSSQL, etc) have an error reporting function that returns exact details (invalid user/pass, etc), PDO is no different:

http://www.php.net/manual/en/pdo.errorinfo.php

You have two error reporting options with PDO, the library can trigger errors or throw exceptions, the latter of which I prefer.

The last argument of the constructor can be passed an array of options, similar to:

http://www.php.net/manual/en/pdo.setattribute.php

PDO::ERRMODE_EXCEPTION is of interest to me.

So, you can explicitly test for errors, catch exceptions and call the errorInfo methods for more detail.

Cheers,
Alex
evans123
Forum Newbie
Posts: 18
Joined: Wed Nov 17, 2010 4:48 pm

Re: PDO Database Class

Post by evans123 »

Ok thanks. An update statement will return true even though it hasnt made any changes?
Post Reply