Skip to content

Instantly share code, notes, and snippets.

@benedict-w
Created November 5, 2012 16:00
Show Gist options
  • Save benedict-w/4017971 to your computer and use it in GitHub Desktop.
Save benedict-w/4017971 to your computer and use it in GitHub Desktop.
A basic query builder class that provides wrappers for building PDO CRUD statements.
<?php
/**
* PDO Query Builder
*
* A basic query builder class that provides wrappers for building PDO queries.
*
* @author Ben Wallis
*/
class pdo_query_builder {
/**
* PDO
*
* Hold the PDO data object
*
* @var \db_pdo
*/
public $pdo = null;
/**
* Cols
*
* Keys define the column names used in the model. Values define the PDO type.
*
* @var array
*/
public $cols = array();
/**
* Table Name
*
* Define the correspoding table name for this model.
*
* @var string
*/
public $table_name = '';
/**
* Data
*
* The data to bind to the query.
*
* @var string
*/
public $data = array();
/**
* Query
*
* The query to execute.
*
* @var string
*/
public $query = '';
/**
* Cmd
*
* The PDO prepared statement
*
* @var object
*/
private $cmd = null;
public function __construct(\db_pdo $pdo, $table_name, array $cols){
$this->pdo = $pdo;
$this->table_name = $table_name;
$this->cols = $cols;
}
/**
* addMarkers
*
* Adds markers for an INSERT or UPDATE statement
*
* @param array $array
* @param bool $update - format markers for an UPDATE stament
*
* @return array
*/
private function addMarkers(array $array, $operator = false){
$markers = array();
foreach($array as $key => &$val) {
$stmt = '';
if ($operator) {
$stmt = "{$key}{$operator}";
}
if (in_array($val, array('NOW()'))){
$stmt .= $val;
} else {
$stmt .= ":{$key}";
}
$markers[$key] = $stmt;
}
return $markers;
}
/**
* Bind Params
*
* Generic function for binding params to the supplied data array
* according to the columns present..
*
* @param string $query - PDO query object by reference
* @param array $data
* @throws \Exception
*
* @return void
*/
public function bindParams (){
foreach($this->data as $key=>&$val) {
if (array_key_exists($key, $this->cols)){
if (is_array($val)){
//var_dump($this->query, $this->data);
//debug_print_backtrace();
throw new \Exception("Invalid value supplied for column {$key}: " . print_r($val));
}
$this->cmd->bindParam(":{$key}", $val, $this->cols[$key]);
}
}
}
/**
* Execute
*
* PDO fetch wrapper
* - prepares the query
* - binds the params
* - fetches the resuls
*
* @throws PDOException
*
* @return associative array $result
*/
public function execute() {
$this->cmd = $this->pdo->prepare($this->query);
if (count($this->data)) {
$this->bindParams();
}
$result = false;
try {
$query->execute();
$query->setFetchMode(\PDO::FETCH_ASSOC);
$result = $query->fetch();
} catch (\PDOException $ex) {
// TODO error handling
throw $ex;
}
return $result;
}
/**
* Insert Batch
*
* Build the parameterized INSERT query, and loop inserts through the batch.
*
* @param array $data
* @param bool $ignore
*
* @return boolean success
*/
public function insertBatch(array $data, $ignore = false) {
// use the first row to build the statement
$first = reset($data);
$this->query = $this->insert($first, $ignore);
foreach($data as &$row) {
$this->bindParams();
$result = $this->execute();
}
return true;
}
/**
* Select
*
* Build a basic SELECT query.
*
* @param array $cols - array of columns to return
*
* @return string
*/
public function select(array $cols = array(), $alias=''){
if (count($cols)) {
$cols = array_intersect_key($cols, $this->cols);
} else {
$cols = array('*');
}
$this->query = "SELECT " . join(', ', $cols) .
" FROM {$this->pdo->prefix}_{$this->table_name}";
if(!empty($alias)) {
$this->query = " AS {$alias}";
}
return $this->query;
}
/**
* Insert
*
* Build a basic INSERT query.
*
* @param array $data = array('col'=>'val',...)
* @param boolean $ignore - true to IGNORE duplicates in MySQL
*
* @return string
*/
public function insert($ignore = false){
if (!count($this->data)){
throw new \exception("No data to insert.");
}
$data = array_intersect_key($this->data, $this->cols);
$markers = $this->addMarkers(array_keys($this->data));
$this->query = "INSERT ";
if ($ignore) {
$this->query .= 'IGNORE ';
}
$this->query .= "INTO {$this->pdo->prefix}_{$this->table_name}
(" . join(', ', array_keys($this->data)) . ")
VALUES (" . join(', ', $markers) . ")";
return $this->query;
}
/**
* Update
*
* Builds a basic UPDATE query.
*
* @param array $data - data to bind to the update query
*
* @return string
*/
public function update(){
$this->data = array_intersect_key($this->data, $this->cols);
if (empty($this->data)){
return true;
}
$markers = $this->addMarkers(array_keys($this->data), '=');
$this->query = "UPDATE {$this->pdo->prefix}_{$this->table_name}
SET " . join(', ',$markers);
return $this->query;
}
/**
* Insert or Update
*
* Builds a basic INSERT or UPDATE query.
*
* @param array $data - data to bind to the update query
*
* @return string
*/
public function insertOrUpdate() {
$markers = $this->addMarkers(array_keys($this->data), '=');
$this->query = $this->insert()
. " ON DUPLICATE KEY UPDATE "
. join(',', $markers);
return $this->query;
}
/**
* Delete
*
* Builds a basic DELETE query.
*
* @param array $where - array of where clauses indexed on column name (e.g. array('col1'=>value))
*
* @return string
*/
public function delete(){
$this->query = "DELETE FROM {$this->pdo->prefix}_{$this->table_name} WHERE ";
return $this->query;
}
/**
* join
*
* @param array $where
*
* @return string
*/
public function join($table, $lcol, $rcol, $alias='', $join='INNER'){
return $this->query .= " {$join} JOIN {$this->pdo->prefix}_{$table} AS {$alias}
ON {$this->pdo->prefix}_{$this->table_name}.{$col} = {$table}.{$lcol}";
}
/**
* Where
*
* Adds a WHERE clause to the query if not present
*
* @return void
*/
public function where(array $where = array()) {
if (!preg_match('/ WHERE /', $this->query)) {
$this->query .= " WHERE ";
}
}
/**
* andWhere
*
* Adds a WHERE clause with AND operator.
*
* NB - bind the WHERE data to $this->data array.
*
* @param array $where - array of col names in the WHERE
* @param string $operator - the relational operator to apply, e.g. '=','>','<',... etc
*
* @return string
*/
public function andWhere(array $where, $operator='='){
$this->where();
$markers = $this->addMarkers($where, $operator);
return $this->query .= join(' AND ', $markers);
}
/**
* orWhere
*
* @param array $where
*
* @return string
*/
public function orWhere(array $where, $operator='='){
$this->where();
$markers = $this->addMarkers($where, $operator);
return $this->query .= join(' OR ', $markers);
}
/**
* orWhere
*
* @param array $where
*
* @return string
*/
public function orderBy(array $order){
return $this->query .= " ORDER BY " . join(' AND ', $order);
}
/**
* Escape Like
*
* Used to escape user params applied to a LIKE clause
*
* @param string $s
* @param string $e
*
* @param string - LIKE escaped.
*/
public static function escapeLike($s, $e){
return str_replace(array($e, '_', '%'), array("{$e}{$e}", "{$e}_", "{$e}%"), $s);
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment