A basic query builder class that provides wrappers for building PDO CRUD statements.

pdo_query_builder.php

<?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);
    }
}