Skip to content

Instantly share code, notes, and snippets.

@niklasf
Created Feb 22, 2011
Embed
What would you like to do?
A reusable class for MySQL queries in 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;
}
}
?>
@niklasf
Copy link
Author

niklasf commented Feb 22, 2011

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

%dInteger values
%sStrings
%fFloating point values
%lStrings. 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)

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment