Page 1 of 1

PDO Database Class

Posted: Wed Nov 24, 2010 12:07 pm
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?

Re: PDO Database Class

Posted: Wed Nov 24, 2010 12:28 pm
by alex.barylski
Well what kind of enhanced functionality are you expecting a PDO class, default PDO is already an object.

Cheers,
Alex

Re: PDO Database Class

Posted: Wed Nov 24, 2010 12:56 pm
by evans123
Im not sure on how to handle the errors and report back to the script which requested the sql task.

Re: PDO Database Class

Posted: Wed Nov 24, 2010 9:48 pm
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

Re: PDO Database Class

Posted: Thu Nov 25, 2010 12:46 am
by evans123
Ok thanks. An update statement will return true even though it hasnt made any changes?