source : https://github.com/Zakisu/QB_Mysql/blob/master/mysql/Mysql.php
please review mycode read the docs about the usage
<?php
/**
* Mysql Class
*
* @category Database Access
* @package Mysql
* @author Muhammad Zaki Sulistya <[email protected]>
* @copyright Copyright (c) 2016
*/
class QB_Mysql
{
/**
* PDO instance
* @var PDO
*/
protected $db;
/**
* Database credentials
* @var string
*/
private $host;
private $user;
private $passwd;
private $dbname;
/**
* Table prefix
* @var string
*/
protected $prefix;
/**
* Table name (with prefix, if used)
* @var string
*/
protected $table;
/**
* The parameter of the query
* @var array
*/
public $params = array();
/**
* Insert query
* @var string
*/
protected $insert;
/**
* Select query
* @var string
*/
protected $select;
/**
* Update query
* @var string
*/
protected $update;
/**
* Delete query
* @var string
*/
protected $delete;
/**
* An array that holds WHERE conditions
* @var array
*/
protected $where = array();
/**
* An array that holds OR WHERE conditions
* @var array
*/
protected $orWhere = array();
/**
* An array that holds HAVING WHERE conditions
* @var array
*/
protected $having = array();
/**
* An array that holds OR HAVING conditions
* @var array
*/
protected $orHaving = array();
/**
* Used to accommodate the value of ORDER BY condition
* @var array
*/
protected $orderBy = array();
/**
* Used to accommodate the value of GROUP BY condition
* @var array
*/
protected $groupBy = array();
/**
* Set a limit for a query
* @var int
*/
protected $limit;
/**
* To know if the query is a Subquery or not
* @var string
*/
protected $sub = "OFF";
/**
* Dynamic list of subquery
* @var array
*/
protected $subQuery = array();
/**
* The list of error
* @var array
*/
protected $errorLog = array();
/**
* @param string $host
* @param string $user
* @param string $passwd
* @param string $dbname
* @param string $prefix
*/
public function __construct($host = null, $user = null, $passwd = null, $dbname = null, $prefix = null)
{
if(is_array($host))
{
foreach($host as $key => $value)
{
$$key = $value;
}
}
$this->host = $host;
$this->user = $user;
$this->passwd = $passwd;
$this->dbname = $dbname;
$this->prefix = $prefix;
$this->connect();
}
/**
* A method to connect to the database
*
* @return void
*/
private function connect()
{
try
{
$this->db = new PDO("mysql:host={$this->host};dbname={$this->dbname}", $this->user, $this->passwd);
}
catch(PDOException $e)
{
$this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
}
}
/**
* Execute SQL query.
*
* @param string $query User-provided query to execute.
* @param array $params Variables array to bind to the SQL statement.
*
* @return Mysql returned a PDOStatement object, or FALSE on failure
*/
public function query($query, $params = null)
{
try
{
if($params == null)
{
$stmt = $this->db->query($query);
}
else
{
$stmt = $this->db->prepare($query);
$stmt->execute($params);
}
return $stmt;
}
catch(PDOException $e)
{
$this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
}
}
/**
* To make sure that the query as subquery
*
* @return Mysql
*/
public function subQuery()
{
$this->sub = "ON";
return $this;
}
/**
* Set the table of the query
*
* @return Mysql
*/
public function table($table)
{
$this->table = $table;
return $this;
}
/**
* Select columns
*
* @param array $cols Desired columns
*
* @return Mysql
*/
public function select($cols = "*")
{
$table = $this->prefix.$this->table;
if(is_array($cols))
{
$cols = implode(", ", $cols);
}
$this->select = "SELECT
{$cols}
FROM
{$table} ";
return $this;
}
/**
* Insert method to add new row
*
* @param array $data Data containing information for inserting into the DB.
*
* @return Mysql
*/
public function insert($data)
{
$table = $this->prefix.$this->table;
$value = array_values($data);
for($i=0;$i<count($value);$i++)
{
$generateKey = uniqid(":");
$this->params[$generateKey] = $value[$i];
$value[$i] = $generateKey;
}
$cols = implode(", ", array_keys($data));
$valueCols = implode(", ", $value);
$this->insert = "INSERT INTO
{$table}
({$cols})
VALUES
({$valueCols})";
return $this;
}
/**
* Update query
*
* @param array $data Array of data to update the desired row
*
* @return Mysql
*/
public function update($data)
{
$table = $this->prefix.$this->table;
$point = array();
foreach ($data as $key => $value) {
$generateKey = uniqid(":");
$this->params[$generateKey] = $value;
$value = $generateKey;
array_push($point, ($key." = ".$value));
}
$update = implode(", ", $point);
$this->update = "UPDATE
{$table}
SET
{$update} ";
return $this;
}
/**
* Delete query
*
* @return Mysql
*/
public function delete()
{
$table = $this->prefix.$this->table;
$this->delete = "DELETE
FROM
{$table} ";
return $this;
}
/**
* Just an ordinary method to filter for AND/OR statements for SQL queries
*
* @param string $option (AND/OR) statements
* @param string the value of the statements
*
* @return void
*/
protected function whereFilter($option, $value)
{
try
{
if(strtoupper($option) == "AND")
{
array_push($this->where, $value);
}
elseif(strtoupper($option) == "OR")
{
array_push($this->orWhere, $value);
}
else
{
throw new Exception("Error Processing Query");
}
}
catch (Exception $e)
{
$this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
}
}
/**
* Just an ordinary method to filter for AND/OR statements for SQL queries
*
* @param string $option (AND/OR) statements
* @param string the value of the statements
*
* @return void
*/
protected function havingFilter($option, $value)
{
try
{
if(strtoupper($option) == "AND")
{
array_push($this->having, $value);
}
elseif(strtoupper($option) == "OR")
{
array_push($this->orHaving, $value);
}
else
{
throw new Exception("Error Processing Query");
}
}
catch (Exception $e)
{
$this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
}
}
/**
* This method allows you to specify multiple (method chaining optional) AND/OR/HAVING WHERE statements for SQL queries.
*
* @param string $cols The name of column.
* @param string $operator Comparison operator. Default is =
* @param string $value The value of the column
* @param string $option Condition of where statement (OR, AND, HAVING)
*
* @return Mysql
*/
public function where($cols, $operator = "=", $value, $option = "AND")
{
try
{
$listOperator = ["=", ">", "<", "<=", ">=", "!=", "BETWEEN", "NOT BETWEEN", "IN", "NOT IN"];
$listOption = array("AND", "OR");
if(in_array($operator, $listOperator) AND in_array($option, $listOption))
{
if(strtoupper($operator) == "BETWEEN" OR strtoupper($operator) == "NOT BETWEEN")
{
if((is_array($value) AND (count($value) > 2)) OR empty($value))
{
throw new Exception("Error Processing [BETWEEN] or [NOT BETWEEN] Query");
}
else
{
for($i=0;$i<count($value);$i++)
{
if(!empty($this->subQuery) AND $this->sub != "ON")
{
if(in_array($value[$i], $this->subQuery))
{
$value[$i] = "(".$value[$i].")";
}
else
{
$generateKey = uniqid(":");
$this->params[$generateKey] = $value[$i];
$value[$i] = $generateKey;
}
}
}
$value = "{$cols} {$operator} ".implode(" AND ", $value);
$this->whereFilter($option, $value);
}
}
elseif(strtoupper($operator) == "IN" OR strtoupper($operator) == "NOT IN")
{
for($i=0;$i<count($value);$i++)
{
if(!empty($this->subQuery) AND $this->sub != "ON")
{
if(in_array($value[$i], $this->subQuery))
{
$value[$i] = "(".$value[$i].")";
}
else
{
$generateKey = uniqid(":");
$this->params[$generateKey] = $value[$i];
$value[$i] = $generateKey;
}
}
}
$value = "{$cols} {$operator} (".implode(", ", $value).")";
$this->whereFilter($option, $value);
}
else
{
if(!empty($this->subQuery) AND $this->sub != "ON")
{
if(in_array($value, $this->subQuery))
{
$value = "(".$value.")";
}
else
{
$generateKey = uniqid(":");
$this->params[$generateKey] = $value;
$value = $generateKey;
}
$value = "{$cols} {$operator} {$value}";
}
else
{
$generateKey = uniqid(":");
$this->params[$generateKey] = $value;
$value = $generateKey;
$value = "{$cols} {$operator} {$value}";
}
$this->whereFilter($option, $value);
}
}
else
{
throw new Exception("Error Processing The Query");
}
return $this;
}
catch(Exception $e)
{
$this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
}
}
/**
* This method allows you to specify multiple (method chaining optional) AND/OR statements for SQL queries.
*
* @param string $cols The name of column.
* @param string $operator Comparison operator. Default is =
* @param string $value The value of the column
* @param string $option Condition of having statement (OR/AND)
*
* @return Mysql
*/
public function having($cols, $operator = "=", $value, $option = "AND")
{
try
{
$listOperator = ["=", ">", "<", "<=", ">=", "!="];
$listOption = array("AND", "OR");
if(in_array($operator, $listOperator) AND in_array($option, $listOption))
{
if(!empty($this->subQuery) AND $this->sub != "ON")
{
if(in_array($value, $this->subQuery))
{
$value = "(".$value.")";
}
else
{
$generateKey = uniqid(":");
$this->params[$generateKey] = $value;
$value = $generateKey;
}
$value = "{$cols} {$operator} {$value}";
}
else
{
$generateKey = uniqid(":");
$this->params[$generateKey] = $value;
$value = $generateKey;
$value = "{$cols} {$operator} {$value}";
}
$this->havingFilter($option, $value);
}
else
{
throw new Exception("Error Processing The Query");
print_r($db->getErrorLog());
}
return $this;
}
catch(Exception $e)
{
$this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
}
}
/**
* This method allows you to specify multiple (method chaining optional) GROUP BY statements for SQL queries.
*
* @param string $cols The name of the database field.
*
* @return Mysql
*/
public function groupBy($cols)
{
if(is_array($cols))
{
$cols = implode(", ", $cols);
}
array_push($this->groupBy, "{$cols}");
return $this;
}
/**
* This method allows you to specify multiple (method chaining optional) ORDER BY statements for SQL queries.
*
* @param string $cols The name of the database field.
* @param string $operator ASC/DESC statement.
* @param array $fields Fieldset for ORDER BY FIELD() ordering
*
* @return Mysql
*/
public function orderBy($cols, $field = null, $operator = "ASC")
{
$operator = strtoupper($operator);
if(is_array($cols))
{
$cols = implode(", ", $cols);
}
if(($field == null) AND ($operator == "ASC" OR $operator == "DESC"))
{
array_push($this->orderBy, "{$cols} {$operator}");
}
elseif(!empty(is_array($field)))
{
for($i=0;$i<count($field);$i++)
{
$field[$i] = "'".$field[$i]."'";
}
$field = implode(", ", $field);
array_push($this->orderBy, "FIELD({$cols}, {$field})");
}
return $this;
}
/**
* This method allows you to set the limit for the SQL query.
*
* @param int $limit set the limit for the SQL query.
*
* @throws Exception
* @return Mysql
*/
public function limit($limit)
{
try
{
if(is_int($limit) OR is_float($limit))
{
$this->limit = $limit;
}
else
{
throw new Exception("The value of limit parameter should be an integer");
}
return $this;
}
catch(Exception $e)
{
$this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
}
}
/**
* This method allows you to merge all statements.
*
* @return string
*/
protected function queryBuilder()
{
$query = null;
if(!empty($this->insert))
{
$query = $this->insert;
}
if(!empty($this->select))
{
$query = $this->select;
}
if(!empty($this->update))
{
$query = $this->update;
}
if(!empty($this->delete))
{
$query = $this->delete;
}
if(!empty($this->where) OR !empty($this->orWhere))
{
$query .= " WHERE ";
}
if(!empty($this->where))
{
$query .= implode(" AND ", $this->where);
}
if(!empty($this->orWhere))
{
if(!empty($this->where))
{
$query .= " OR ";
}
$query .= implode(" OR ", $this->orWhere);
}
if(!empty($this->groupBy))
{
$query .= " GROUP BY ".implode(",", $this->groupBy);
}
if(!empty($this->having) OR !empty($this->orHaving))
{
$query .= " HAVING ";
}
if(!empty($this->having))
{
$query .= implode(" AND ", $this->having);
}
if(!empty($this->orHaving))
{
if(!empty($this->having))
{
$query .= " OR ";
}
$query .= implode(" OR ", $this->orHaving);
}
if(!empty($this->orderBy))
{
$query .= " ORDER BY ".implode(",", $this->orderBy);
}
if(!empty($this->limit))
{
$query .= " LIMIT {$this->limit}";
}
return $query;
}
/**
* This method allows you to set a subquery.
*
* @return string
*/
public function get()
{
try
{
$query = $this->queryBuilder();
if($this->sub == "ON")
{
array_push($this->subQuery, $query);
$this->sub = "OFF";
$this->where = array();
$this->orWhere = array();
$this->having = array();
return $query;
}
else
{
throw new Exception("Error Processing Request");
}
}
catch(Exception $e)
{
$this->catchError($e->getFile(), $e->getLine(), $e->getMessage());
}
}
/**
* This method allows you to run the SQL query.
*
* @return Mysql
*/
public function run()
{
$query = $this->queryBuilder();
if(empty($this->params))
{
$run = $this->query($query);
}
else
{
$run = $this->query($query, $this->params);
}
$this->reset();
return $run;
}
/**
* This method is used to catch an error and log it.
*
* @return void
*/
protected function catchError($file, $line, $message)
{
$error = array("file"=>$file, "line"=>$line, "message"=>$message);
array_push($this->errorLog, $error);
}
/**
* This method is used to dump all error info.
*
* @return array returned errorLog property
*/
public function getErrorLog()
{
return $this->errorLog;
}
/**
* Reset states after an execution.
*
* @return void
*/
protected function reset()
{
$this->table = "";
$this->params = array();
$this->select = "";
$this->where = array();
$this->orWhere = array();
$this->having = array();
$this->orHaving = array();
$this->orderBy = array();
$this->groupBy = array();
$this->limit = "";
$this->subQuery = array();
}
}
?>
