A reusable class for MySQL queries in PHP.

db.class.php

<?php

error_reporting(E_ALL);

define('DB_HOSTNAME', 'localhost');
define('DB_USERNAME', '***');
define('DB_PASSWORD', '***');
define('DB_DATABASE', '***');
define('DB_PCONNECT', TRUE);

define('DB_ER_DUP_ENTRY', 1062);

class DbException extends Exception { }
class DbConnectionException extends DbException { }
class DbQueryException extends DbException { }
class DbEmptyQueryException extends DbQueryException { }
class DbDuplicateEntryException extends DbQueryException { }

class UnsupportedOperationException extends Exception { }

class DbQuery implements Iterator, ArrayAccess {

	private static $connection;
	
	private static function connect() {
		if(!self::$connection) {
			self::$connection = DB_PCONNECT ? mysql_pconnect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD) : mysql_connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD);
			if(!self::$connection) {
				throw new DatabaseConnectionException(mysql_error(self::$connection));
			}
			
			if(!mysql_select_db(DB_DATABASE, self::$connection)) {
				throw new DatabaseConnectionException(mysql_error());
			}
		}
	}
	
	private $query;
	
	private $filledQuery;

	private $args;
	
	private $result;

	private $rows;
	
	private function fillQuery($match) {
		if($match[1] != '%%' && !count($this->args)) {
			throw new DbQueryException('too few parameters');
		}
		
		switch($match[1]) {
			case '%d':
				$value = array_shift($this->args);
				if($value > PHP_INT_MAX) {
					$value = sprintf('%.0f', $value);
				} else {
					$value = (int) $value;
				}
				return $value;
			
			case '%s':
				return '\'' . mysql_real_escape_string(array_shift($this->args), self::$connection) . '\'';
			
			case '%f':
				return (float) array_shift($this->args);
			
			case '%l':
				$preescaped = str_replace(array('%', '_'), array('\%', '\_'), array_shift($this->args));
				return '\'' . mysql_real_escape_string($preescaped, self::$connection) . '\'';
			
			case '%%':
				return '%';
			
			default:
				throw new DbQueryException('unknown parameter type');
		}
	}

	public function __construct() {
		self::connect();
		
		$this->args = func_get_args();
		if(!count($this->args)) {
			throw new EmptyQueryException();
		}
		
		$this->query = array_shift($this->args);
		$this->filledQuery = preg_replace_callback('/(%d|%s|%f|%l|%%)/', array($this, 'fillQuery'), $this->query);
		
		if(count($this->args)) {
			throw new DbQueryException('too many parameters');
		}
		
		$this->result = mysql_query($this->filledQuery, self::$connection);
		if(!$this->result) {
			switch(mysql_errno(self::$connection)) {
				case DB_ER_DUP_ENTRY:
					throw new DbDuplicateEntryException(mysql_error(self::$connection));
				default:
					throw new DbQueryException(mysql_error(self::$connection));
			}
		}
		
		if($this->result === TRUE) {
			$this->rows = mysql_affected_rows(self::$connection);
		} else {
			$this->rows = mysql_num_rows($this->result);
		}
	}
	
	private $internalPos;
	
	private $internalValue;
	
	private $mysqlPos;
	
	private function internalSeek($pos) {
		if($this->internalPos != $pos) {
			$this->internalPos = $pos;
			$this->internalValue = null;
		}
	}
	
	private function rowSeek($pos) {
		if($this->internalPos == $pos && $this->internalValue) {
			return $this->internalValue;
		}
		
		if($this->mysqlPos != $pos) {
			if($pos < 0 || $pos >= $this->rows) {
				return;
			}
			
			mysql_data_seek($this->result, $pos);
			$this->mysqlPos = $pos;
		}
		
		$this->internalPos = $this->mysqlPos;
		$this->internalValue = mysql_fetch_array($this->result, MYSQL_BOTH);
		$this->mysqlPos++;
		return $this->internalValue;
	}
	
	private $iteratorPos = 0;
	
	public function current() {
		return $this->rowSeek($this->iteratorPos);
	}
	
	public function key() {
		return $this->iteratorPos;
	}
	
	public function next() {
		$this->internalSeek(++$this->iteratorPos);
	}
	
	public function rewind() {
		$this->internalSeek($this->iteratorPos = 0);
	}
	
	public function valid() {
		return $this->offsetExists($this->iteratorPos);
	}
	
	public function offsetExists($offset) {
		return is_int($offset) && $this->result !== TRUE && $offset >= 0 && $offset < $this->rows;
	}
	
	public function offsetGet($offset) {
		return $this->offsetExists($offset) ? $this->rowSeek($offset) : null;
	}
	
	public function offsetSet($offset, $value) {
		throw new UnsupportedOperationException();
	}
	
	public function offsetUnset($offset) {
		throw new UnsupportedOperationException();
	}
	
	public function getAffectedRows() {
		return $this->rows;
	}
	
	public function isEmpty() {
		return $this->rows != 0;
	}
	
	public function field($row = 0, $col = 0) {
		$res = $this->rowSeek($row);
		return ($res && isset($res[$col])) ? $res[$col] : null;
	}
}

?>

Executing queries

Create a query object. Give an SQL query with placeholders and one argument for each placeholder to the constructor. Those parameters will be inserted safely escaped.

$query = new DbQuery('SELECT a, b, c FROM table WHERE col = %d', 7);
$query = new DbQuery('SELECT a AS col FROM table WHERE a = %d AND b = %s', 7, 'str');
$query = new DbQuery('DELETE FROM table');

Placeholders

%d	Integer values
%s	Strings
%f	Floating point values
%l	Strings. Additionally % and _ are escaped for use with LIKE.
%%	%

Catching errors

Simply try and catch.

try {
    new DbQuery('INSERT INTO table (a, b) VALUES (%s, %s)', 8, 8);
} catch(DbDuplicateEntryException $e) {
    // Duplicate Entry
}

Using the result

$q->getAffectedRows(); // Affected rows by INSERT OR DELETE or selected rows by SELECT

$q[6]['column']; // Access the resultset like an array

foreach(new DbQuery('SELECT a FROM table') as $row) {
    // iterate - $row is an associative array
}

$q->field(); // First row, first column
$q->field(3, 5); // Fourth row (index 3), sixth column (index 5)