<?php

class SQLGenerator
{
public static $queries = array();
protected $_parts = array();
protected $_use    = "";
protected $_select = "SELECT {{FIELDS}}{{FROM}}{{JOIN}}{{ON}}{{WHERE}}{{GROUP}}{{ORDER}}{{LIMIT}}";
protected $_insert = "INSERT INTO {{TABLE}}{{COLS}} VALUES{{VALUES}}";
protected $_delete = "DELETE{{FROM}}{{WHERE}}{{ORDER}}{{LIMIT}}";
protected $_update = "UPDATE {{TABLE}} SET {{SET}}{{ORDER}}{{LIMIT}}";
protected function __construct($use)
{
$use = "_" . $use;
$this->_use = $this->$use;
self::$queries[] = $this;
}
protected function _is_assoc($array) {
foreach (array_keys($array) as $k => $v) {
if ($k !== $v)
return true;
}
return false;
}
protected function _nameList($arg)
{
if(is_array($arg))
{
$fin = array();
foreach($arg as $al=>$te)
{
if(!is_numeric($al))
$fin[] = $te . " AS `" . $al . "`";
else
$fin[] = $te;
}
$arg = join(", ", $fin);
}
return $arg;
}
public static function select()
{
$query = new SQLGenerator('select');
if(func_num_args())
{
$args = func_get_args();
call_user_func_array(array($query, 'fields'), $args);
}
self::$queries[] = $query;
return $query;
}
public static function insert()
{
$query = new SQLGenerator('insert');
if(func_num_args())
$query->table(func_get_arg(0));
self::$queries[] = $query;
return $query;
}
public static function update()
{
$query = new SQLGenerator('update');
if(func_num_args())
$query->table(func_get_arg(0));
self::$queries[] = $query;
return $query;
}
public static function delete()
{
$query = new SQLGenerator('delete');
if(func_num_args())
$query->from(func_get_arg(0));
self::$queries[] = $query;
return $query;
}
public function fields()
{
$fs = $this->_nameList((func_num_args() == 1)? func_get_arg(0) : func_get_args());
$this->fields = (empty($this->fields))? $fs : $this->fields . ", " . $fs;
return $this;
}
public function from()
{
$ft = $this->_nameList((func_num_args() == 1)? func_get_arg(0) : func_get_args());
$this->from = (empty($this->from))? (" FROM " . $ft) : ($this->from . ", " . $fs);
return $this;
}
public function table($n)
{
$this->table = $n;
return $this;
}
public function join()
{
$js = (func_num_args() == 1)? func_get_arg(0) : func_get_args();
$v = array_values($js);
if(!is_array($v[0]))
return $this->join(array($js));
foreach($js as $j)
{
$type = isset($j["type"])? ($j["type"] . " ") : isset($j[0])? ($j[0] . " ") : "";
$type = strtoupper($type);
$tables = $this->_nameList(isset($j["tables"])? $j["tables"] : isset($j["table"])? $j["table"] : $j[1]);
$on = isset($j["on"])? $j["on"] : (isset($j[2])? $j[2] : false);
$on = ($on)? (" ON" . $this->_conditions($on)) : ("");
$join = ($type."JOIN (".$tables.")".$on);
$this->join = $this->join . " " . $join;
}
return $this;
}
public function on()
{
$cons = $this->_conditions((func_num_args() == 1)? func_get_arg(0) : func_get_args());
$this->on = (empty($this->on))? (" ON(" . $cons . ")") : (substr($this->on, 0, -1) . ' AND ' . $cons . ')');
return $this;
}
public function _condition($field, $check)
{
if(!preg_match("/^.{0,2}('|\").+?('|\")$/", $check))
{
$check = "'" . addslashes($check) . "'";
}
$ops = array(
'=' => '=',
'>' => '>',
'>=' => '>=',
'<' => '<',
'<=' => '<=',
'!' => '!=',
'L' => ' LIKE '
);
preg_match("/^([><=!L]{0,2})?(('|\").+)$/", $check, $matches);
$op = $ops[$matches[1]];
$op = $op? $op : '=';
$val = $matches[2];

return $field.$op.$val;
}
public function _conditions()
{
$cons = (func_num_args() == 1)? func_get_arg(0) : func_get_args();
if(is_array($cons))
{
$cons = '(' . join(' AND ', $this->_conArray($cons)) . ')';
}
return $cons;
}
protected function _conArray($cons)
{
$c2 = array();
$temp;
foreach($cons as $key=>$val)
{
if(strtoupper($key) == 'AND' || strtoupper($key) == 'OR')
$c2[] = '(' . join(' ' . strtoupper($key) . ' ', $this->_conArray($val)) . ')';
elseif(is_numeric($key))
$c2[] = $this->_conditions($val);
elseif(is_string($key) && is_array($val))
{
$temp = array();
foreach($val as $av)
{
$temp[] = $this->_condition($key, $av);
}
$c2[] = '(' . join(' OR ', $temp) . ')';
}
elseif(is_string($key) && (is_string($val) || is_numeric($val)))
$c2[] = $this->_condition($key, $val);
}
return $c2;
}
public function where()
{
$cons = $this->_conditions((func_num_args() == 1)? func_get_arg(0) : func_get_args());
$this->where = empty($this->where)? (" WHERE " . $cons) : ($this->where . ' AND ' . $cons);
return $this;
}
public function group()
{
$by = (func_num_args() == 1)? func_get_arg(0) : func_get_args();
if(is_array($by))
$by = join(', ', $by);
$this->group = empty($this->group)? (" GROUP BY " . $by) : ($this->group . ", " . $by);
return $this;
}
public function order()
{
$by = (func_num_args() == 1)? func_get_arg(0) : func_get_args();
if(is_array($by))
{
$temp = array();
foreach($by as $k=>$v)
{
if(!is_numeric($k))
$temp[] = $k . " " . $v;
else
$temp[] = $v;
}
$by = join(", ", $temp);
}
$this->order = empty($this->order)? (" ORDER BY " . $by) : ($this->order . ", " . $by);
return $this;
}
public function limit($rows, $offset = 0)
{
$this->limit = " LIMIT BY " . ($offset? $offset . ", ":"") . $rows;
return $this;
}
public function cols()
{
if(!empty($this->cols))
return $this;
$cols = (func_num_args() == 1)? func_get_arg(0) : func_get_args();
if(is_array($cols))
$cols = join(", ", $cols);
$this->cols = " (" . $cols . ")";
return $this;
}
public function values()
{
$valset = (func_num_args() == 1)? func_get_arg(0) : func_get_args();
$temp;
if(is_array($valset))
{
if($this->_is_assoc($valset))
{
$this->cols(array_keys($valset));
$this->values(array_values($valset));
return $this;
}
else
{
$temp = array();
foreach($valset as $val)
{
if(is_array($val))
$this->values($val);
else
{
$temp[] = $this->_value($val);
}
}
$valset = join(", ", $temp);
}
}
$valset = "(" . $valset . ")";
$this->values = empty($this->values)? $valset : $this->values . ", " . $valset;
return $this;
}
public function _value($val)
{
if(is_string($val))
{
if(get_magic_quotes_gpc())
$val = stripslashes($val);
$val = addslashes($val);
return "'" . $val . "'";
}
if(is_numeric($val))
return $val;
if($val === false)
return 'FALSE';
if($val === true)
return 'TRUE';
if($val === null)
return 'NULL';
return $val;
}
public function set($cols)
{
$c2 = array();
foreach($cols as $col=>$val)
{
$c2[] = $col."=".$this->_value($val);
}
$cols = join(", ", $c2);
$this->set = empty($this->set)? $cols : $this->set . ", " . $cols;
return $this;
}
public function clear($field)
{
if($field)
{
unset($this->_parts[$field]);
}
else
{
$this->_parts = array();
}
}
public function __toString()
{
$query = $this->_use;
foreach($this->_parts as $key=>$val)
{
$query = str_replace('{{' . strtoupper($key) . '}}', $val, $query);
}
$query = preg_replace("/(\{\{[A-Z]+\}\})/", "", $query);
return $query;
}
protected function __get($p)
{
if(!isset($this->_parts[$p]))
$this->_parts[$p] = "";
return $this->_parts[$p];
}
protected function __set($p, $v)
{
$this->_parts[$p] = $v;
}
protected function __isset($p)
{
return isset($this->_parts[$p]);
}
}

----------------------------
Some comments. It is simple enough to use, and it's not made to replace simple queries. However, when you have complex, conditional queries I found that this is highly useful. If I'm remembering correctly I did fix some glitches, but I don't have the corrected version with me. If you find some, let me know, and I'll try to get the corrected version up as soon as I can.

Also, soon enough I intend to create a DB Table abstraction, which I will tie this into.
----------------------------

Example usages:

Select:
$query = SQLGenerator::select()->fields('*')->from('users');
$query->where(array('name'=>'jack','visits'=>">'200'"));
$res = mysql_query($query)

Insert:
$query = SQLGenerator::insert()->table('users');
$query->values(array(null, 'mike', 'pass'));
$query->values(array(null, 'john', 'pass'));
$query->values(array(null, 'jane', 'pass'));
mysql_query($query);

Update:
$query = SQLGenerator::update()->table('users');
$query->set(array('password'=>'newpass'))->limit(5);
mysql_query($query);

Delete:
$query = SQLGenerator::delete()->from('users')->where("id=5");
mysql_query($query);

Basically they all follow this same general format. If you call a method twice, it appends the data, it doesn't overwrite it. The form of input is fairly variable, at least in terms of whether you pass an array or just each as an argument. To clear out some information, you can use the clear method, ie: $query->clear('from');

For both the fields and from methods, you can pass an associative array to generate aliases. The where method can take arrays as shown above, and nested ones too. If you want to use OR to group conditions, you simply pass an associative array with key as 'OR' and the value as the array of conditions to bind using OR.

Joins follow the format of array(TYPE, TABLE(S), ON)

The conditions in ON have all the same features as the where method.


Last Edit: Aug 26, 2008 4:12:51 GMT by Eric