Hello all,
I was wondering if I could get some feedback on my query wrapper class. Basically the class takes input in the form of strings or arrays, stores them inside itself, and then takes all of those arrays and forms an sql query + values to be bound, and then executes the query with PDO. I realize there are some unorthodox uses of magic functions, I did my best to document the functions, but if anyone is confused I would be happy to explain them further. The class is set to allow method chaining, so calls will appear pretty similar to the query it defines. I didn't allow for any default values or "query context", because the class is meant to be used by ActiveRecord classes or Table/Row Data Gateways, which define the query context and any default values.
Eventually, I'd like to add capability for joins, and extra items such as order by, limit, sort, etc.
Any and all feedback would be welcome. Even if its as simple as telling me its a useless class that is far outdone by existing ORMs.
Here is the class:
<?php
class iceQuery {
/* Database connection */
protected $db;
/* Type of query, ie. select */
protected $type;
/* Arrays of data */
protected $sql = array();
/* Sql query that will be eventually prepared and executed */
protected $query = array();
/* Array of values to be bound to the query */
protected $values = array();
/* Array of acceptable methods to call for a certain query typ */
protected $methods = array();
/* Default data for queries */
protected $defaults = array (
'select' => array (
'query' => 'SELECT :select FROM :from',
'methods' => array ('select', 'from', 'where')
),
'insert' => array (
'query' => 'INSERT INTO :into SET :set',
'methods' => array ('into', 'set'),
),
'update' => array (
'query' => 'UPDATE :tables SET :set WHERE :where',
'methods' => array ('update', 'set', 'where'),
),
'delete' => array (
'query' => 'DELETE FROM :tables WHERE :where',
'methods' => array ('from', 'where'),
)
);
/* Constructor... sets the database connection */
public function __construct ($db) {
$this->db = $db;
}
/* Magic function that sets the type when public properties select, update, insert, or delete are called/'requested' */
public function __get ($type) {
if (!in_array ($type, array_keys ($this->defaults))):
xprint (array_keys ($this->defaults));
throw new Exception ('Invalid query type: ' . $type);
else:
$this->type = $type;
$this->query = $this->defaults[$type]['query'];
$this->methods = $this->defaults[$type]['methods'];
return $this;
endif;
}
/* Magic function that makes sure only methods belonging to the query type are callable */
public function __call ($method, $args) {
if (!$this->type):
throw new Exception ('Please specify query type first');
endif;
if (!in_array ($method, $this->methods)):
xprint ($this->methods);
throw new Exception ('Invalid method: ' . $method);
endif;
foreach ($args as $arg):
$this->{'_' . $method} ($arg);
endforeach;
return $this;
}
/* Resets all data for use with another query */
public function reset() {
$this->type = false;
$this->sql = array();
$this->query = array();
$this->values = array();
$this->methods = array();
}
/* Hack allowing query type to be set and select columns added at the same time */
public function select ($arg) {
if (!$this->type): $this->select; endif;
$this->_select ($arg);
return $this;
}
/* Hack allowing query type to be set and update tables added at the same time */
public function update ($arg) {
if (!$this->type): $this->update; endif;
$this->_update ($arg);
return $this;
}
/* Magic function that adds data to the sql array */
protected function __set ($method, $value) {
if (!is_array ($this->sql[$method])):
$this->sql[$method] = array();
endif;
if (!is_array ($value)):
$this->sql[$method][] = $value;
else:
foreach ($value as $key => $item):
if (is_int ($key)):
$this->sql[$method][] = $item;
else:
$this->sql[$method][$key] = $item;
endif;
endforeach;
endif;
}
/* Add select columns */
protected function _select ($data) {
$this->select = $data;
}
/* Add update tables */
protected function _update ($data) {
$this->tables = $data;
}
/* Add from tables */
protected function _from ($data) {
$this->tables = $data;
}
/* Add where conditions */
protected function _where ($data) {
$this->conditions = $data;
}
/* Add (insert) into tables */
protected function _into ($data) {
$this->tables = $data;
}
/* Add set data */
protected function _set ($data) {
$this->set = $data;
}
/* Joins arrays into an sql statement */
protected function joinArray ($glue, $array, $bind = false) {
foreach ($array as $key => $value):
$keys[] = (is_string ($key)?$key . ($bind?' = :' . $key:' AS ' . $value):$value);
if ($bind && is_string ($key)): $this->values[':' . $key] = $value; endif;
endforeach;
return join ($glue, $keys);
}
/* Take select data and transform into sql query */
protected function prepare_select() {
if (!empty ($this->sql['select'])):
$this->query = str_replace (':select', $this->joinArray (', ', $this->sql['select']), $this->query);
else:
throw new Exception ('Please specify select columns');
endif;
if (!empty ($this->sql['tables'])):
$this->query = str_replace (':from', $this->joinArray (', ', $this->sql['tables']), $this->query);
else:
throw new Exception ('Please specify tables');
endif;
if (!empty ($this->sql['conditions'])):
$this->query .= ' WHERE ' . $this->joinArray (' AND ', $this->sql['conditions'], true);
endif;
}
/* Take insert data and transform into sql query */
protected function prepare_insert() {
if (!empty ($this->sql['tables'])):
$this->query = str_replace (':into', $this->joinArray (', ', $this->sql['tables']), $this->query);
else:
throw new Exception ('Please specify tables');
endif;
if (!empty ($this->sql['set'])):
$this->query = str_replace (':set', $this->joinArray (' AND ', $this->sql['set'], true), $this->query);
else:
throw new Exception ('Please specify set fields');
endif;
}
/* Take update data and transform into sql query */
protected function prepare_update() {
if (!empty ($this->sql['tables'])):
$this->query = str_replace (':tables', $this->joinArray (', ', $this->sql['tables']), $this->query);
else:
throw new Exception ('Please specify tables');
endif;
if (!empty ($this->sql['set'])):
$this->query = str_replace (':set', $this->joinArray (' AND ', $this->sql['set'], true), $this->query);
else:
throw new Exception ('Please specify set fields');
endif;
if (!empty ($this->sql['conditions'])):
$this->query = str_replace (':where', $this->joinArray (' AND ', $this->sql['conditions'], true), $this->query);
else:
throw new Exception ('Please specify conditions');
endif;
}
/* Take delete data and transform into sql query */
protected function prepare_delete() {
if (!empty ($this->sql['tables'])):
$this->query = str_replace (':tables', $this->joinArray (', ', $this->sql['tables']), $this->query);
else:
throw new Exception ('Please specify tables');
endif;
if (!empty ($this->sql['conditions'])):
$this->query = str_replace (':where', $this->joinArray (' AND ', $this->sql['conditions'], true), $this->query);
else:
throw new Exception ('Please specify conditions');
endif;
}
/* Fetches single row */
public function fetch() {
$this->{'prepare_' . $this->type}();
return $this->stmt()->fetch (PDO::FETCH_ASSOC);
}
/* Fetches all rows */
public function fetchAll() {
$this->{'prepare_' . $this->type}();
return $this->stmt()->fetchAll (PDO::FETCH_ASSOC);
}
/* Executes a statement */
public function execute() {
$this->{'prepare_' . $this->type}();
return $this->stmt();
}
/* Ran out of good names... */
protected function stmt() {
$stmt = $this->db->prepare ($this->query);
foreach ($this->values as $key => $value):
$stmt->bindValue ($key, $value);
endforeach;
$stmt->execute();
return $stmt;
}
}
?>
And here is how it is used:
$query = new iceQuery ($db);
$row = $query->select ('*')->from ('pages')->where (array ('page_id' => '1'))->fetch();
$query->reset();
$rowset = $query->select ('*')->from ('pages')->where (array ('page_id' => '1'))->fetchAll();
$query->reset();
$query->insert->into ('pages')->set (array ('title' => 'Page Title'))->execute();
$query->reset();
$query->update('pages')->set (array ('title' => 'New Page Title'))->where (array ('page_id' => '1'))->execute();
$query->reset();
$query->delete->from ('pages')->where (array ('page_id' => '1'))->execute();
$query->reset();
