数据库查询语句构造类

/**
 *  SQL Query Builder
 *
 *  @author CRH380A-2722 <609657831@qq.com>
 */

class QueryBuilder {

  /**
   *  查询语句碎片
   *
   *  @var array
   *  @access private
   */

  private $_querySnippets = array();

  /**
   *  数据表前缀
   *
   *  @var string
   *  @access private
   */

  private $_prefix = DB_PREFIX;

  /**
   *  构造函数
   *
   *  @return void
   *  @access public
   */

  public function __construct() {
    $this->_querySnippets = array();
  }

  /**
   *  insert
   *
   *  @param string $table 表名
   *  @return QueryBuilder
   *  @access public
   */

  public function insert($table) {
    $this->_querySnippets[] = 'INSERT INTO';
    $this->_querySnippets[] = $this->_prefix . $table;
    return $this;
  }

  /**
   *  values （要插入的数据）
   *
   *  @param array $data 数据集合
   *  @return QueryBuilder
   *  @access public
   */

  public function values(array $data) {
    $field = array();
    $value = array();
    foreach ($data as $f => $v) {
      $field[] = $f;
      $value[] = $v;
    }
    $this->_querySnippets[] = '(' . implode(',', $field) . ')';
    $this->_querySnippets[] = 'VALUES';
    $this->_querySnippets[] = '(' . implode(',', $value) . ')';
    return $this;
  }

  /**
   *  select
   *
   *  @param string $fields 字段名称
   *  @return QueryBuilder
   *  @access public
   */

  public function select($fields = '*') {
    $this->_querySnippets[] = 'SELECT';
    $this->_querySnippets[] = $fields;
    return $this;
  }

  /**
   *  select distinct
   *
   *  @param string $fields 字段名称
   *  @return QueryBuilder
   *  @access public
   */

  public function distinctSelect($fields = '*') {
    $this->_querySnippets[] = 'SELECT DISTINCT';
    $this->_querySnippets[] = $fields;
    return $this;
  }

  /**
   *  from
   *
   *  @param string $table 表名
   *  @return QueryBuilder
   *  @access public
   */

  public function from($table) {
    $this->_querySnippets[] = 'FROM';
    $this->_querySnippets[] = $this->_prefix . $table;
    return $this;
  }

  /**
   *  where
   *
   *  @param string $condition 选择的条件
   *  @return QueryBuilder
   *  @access public
   */

  public function where($condition) {
    $this->_querySnippets[] = 'WHERE';
    $this->_querySnippets[] = $condition;
    return $this;
  }

  /**
   *  and
   *
   *  @param string $condition 选择的条件
   *  @return QueryBuilder
   *  @access public
   */

  public function andWhere($condition) {
    $this->_querySnippets[] = 'AND';
    $this->_querySnippets[] = $condition;
    return $this;
  }

  /**
   *  or
   *
   *  @param string $condition 选择的条件
   *  @return QueryBuilder
   *  @access public
   */

  public function orWhere($condition) {
    $this->_querySnippets[] = 'OR';
    $this->_querySnippets[] = $condition;
    return $this;
  }

  /**
   *  as
   *
   *  @param string $alias 表别名
   *  @return QueryBuilder
   *  @access public
   */

  public function asAlias($alias) {
    $this->_querySnippets[] = 'AS';
    $this->_querySnippets[] = $alias;
    return $this;
  }

  /**
   *  join
   *
   *  @param string $type 联结类型
   *  @param string $table 表名
   *  @return QueryBuilder
   *  @access public
   */

  public function join($type, $table) {
    switch ($type) {
      case 'inner':
        $this->_querySnippets[] = 'INNER JOIN';
        break;
      case 'left':
        $this->_querySnippets[] = 'LEFT JOIN';
        break;
      case 'right':
        $this->_querySnippets[] = 'RIGHT JOIN';
        break;
      default: break;
    }
    $this->_querySnippets[] = $this->_prefix . $table;
    return $this;
  }

  /**
   *  on
   *
   *  @param string $condition 连接的条件
   *  @return QueryBuilder
   *  @access public
   */

  public function on($condition) {
    $this->_querySnippets[] = 'ON';
    $this->_querySnippets[] = $condition;
    return $this;
  }

  /**
   *  using
   *
   *  @param string $field 字段名称
   *  @return QueryBuilder
   *  @access public
   */

  public function using($field) {
    $this->_querySnippets[] = 'USING(' . $field . ')';
    return $this;
  }

  /**
   *  limit
   *
   *  @param int $limit 限制的条目数
   *  @param int $offset 决定从哪条结果开始
   *  @return QueryBuilder
   *  @access public
   */

  public function limit($limit, $offset = NULL) {
    $this->_querySnippets[] = 'LIMIT';
    if ($offset != NULL) {
      $this->_querySnippets[] = $offset . ',' . $limit;
    } else {
      $this->_querySnippets[] = $limit;
    }
    return $this;
  }

  /**
   *  order by
   *
   *  @param string $field 字段名
   *  @param string $type 排序类型 (ASC || DESC)
   *  @return QueryBuilder
   *  @access public
   */

  public function orderBy($field, $type) {
    $this->_querySnippets[] = 'ORDER BY';
    $this->_querySnippets[] = $field;
    $this->_querySnippets[] = $type;
    return $this;
  }

  /**
   *  group by
   *
   *  @param string $field 字段名
   *  @return QueryBuilder
   *  @access public
   */

  public function groupBy($field) {
    $this->_querySnippets[] = 'GROUP BY';
    $this->_querySnippets[] = $field;
    return $this;
  }

  /**
   *  update
   *
   *  @param string $table 表名
   *  @return QueryBuilder
   *  @access public
   */

  public function update($table) {
    $this->_querySnippets[] = 'UPDATE';
    $this->_querySnippets[] = $this->_prefix . $table;
    return $this;
  }

  /**
   *  set
   *
   *  @param string $values 重新设定的值
   *  @return QueryBuilder
   *  @access public
   */

  public function set($values) {
    $this->_querySnippets[] = 'SET';
    $this->_querySnippets[] = $values;
    return $this;
  }

  /**
   *  delete
   *
   *  @param string $table 表名
   *  @return QueryBuilder
   *  @access public
   */

  public function delete($table) {
    $this->_querySnippets[] = 'DELETE FROM';
    $this->_querySnippets[] = $this->_prefix . $table;
    return $this;
  }

  /**
   *  构建查询语句
   *
   *  @return string
   *  @access public
   */

  public function build() {
    return implode(' ', $this->_querySnippets) . ';';
  }

  /**
   *  @return string
   *  @access public
   */

  public function __toString() {
    return $this->build();
  }

}

 select('s.*, p.*')
            ->from('test')
            ->asAlias('s')
            ->join('left', 'projects')
            ->asAlias('p')
            ->using('shareId')
            ->where('userId = id')
            ->andWhere('userName = \'test\'')
            ->orderBy('userRegDate', 'DESC')
            ->limit(1, 20) . '';

print $query2->insert('table')
            ->values(array(
              'id' => "0",
              'test' => "'test text'",
              'date' => "NOW()"
            )) . '';

print $query3->update('table')
            ->set("`id` = 2333, `text` = 'test'")
            ->where('uid = 1')
            ->limit(1) . '';

print $query4->delete('table')
            ->where('uid = 1')
            ->limit(1) . '';

print $query5->distinctSelect('company')
            ->from('table') . '';

print $query6->select('SUM(price)')
            ->from('orders')
            ->groupBy('user');