I created a PHP class to handle building SQL query strings. I need advice on how to make the code more efficient.
<?php
/**
* Example:
*
* $query = new SQLQuery();
* $query->select('*')->from('users')->where(array('banned' => 0))->order('id' => 'asc')->limit(1);
* $result = $query->execute();
*
**/
requireComponent('Database');
define('SQLQUERY_INSERT', 1);
define('SQLQUERY_UPDATE', 2);
define('SQLQUERY_DELETE', 3);
define('SQLQUERY_SELECT', 4);
define('SQLQUERY_REPLACE', 5);
define('SQLQUERY_CUSTOM', 6);
class SQLQuery extends Component
{
private $type = null; // Query type
private $from = null; // Table name
private $select = array(); // Selected fields
private $where = array(); // Where conditions
private $order = array(); // Order conditions
private $fields = array(); // Insert fields
private $where_condition = null; // Where condition (AND, OR)
private $join_table = null; // Joined table
private $join_on = null; // Join constraint
private $limit = 0; // Row limit
private $custom_sql = null; // Explicit SQL code
private $custom_args = array(); // Explicit SQL arguments
// Initializes an SQLQuery object with an optional preset table name
public function __construct($table_name = null)
{
if ($table_name !== null)
$this->from = $table_name;
}
// Wraps a string in backticks
private function bwrap($s)
{
return surround('`', $s);
}
// Returns the SQL code for a SELECT clause
private function getSelectClause()
{
$append = ($this->fields == '*') ? $this->fields : implode(',' , array_map(array($this, 'bwrap'), is_array($this->fields) ? $this->fields : array($this->fields)));
return 'SELECT ' . $append;
}
// Returns the SQL code for an UPDATE clause
private function getUpdateClause()
{
$sql = 'UPDATE ' . $this->bwrap($this->from) . ' SET ';
$fields = array();
foreach ($this->fields as $key => $value)
{
$value = strcmp_is($value, 'null') ? 'NULL' : '"' . sql($value) . '"';
$fields[] = $this->bwrap($key) . ' = ' . $value;
}
return $sql . implode(',', $fields);
}
// Returns the SQL code for a DELETE clause
private function getDeleteClause()
{
return 'DELETE';
}
// Returns the SQL code for a WHERE clause
private function getWhereClause()
{
if (count($this->where) == 0)
return '';
$operators = array('< ', '> ', '<=', '>=', '!=', '<>');
$fields = array();
foreach ($this->where as $key => $value)
{
// Check first two characters of $value to search for operators
$op = $value[0] . $value[1];
$op_exists = in_array($op, $operators);
$joiner = $op_exists ? $op : (($value == null) ? 'IS' : '=');
$value = empty($value) ? 'NULL' : '"' . sql(trim($op_exists ? str_replace($op, '', $value) : $value)) . '"';
$fields[] = $this->bwrap($key) . ' ' . $joiner . ' ' . $value;
}
return 'WHERE ' . implode(' ' . $this->where_condition . ' ', $fields);
}
// Returns the SQL code for a FROM clause
private function getFromClause()
{
return 'FROM ' . $this->bwrap($this->from);
}
// Returns the SQL code for a JOIN clause
private function getJoinClause()
{
if (!$this->join_table || !$this->join_on)
return '';
return sprintf('INNER JOIN %s ON (%s.%s = %s.%s)', $this->join_table, $this->from, $this->join_on[0],
$this->join_table, $this->join_on[1]);
}
// Returns the SQL code for an ORDER BY clause
private function getOrderByClause()
{
if (count($this->order) == 0)
return '';
$allowed = array('asc', 'desc');
$fields = array();
foreach ($this->order as $key => $value)
{
if (in_array(strtolower($value), $allowed))
$fields[] = $this->bwrap($key) . ' ' . strtoupper($value);
}
return 'ORDER BY ' . implode(',', $fields);
}
// Returns the SQL code for a LIMIT clause
private function getLimitClause()
{
return ($this->limit > 0) ? 'LIMIT ' . $this->limit : '';
}
// Builds a query string for a SELECT operation
private function buildSelectQueryString()
{
return implode(' ', array($this->getSelectClause(), $this->getFromClause(), $this->getWhereClause(),
$this->getJoinClause(), $this->getOrderByClause(), $this->getLimitClause()));
}
// Builds a query string for an INSERT operation
private function buildInsertQueryString()
{
// No easy way to do it in one line
$values = array_values($this->fields);
$count = count($values);
for ($c = 0; $c < $count; $c++)
{
if ($values[$c] == 'NOW()')
$values[$c] = 'NOW()';
else
$values[$c] = '"' . sql($values[$c]) . '"';
}
$sql = 'INSERT INTO ' . $this->bwrap($this->from) . ' ';
$sql .= '(' . implode(',', array_map(array($this, 'bwrap'), array_keys($this->fields))) . ') VALUES ';
$sql .= '(' . implode(',', array_values($values)) . ')';
return $sql;
}
// Builds a query string for a replace operation
private function buildReplaceQueryString()
{
return 'REPLACE' . substr($this->buildInsertQueryString(), 6);
}
// Builds a query string for an UPDATE operation
private function buildUpdateQueryString()
{
return implode(' ', array($this->getUpdateClause(), $this->getWhereClause(), $this->getLimitClause()));
}
// Builds a query string for a DELETE operation
private function buildDeleteQueryString()
{
return implode(' ', array($this->getDeleteClause(), $this->getFromClause(), $this->getWhereClause(),
$this->getLimitClause()));
}
// Returns the finalized query string for the appropriate query type
public function buildQueryString()
{
switch ($this->type)
{
case SQLQUERY_SELECT:
return $this->buildSelectQueryString();
break;
case SQLQUERY_UPDATE:
return $this->buildUpdateQueryString();
break;
case SQLQUERY_INSERT:
return $this->buildInsertQueryString();
break;
case SQLQUERY_REPLACE:
return $this->buildReplaceQueryString();
break;
case SQLQUERY_DELETE:
return $this->buildDeleteQueryString();
break;
case SQLQUERY_CUSTOM:
return $this->custom_sql;
break;
}
trigger_error('Could not call SQLQuery::buildQueryString because of bad query type', E_USER_ERROR);
}
// Executes the SQL query and returns the results
public function execute()
{
// Build query string
$query_string = $this->buildQueryString();
// Log SQL query if needed
if (!Logger::busy() && Config::read('Logger.log_sqlqueries') && componentExists('Logger'))
{
Logger::log(sprintf('SQL query executed: %s', $query_string), LOGGER_CATEGORY_SQLQUERY);
}
// Note: Since prepared statements technically aren't being used in this case,
// the inputs must be escaped beforehand
return ($this->type != SQLQUERY_CUSTOM) ?
call_user_func_array(array('Database', 'query'), array($query_string)) :
call_user_func_array(array('Database', 'query'), array_merge(array($this->custom_sql), $this->custom_args));
}
// Ignores all other parameters and runs explicitly specified SQL code
public function explicit()
{
$args = func_get_args();
$this->type = SQLQUERY_CUSTOM;
$this->custom_sql = array_shift($args);
$this->custom_args = $args;
return $this;
}
// Initializes a SELECT query with selected fields
public function select($fields)
{
$this->type = SQLQUERY_SELECT;
$this->fields = $fields;
return $this;
}
// Initializes an INSERT query with starting fields
public function insert($fields)
{
$this->type = SQLQUERY_INSERT;
$this->fields = $fields;
return $this;
}
// Initializes a REPLACE query with starting fields
public function replace($fields)
{
$this->type = SQLQUERY_REPLACE;
$this->fields = $fields;
return $this;
}
// Initializes an UPDATE query with updated fields
public function update($fields)
{
$this->type = SQLQUERY_UPDATE;
$this->fields = $fields;
return $this;
}
// Initializes a DELETE query
public function delete()
{
$this->type = SQLQUERY_DELETE;
$this->fields = array();
return $this;
}
// Sets the FROM field
public function from($from)
{
$this->from = $from;
return $this;
}
// Sets the WHERE field
public function where($fields)
{
$this->where = $fields;
$this->where_condition = $fields['_condition'];
unset($this->where['_condition']);
return $this;
}
// Sets the WHERE field with AND condition
public function whereAnd($fields)
{
return $this->where(array_merge($fields, array('_condition' => 'AND')));
}
// Sets the WHERE field with OR condition
public function whereOr($fields)
{
return $this->where(array_merge($fields, array('_condition' => 'OR')));
}
// Sets the JOIN field
public function join($table, $on)
{
$this->join_table = $table;
$this->join_on = $on;
return $this;
}
// Sets the ORDER BY field
public function order($by)
{
$this->order = $by;
return $this;
}
// Sets the LIMIT field
public function limit($limit)
{
if ($limit > 0)
$this->limit = $limit;
return $this;
}
}
?>
Also another thing - how can I handle WHERE conditions? Like WHERE conditions should support AND, OR, and nested ANDs and ORs, and also a variety of operators (<, >, >=, <=, !=) and LIKE, NOW(), etc. There's just so many of them though, and I can't think of a way to implement all that without resorting to ugly hacks.