A database wrapper that uses PDO.

db.class.php

<?php

/**
 * db.class.php
 *
 * My first attempt at a Database wrapper class in PHP using PDO.
 * 
 * @author 	John McCarthy
 * @version	1.0
 */

/*
 * First we have to check if PDO exists. It should do if you are using PHP 5.1.0 or later
 */
try
{
    if (!class_exists('PDO'))
    {
        throw new Exception('PDO not enabled.');
    }
}
catch (Exception $e)
{
    die($e->getMessage());// Deal with exception
}

if (!class_exists('db'))
{
	class db
	{
		/**
		 * This variable represents the PDO object attained in __construct()
		 * It is used for queries throughout the class
		 * @var PDO 
		 */
		private $pdoHandle;

		/**
		 * Defines which method is used to run queries.
		 * Either PDO::Query() or PDO::Prepare()
		 * @var int 
		 */
		private $queryMethod;
		
		/**
		 * A count of the number of queries run by the instance of the db
		 * Can be retrieved through external functions by db::getAttribute('queriesMade')
		 * @var int 
		 */
		private $queriesMade;
		
		const METHOD_QUERY = 1;
		const METHOD_PREPARE = 2;
		
		/**
		 * Creates a new database connection and stores the PDO object in $pdoHandle
		 * @example $db = new db('mysql:host=localhost;dbname=testdb', 'root', 'root');
		 *  
		 * @param string $dsn
		 * @param string $username
		 * @param string $password
		 * @param int $method default = db::METHOD_PREPARE
		 * @param array $options default = array()
		 * @throws dbException 
		 */
		public function __construct($dsn, $username, $password, $method = self::METHOD_PREPARE, $options = array()) 
		{
			try
			{
				$this->pdoHandle = new PDO($dsn, $username, $password, $options);
			}
			catch (PDOException $e)
			{
				if (is_object($this->pdoHandle))
				{
					$errorInfo = $this->pdoHandle->errorInfo();
				}
				else
				{
					$errorInfo = null;
				}
				throw new Exception('Unable to connect to database');
			}
			// Turn off PDO Exceptions, as we are handling exceptions with dbException
			$this->pdoHandle->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
			$this->queryMethod = $method;
		}
		
		/**
		 * Used to get the values of class properties.
		 * Currently the only properties available are $queryMethod and $queriesMade but it is left open to be expanded later.
		 * @param string $attr
		 * @return mixed 
		 */
		public function getAttribute($attr)
		{
			if (isset($this->$attr) && $attr != 'pdoHandle')
			{
				return $this->$attr;
			}
			else
			{
				return false;
			}
		}
		
		/**
		 * Allows the value of class attributes to be changed. Currently the only attribute that can be changed is $queryMethod
		 * but it's left open for more properties in the future.
		 * @param string $attr
		 * @param mixed $value
		 * @return \db|boolean
		 * @throws dbException 
		 */
		public function setAttribute($attr, $value)
		{
			switch ($attr)
			{
				case 'queryMethod':
					if ($value == self::METHOD_PREPARE || $value == self::METHOD_QUERY)
					{
						$this->queryMethod = $value;
					}
					else
					{
						throw new Exception('The query method set in <code>' . __METHOD__ . '</code> was of an unknown type.');
					}
					return $this;
					break;
					
				case 'queriesMade':
				default:
					return false;
					break;
			}
		}
		
		/**
		 * Calls either db::runQueryPrepare or db::runQueryQuery depending on $queryMethod
		 * and returns the current db object on success.
		 * @param string $query
		 * @param array $values default = array()
		 * @param int $queryMethod default = null
		 * @return mixed
		 * @throws dbException 
		 */
		public function runQuery($query, $values = array(), $queryMethod = null)
		{
			$method = $queryMethod ? $queryMethod : $this->getAttribute('queryMethod');
			switch ($method)
			{
				case self::METHOD_PREPARE:
					$result = $this->runQueryPrepare($query, $values);
					$this->queriesMade++;
					return $result;
					break;
				
				case self::METHOD_QUERY:
					$result = $this->runQueryQuery($query);
					$this->queriesMade++;
					return $result;
					break;
				
				default:
					throw new Exception('Unknown query method supplied to <code>' . __METHOD__ . '</code>.');
					break;
			}
		}
		
		/**
		 * Runs a query that is not expected to return any results using PDO::prepare. 
		 * @param string $query
		 * @param array $values
		 * @return \db
		 * @throws dbException 
		 */
		private function runQueryPrepare($query, $values)
		{
			if (!$stmt = $this->pdoHandle->prepare($query))
			{
				throw new Exception('Unable to prepare statement in <code>' . __METHOD__ . '</code>');
			}
			
			foreach ($values as $i => $val)
			{
				if (!$stmt->bindParam($i, $val))
				{
					throw new Exception('Parameter bind failed when attempting to bind variable in <code>');
				}
			}
			
			if (!$stmt->execute())
			{
				throw new Exception('The Query run in <code>' . __METHOD__ . '</code> failed.');
			}
			
			return $this;
		}
		
		/**
		 * Runs a query that is not expected to return any results using PDO::query.
		 * @param string $query
		 * @return \db
		 * @throws dbException 
		 */
		private function runQueryQuery($query)
		{
			if (!$stmt = $this->pdoHandle->query($query))
			{
				throw new Exception('The query run by <code>' . __METHOD__ . '</code> failed to be run.');
			}
			
			return $this;
		}
		
		/**
		 * Calls either db::getResultPrepare or db::getResultQuery depending on $queryMethod
		 * and returns the result on success.
		 * @param string $query
		 * @param array $values default = array()
		 * @param int $queryMethod default = null
		 * @return mixed
		 * @throws dbException 
		 */
		public function getResult($query, $values = array(), $queryMethod = null)
		{
			$method = $queryMethod ? $queryMethod : $this->getAttribute('queryMethod');
			switch ($method)
			{
				case self::METHOD_PREPARE:
					$result = $this->getResultPrepare($query, $values);
					$this->queriesMade++;
					return $result;
					break;
				
				case self::METHOD_QUERY:
					$result = $this->getResultQuery($query);
					$this->queriesMade++;
					return $result;
					break;
				
				default:
					throw new Exception('Unknown query method supplied to <code>' . __METHOD__ . '</code>.');
					break;
			}
		}

		/**
		 * Gets the single scalar value result of a query using the PDO::prepare method. 
		 * @param string $query
		 * @param array $values
		 * @return mixed
		 * @throws dbException 
		 */
		private function getResultPrepare($query, $values)
		{
			if (!$stmt = $this->pdoHandle->prepare($query))
			{
				throw new Exception('Unable to prepare statement in <code>' . __METHOD__ . '</code>');
			}
			
			if (!$stmt->execute($values))
			{
				throw new Exception('The Query run in <code>' . __METHOD__ . '</code> failed.');
			}
			
			if ($stmt->rowCount() > 1 || $stmt->columnCount() > 1)
			{
				throw new Exception('<code>' . __METHOD__ . '</code> will only return single values. Please use <code>db::getAssoc</code> for mulitple values.');
			}
			
			$result = $stmt->fetch(PDO::FETCH_LAZY);
			return $result[0];
		}
		
		/**
		 * Gets the single scalar value result of a query using the PDO::query method. 
		 * @param string $query
		 * @return mixed
		 * @throws dbException 
		 */
		private function getResultQuery($query)
		{
			if (!$stmt = $this->pdoHandle->query($query))
			{
				throw new Exception('The query run by <code>' . __METHOD__ . '</code> failed to be run.');
			}
			
			if ($stmt->rowCount() > 1 || $stmt->columnCount() > 1)
			{
				throw new Exception('<code>' . __METHOD__ . '</code> will only return single values. Please use <code>db::getAssoc</code> for mulitple values.');
			}
			
			$result = $stmt->fetch(PDO::FETCH_LAZY);
			return $result[0];
		}
		
		/**
		 * Calls either db::getAssocPrepare or db::getAssocQuery depending on $queryMethod
		 * and returns the current db object on success.
		 * @param string $query
		 * @param array $values default = array()
		 * @param int $queryMethod default = null
		 * @return mixed
		 * @throws dbException 
		 */
		public function getAssoc($query, $values = array(), $queryMethod = null)
		{
			$method = $queryMethod ? $queryMethod : $this->getAttribute('queryMethod');
			switch ($method)
			{
				case self::METHOD_PREPARE:
					$result = $this->getAssocPrepare($query, $values);
					$this->queriesMade++;
					return $result;
					break;
				
				case self::METHOD_QUERY:
					$result = $this->getAssocQuery($query);
					$this->queriesMade++;
					return $result;
					break;
				
				default:
					throw new Exception('Unknown query method supplied to <code>' . __METHOD__ . '</code>.');
					break;
			}
		}
		
		/**
		 * Gets the single row array result of a query using the PDO::prepare method. 
		 * @param string $query
		 * @param array $values
		 * @return mixed
		 * @throws dbException 
		 */
		private function getAssocPrepare($query, $values)
		{
			if (!$stmt = $this->pdoHandle->prepare($query))
			{
				throw new Exception('Unable to prepare statement in <code>' . __METHOD__ . '</code>');
			}
			
			if (!$stmt->execute($values))
			{
				throw new Exception('The Query run in <code>' . __METHOD__ . '</code> failed.');
			}
			
			if ($stmt->rowCount() > 1)
			{
				throw new Exception('<code>' . __METHOD__ . '</code> will only return single rows of values. Please use <code>db::getArraySet</code> for mulitple rows of values.');
			}
			
			$result = $stmt->fetch(PDO::FETCH_ASSOC);
			return $result;
		}
		
		/**
		 * Gets the single row array result of a query using the PDO::query method. 
		 * @param string $query
		 * @return mixed
		 * @throws dbException 
		 */
		private function getAssocQuery($query)
		{
			if (!$stmt = $this->pdoHandle->query($query))
			{
				throw new Exception('The query run by <code>' . __METHOD__ . '</code> failed to be run.');
			}
			
			if ($stmt->rowCount() > 1)
			{
				throw new Exception('<code>' . __METHOD__ . '</code> will only return single rows of values. Please use <code>db::getArraySet</code> for mulitple rows of values.');
			}
			
			$result = $stmt->fetch(PDO::FETCH_ASSOC);
			return $result;
		}
		
		/**
		 * Calls either db::getArraySetPrepare or db::getArraySetQuery depending on $queryMethod
		 * and returns the current db object on success.
		 * @param string $query
		 * @param array $values default = array()
		 * @param int $queryMethod default = null
		 * @return mixed
		 * @throws dbException 
		 */
		public function getArraySet($query, $values = array(), $queryMethod = null)
		{
			$method = $queryMethod ? $queryMethod : $this->getAttribute('queryMethod');
			switch ($method)
			{
				case self::METHOD_PREPARE:
					$result = $this->getArraySetPrepare($query, $values);
					$this->queriesMade++;
					return $result;
					break;
				
				case self::METHOD_QUERY:
					$result = $this->getArraySetQuery($query);
					$this->queriesMade++;
					return $result;
					break;
				
				default:
					throw new Exception('Unknown query method supplied to <code>' . __METHOD__ . '</code>.');
					break;
			}
		}
		
		/**
		 * Gets the multi row array result of a query using the PDO::prepare method as a multidimensional array. 
		 * @param string $query
		 * @param array $values
		 * @return mixed
		 * @throws dbException 
		 */
		private function getArraySetPrepare($query, $values)
		{
			if (!$stmt = $this->pdoHandle->prepare($query))
			{
				throw new Exception('Unable to prepare statement in <code>' . __METHOD__ . '</code>');
			}
			
			if (!$stmt->execute($values))
			{
				throw new Exception('The Query run in <code>' . __METHOD__ . '</code> failed.');
			}
			
			$stmt->setFetchMode(PDO::FETCH_ASSOC);
			return $stmt->fetchAll();
		}
		
		/**
		 * Gets the multi row array result of a query using the PDO::query method as a multidimensional array. 
		 * @param string $query
		 * @return mixed
		 * @throws dbException 
		 */
		private function getArraySetQuery($query)
		{
			if (!$stmt = $this->pdoHandle->query($query))
			{
				throw new Exception('The query run by <code>' . __METHOD__ . '</code> failed to be run.');
			}
			
			$stmt->setFetchMode(PDO::FETCH_ASSOC);
			return $stmt->fetchAll();
		}
		
		/**
		 * Calls either db::countRowsPrepare or db::countRowsQuery depending on $queryMethod
		 * and returns the number of rows affected by the query.
		 * @param string $query
		 * @param array $values default = array()
		 * @param int $queryMethod default = null
		 * @return int
		 * @throws dbException 
		 */
		public function countRows($query, $values = array(), $queryMethod = null)
		{
			$method = $queryMethod ? $queryMethod : $this->getAttribute('queryMethod');
			switch ($method)
			{
				case self::METHOD_PREPARE:
					$result = $this->countRowsPrepare($query, $values);
					$this->queriesMade++;
					return $result;
					break;
				
				case self::METHOD_QUERY:
					$result = $this->countRowsQuery($query);
					$this->queriesMade++;
					return $result;
					break;
				
				default:
					throw new Exception('Unknown query method supplied to <code>' . __METHOD__ . '</code>.');
					break;
			}
		}
		
		/**
		 * Gets the number of rows affected by a query using the PDO::prepare method. 
		 * @param string $query
		 * @param array $values
		 * @return int
		 * @throws dbException 
		 */
		private function countRowsPrepare($query, $values)
		{
			if (!$stmt = $this->pdoHandle->prepare($query))
			{
				throw new Exception('Unable to prepare statement in <code>' . __METHOD__ . '</code>');
			}
			
			if (!$stmt->execute($values))
			{
				throw new Exception('The Query run in <code>' . __METHOD__ . '</code> failed.');
			}
			
			return $stmt->rowCount();
		}
		
		/**
		 * Gets the number of rows affected by a query using the PDO::query method. 
		 * @param string $query
		 * @return int
		 * @throws dbException 
		 */
		private function countRowsQuery($query)
		{
			if (!$stmt = $this->pdoHandle->query($query))
			{
				throw new Exception('The query run by <code>' . __METHOD__ . '</code> failed to be run.');
			}
			
			return $stmt->rowCount();
		}
		
		/**
		 * Returns the last inserted ID. Note this may not work with some database drivers as the function 
		 * does not exist in them.
		 * @return int 
		 */
		public function lastInsertedId()
		{
			return $this->pdoHandle->lastInsertId();
		}
	}
}

?>