/system/database/query.php
https://bitbucket.org/viswanath608/roar · PHP · 316 lines · 151 code · 64 blank · 101 comment · 12 complexity · b31eb12329307e8e2152563684a5bcf1 MD5 · raw file
- <?php namespace System\Database;
- /**
- * Nano
- *
- * Lightweight php framework
- *
- * @package nano
- * @author k. wilson
- * @link http://madebykieron.co.uk
- */
- use System\Database as DB;
- use System\Config;
- class Query {
- private $sql = '';
- private $bindings = array();
- private $wrapper = '`';
- private $table, $select, $join, $where, $group = array(), $order = array(), $limit, $offset;
- private $connection, $style;
- private function wrap($value) {
- // remove white space
- $column = trim($value);
- // handle aliases
- if(strpos(strtolower($column), ' as ') !== false) {
- list($col, $alias) = explode(' as ', strtolower($column));
- return $this->wrap($col) . ' AS ' . $this->wrap($alias);
- }
- // dont wrap function calls
- if(preg_match('/[a-z]+\(.*?\)/i', $column)) {
- return $column;
- }
- foreach(explode('.', $column) as $segment) {
- $sql[] = ($segment !== '*') ? $this->wrapper . $segment . $this->wrapper : $segment;
- }
- return implode('.', $sql);
- }
- private function columnizer($value) {
- if(is_array($value)) {
- foreach($value as $column) {
- $sql[] = $this->wrap($column);
- }
- return implode(', ', $sql);
- }
- return $this->wrap($value);
- }
- public static function table($table) {
- return new static($table);
- }
- public function __construct($table, $connection = null) {
- $this->table = $table;
- $this->connection = DB::connection($connection);
- $this->style = Config::get('database.fetch');
- }
- /*
- MySQL Joins
- Example:
- Db::table('users')
- ->join('groups', 'groups.id', '=', 'users.group')
- ->get(array('users.id', 'groups.name'));
- */
- public function join($table, $left, $operator, $right, $type = 'INNER') {
- $this->join .= ' ' . $type . ' JOIN ' . $this->wrap($table) . ' ON (' . $this->wrap($left) . ' ' . $operator . ' ' . $this->wrap($right) . ')';
- return $this;
- }
- public function left_join($table, $left, $operator, $right) {
- return $this->join($table, $left, $operator, $right, 'LEFT');
- }
- /*
- MySQL where clauses
- */
- public function where($column, $operator, $value) {
- $this->where .= (empty($this->where) ? ' WHERE ' : ' AND ') . $this->wrap($column) . ' ' . $operator . ' ?';
- $this->bindings[] = $value;
- return $this;
- }
- public function or_where($column, $operator, $value) {
- $this->where .= ' OR ' . $this->wrap($column) . ' ' . $operator . ' ?';
- $this->bindings[] = $value;
- return $this;
- }
- public function where_in($column, $keys) {
- if(count($keys)) {
- $instance = $this->connection->pdo;
- $keys = array_map(function($string) use ($instance) {
- return $instance->quote($string);
- }, $keys);
- $this->where .= (empty($this->where) ? ' WHERE ' : ' AND ') . $this->wrap($column) . ' IN (' . implode(',', $keys) . ')';
- }
- return $this;
- }
- public function where_is_null($column) {
- $this->where .= (empty($this->where) ? ' WHERE ' : ' AND ') . $this->wrap($column) . ' IS NULL';
- return $this;
- }
- public function or_where_is_null($column) {
- $this->where .= ' OR ' . $this->wrap($column) . ' IS NULL';
- return $this;
- }
- /*
- MySQL Sorting
- */
- public function take($num) {
- $this->limit = ' LIMIT ' . $num;
- return $this;
- }
- public function skip($num) {
- $this->offset = ' OFFSET ' . $num;
- return $this;
- }
- public function order_by($column, $mode = 'ASC') {
- $this->order[] = $this->wrap($column) . ' ' . strtoupper($mode);
- return $this;
- }
- public function group_by($column) {
- $this->group[] = $this->wrap($column);
- return $this;
- }
- /*
- Build SQL statement
- */
- public function build() {
- $select = empty($this->select) ? '*' : $this->columnizer($this->select);
- $ordering = count($this->order) ? ' ORDER BY ' . implode(', ', $this->order) : '';
- $grouping = count($this->group) ? ' GROUP BY ' . implode(', ', $this->group) : '';
- return 'SELECT ' . $select . ' FROM ' . $this->table . $this->join . $this->where . $grouping . $ordering . $this->limit . $this->offset;
- }
- /*
- MySQL select first col from first record
- Example:
- Query::table('visits')
- ->where('id', '=', 26)
- ->col();
- */
- public function col($column_number = 0) {
- $sql = $this->build();
- list($statement, $result) = $this->connection->execute($sql, $this->bindings);
- if($result) return $statement->fetchColumn($column_number);
- }
- /*
- MySQL select first record
- Example:
- Query::table('books')
- ->where('id', '=', 26)
- ->fetch(array('name', 'author'));
- */
- public function fetch($columns = array()) {
- $this->select = $columns;
- $sql = $this->build();
- list($statement, $result) = $this->connection->execute($sql, $this->bindings);
- if($result) return $statement->fetch($this->style);
- }
- /*
- MySQL get result set
- Example:
- Query::table('books')
- ->get(array('name', 'author'));
- */
- public function get($columns = array()) {
- $this->select = $columns;
- $sql = $this->build();
- list($statement, $result) = $this->connection->execute($sql, $this->bindings);
- if($result) return $statement->fetchAll($this->style);
- }
- /*
- Aggregate methods
- */
- public function count() {
- $this->select = 'COUNT(*)';
- $sql = $this->build();
- list($statement, $result) = $this->connection->execute($sql, $this->bindings);
- if($result) return $statement->fetchColumn();
- }
- /*
- MySQL Insert
- Example:
- Query::table('my_table')
- ->insert(array('id' => 1));
- */
- public function insert($data) {
- foreach($data as $k => $v) {
- $keys[] = $this->wrap($k);
- $tokens[] = '?';
- $bindings[] = $v;
- }
- $sql = 'INSERT INTO ' . $this->wrap($this->table) . ' (' . implode(', ', $keys) . ') values (' . implode(', ', $tokens) . ')';
- list($statement, $result) = $this->connection->execute($sql, $bindings);
- if($result) return $statement;
- }
- /*
- MySQL Insert
- Example:
- Query::table('my_table')
- ->insert(array('id' => 1));
- */
- public function insert_get_id($data) {
- if($statement = $this->insert($data)) {
- return $this->connection->pdo->lastInsertId();
- }
- }
- /*
- MySQL Update
- Example:
- Query::table('my_table')
- ->where('id', '=', 2)
- ->update(array('name' => 'dave'));
- */
- public function update($data) {
- foreach($data as $k => $v) {
- $update[] = $this->wrap($k) . ' = ?';
- $bindings[] = $v;
- }
- $sql = 'UPDATE ' . $this->wrap($this->table) . ' SET ' . implode(', ', $update) . $this->where;
- $bindings = array_merge($bindings, $this->bindings);
- list($statement, $result) = $this->connection->execute($sql, $bindings);
- if($result) return $statement->rowCount();
- }
- /*
- MySQL Delete
- Example:
- Query::table('my_table')
- ->where('id', '=', 1)
- ->delete();
- */
- public function delete() {
- $sql = 'DELETE FROM ' . $this->wrap($this->table) . $this->where;
- list($statement, $result) = $this->connection->execute($sql, $this->bindings);
- if($result) return $statement->rowCount();
- }
- }