Is using an object to build up SQL Statements overkill?
Is defining a bare String enough for a SQL statement?
This is my PHP and SQL code:
class SQLStatement {
private $table;
private $sql;
const INNER_JOIN = 'INNER JOIN';
const LEFT_JOIN = 'LEFT JOIN';
const RIGHT_JOIN = 'RIGHT JOIN';
const OUTER_JOIN = 'OUTER JOIN';
public function __construct($table) {
$this->setTable($table);
}
public function setTable($table) {
$this->table = $table;
}
public function buildFromString($string) {
$this->sql = $sql;
}
public function select(array $columns) {
$sql = 'SELECT ';
$columns = implode(',', $columns);
$sql .= $columns;
$sql .= " FROM $this->table";
$this->sql = $sql;
return $this;
}
/**
* Setting up INSERT sql statement
*
* @param array $records The records to insert.The array keys must be the columns, and the array values must be the new values
* @return object Return this object back for method chaining
*/
public function insert(array $records) {
$columns = array_keys($records);
$values = array_values($records);
$values = array_map('quote',$values);
$sql = 'INSERT INTO ';
$sql .= $this->table . '('. implode(',', $columns) . ')';
$sql .= ' VALUES ' . '(' . implode(',', $values) . ')';
$this->sql = $sql;
return $this;
}
/**
* Setting up UPDATE sql statement
*
* @param array $records The records to update. The array keys must be the columns, and the array values must be the new records
* @return object Return this object back for method chaining
*/
public function update(array $records, $where) {
$sql = 'UPDATE ' . $this->table . ' SET ';
$data = array();
foreach ($records as $column => $record) {
$data[] = $column . '=' . quote($record);
}
$sql .= implode(', ', $data);
$this->sql = $sql;
return $this;
}
/**
* Setting up DELETE sql statement
* @return object Return this object back for method chaining
*/
public function delete($where=null) {
$sql = 'DELETE FROM ' . $this->table;
$this->sql = $sql;
if (isset($where)) {
$this->where($where);
}
return $this;
}
/**
* Setting up WHERE clause with equality condition. (Currently only support AND logic)
* @param array $equalityExpression Conditional equality expression. The key is the column, while the value is the conditional values
* @return object Return this object back for method chaining
*
*/
public function where(array $equalityExpression) {
if (!isset($this->sql)) {
throw new BadMethodCallException('You must use SELECT, INSERT, UPDATE, or DELETE first before where clause');
}
$where = ' WHERE ';
$conditions = array();
foreach ($equalityExpression as $column => $value) {
if (is_array($value)) {
$value = array_map('quote', $value);
$conditions[] = "$column IN (" . implode(',', $value) . ')';
}
else {
$value = quote($value);
$conditions[] = "$column = $value";
}
}
$where .= implode(' AND ', $conditions);
$this->sql .= $where;
return $this;
}
/**
* Setting up WHERE clause with expression (Currently only support AND logic)
* @param array $expression Conditional expression. The key is the operator, the value is array with key being the column and the value being the conditional value
* @return object Return this object back for method chaining
*/
public function advancedWhere(array $expression) {
if (!isset($this->sql)) {
throw new BadMethodCallException('You must use SELECT, INSERT, UPDATE, or DELETE first before where clause');
}
if (!is_array(reset($expression))) {
throw new InvalidArgumentException('Invalid format of expression');
}
$where = ' WHERE ';
$conditions = array();
foreach ($expression as $operator => $record) {
foreach ($record as $column => $value) {
$conditions[] = $column . ' ' . $operator . ' ' . quote($value);
}
}
$where .= implode(' AND ', $conditions);
$this->sql .= $where;
return $this;
}
/**
* Setting up join clause (INNER JOIN, LEFT JOIN, RIGHT JOIN, or OUTER JOIN)
* @param array $tables <p>Tables to join as the key and the conditions array as the value (Currently only support ON logic)</p>
* <p>Array example : array('post'=>array('postid'=>'post.id'))</p>
* @param string $mode The mode of join. It can be INNER JOIN, LEFT JOIN, RIGHT JOIN, or OUTER JOIN
* @return object Return this object back for method chaining
*/
public function join(array $joins, $mode = self::INNER_JOIN) {
if (!isset($this->sql) && strpos($this->sql, 'SELECT')) {
throw new BadMethodCallException('You must have SELECT clause before joining another table');
}
if (!is_array(reset($joins))) {
throw new InvalidArgumentException('Invalid format of the join array.');
}
$Conditions = array();
foreach ($joins as $table => $conditions) {
$join = ' ' . $mode . ' ' . $table . ' ON ';
foreach ($conditions as $tbl1 => $tbl2) {
$Conditions[] = $tbl1 . ' = ' . $tbl2;
}
$join .= implode(' AND ', $Conditions);
}
$this->sql .= $join;
return $this;
}
/**
* Setting up GROUP BY clause
* @param array $columns The columns you want to group by
* @param string $sort The type of sort, ascending is the default
* @return object Return this object back for method chaining
*/
public function groupBy(array $columns, $sort = 'ASC') {
if (!isset($this->sql)) {
throw new BadMethodCallException('You must use SELECT, INSERT, UPDATE, or DELETE first before group by clause');
}
$groupBy = ' GROUP BY ' . implode(',', $columns) . ' ' . $sort;
$this->sql .= $groupBy;
return $this;
}
/**
* Setting up HAVING clause with expression
* @param expression $expression Conditional expression. The key is the operator, the value is an array with key being the column and the value being the conditional value
* @return object Return this object back for method chaining
*/
public function having($expression) {
if (!isset($this->sql) && strpos($this->sql, 'GROUP BY') === FALSE) {
throw new BadMethodCallException('You must have SELECT, INSERT, UPDATE, or DELETE and have GROUP BY clause first before where clause');
}
if (!is_array(reset($expression))) {
throw new InvalidArgumentException('Invalid format of expression');
}
$having = ' HAVING ';
$conditions = array();
foreach ($expression as $operator => $record) {
foreach ($record as $column => $value) {
$conditions[] = $column . ' ' . $operator . ' ' . $value;
}
}
$having .= implode(' AND ', $conditions);
$this->sql .= $having;
return $this;
}
/**
* Return the SQL statement if this object is supposed to be string
* @return string The sql statement
*/
public function __toString() {
return $this->sql;
}
}
The disadvantage I found is when I need to use prepared statement since prepared statements contains placeholders.
Should I add a feature to my SQL Statement Object to do prepared statements? When should using prepared statements good practice?
