Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.

I created a PHP class to handle building SQL query strings. I need advice on how to make the code more efficient.

<?php

/**
 * Example: 
 *
 * $query = new SQLQuery();
 * $query->select('*')->from('users')->where(array('banned' => 0))->order('id' => 'asc')->limit(1);
 * $result = $query->execute();
 *
**/

requireComponent('Database'); 

define('SQLQUERY_INSERT', 1);
define('SQLQUERY_UPDATE', 2);
define('SQLQUERY_DELETE', 3);
define('SQLQUERY_SELECT', 4);
define('SQLQUERY_REPLACE', 5);
define('SQLQUERY_CUSTOM', 6);


class SQLQuery extends Component
{
    private $type = null;              // Query type
    private $from = null;              // Table name
    private $select = array();         // Selected fields
    private $where = array();          // Where conditions
    private $order = array();          // Order conditions
    private $fields = array();         // Insert fields
    private $where_condition = null;   // Where condition (AND, OR)
    private $join_table = null;        // Joined table
    private $join_on = null;           // Join constraint
    private $limit = 0;                // Row limit
    private $custom_sql = null;        // Explicit SQL code
    private $custom_args = array();    // Explicit SQL arguments


    // Initializes an SQLQuery object with an optional preset table name
    public function __construct($table_name = null)
    {
        if ($table_name !== null)
            $this->from = $table_name; 
    }


    // Wraps a string in backticks
    private function bwrap($s)
    {
        return surround('`', $s);
    }


    // Returns the SQL code for a SELECT clause
    private function getSelectClause()
    {
        $append = ($this->fields == '*') ? $this->fields : implode(',' , array_map(array($this, 'bwrap'), is_array($this->fields) ? $this->fields : array($this->fields))); 
        return 'SELECT ' . $append; 
    }


    // Returns the SQL code for an UPDATE clause
    private function getUpdateClause()
    {
        $sql = 'UPDATE ' . $this->bwrap($this->from) . ' SET '; 

        $fields = array(); 
        foreach ($this->fields as $key => $value)
        {
            $value = strcmp_is($value, 'null') ? 'NULL' : '"' . sql($value) . '"'; 
            $fields[] = $this->bwrap($key) . ' = ' . $value; 
        }

        return $sql . implode(',', $fields);
    }


    // Returns the SQL code for a DELETE clause
    private function getDeleteClause()
    {
        return 'DELETE'; 
    }


    // Returns the SQL code for a WHERE clause
    private function getWhereClause()
    {
        if (count($this->where) == 0)
            return ''; 

        $operators = array('< ', '> ', '<=', '>=', '!=', '<>');
        $fields = array(); 

        foreach ($this->where as $key => $value)
        {
            // Check first two characters of $value to search for operators
            $op = $value[0] . $value[1]; 
            $op_exists = in_array($op, $operators);
            $joiner = $op_exists ? $op : (($value == null) ? 'IS' : '=');
            $value = empty($value) ? 'NULL' : '"' . sql(trim($op_exists ? str_replace($op, '', $value) : $value)) . '"'; 
            $fields[] = $this->bwrap($key) . ' ' . $joiner . ' ' . $value; 
        }

        return 'WHERE ' . implode(' ' . $this->where_condition . ' ', $fields);
    }


    // Returns the SQL code for a FROM clause
    private function getFromClause()
    {
        return 'FROM ' . $this->bwrap($this->from); 
    }


    // Returns the SQL code for a JOIN clause
    private function getJoinClause()
    {
        if (!$this->join_table || !$this->join_on)
            return ''; 

        return sprintf('INNER JOIN %s ON (%s.%s = %s.%s)', $this->join_table, $this->from, $this->join_on[0], 
            $this->join_table, $this->join_on[1]);
    }


    // Returns the SQL code for an ORDER BY clause
    private function getOrderByClause()
    {
        if (count($this->order) == 0)
            return ''; 

        $allowed = array('asc', 'desc'); 
        $fields = array(); 

        foreach ($this->order as $key => $value)
        {
            if (in_array(strtolower($value), $allowed))
                $fields[] = $this->bwrap($key) . ' ' . strtoupper($value);
        }

        return 'ORDER BY ' . implode(',', $fields);
    }


    // Returns the SQL code for a LIMIT clause
    private function getLimitClause()
    {
        return ($this->limit > 0) ? 'LIMIT ' . $this->limit : ''; 
    }


    // Builds a query string for a SELECT operation
    private function buildSelectQueryString()
    {
        return implode(' ', array($this->getSelectClause(), $this->getFromClause(), $this->getWhereClause(),
            $this->getJoinClause(), $this->getOrderByClause(), $this->getLimitClause()));
    }


    // Builds a query string for an INSERT operation
    private function buildInsertQueryString()
    {
        // No easy way to do it in one line
        $values = array_values($this->fields);
        $count = count($values);

        for ($c = 0; $c < $count; $c++)
        {
            if ($values[$c] == 'NOW()')
                $values[$c] = 'NOW()';
            else
                $values[$c] = '"' . sql($values[$c]) . '"';
        }

        $sql = 'INSERT INTO ' . $this->bwrap($this->from) . ' '; 
        $sql .= '(' . implode(',', array_map(array($this, 'bwrap'), array_keys($this->fields))) . ') VALUES ';
        $sql .= '(' . implode(',', array_values($values)) . ')'; 

        return $sql; 
    }


    // Builds a query string for a replace operation
    private function buildReplaceQueryString()
    {
        return 'REPLACE' . substr($this->buildInsertQueryString(), 6);
    }


    // Builds a query string for an UPDATE operation
    private function buildUpdateQueryString()
    {
        return implode(' ', array($this->getUpdateClause(), $this->getWhereClause(), $this->getLimitClause()));
    }


    // Builds a query string for a DELETE operation
    private function buildDeleteQueryString()
    {
        return implode(' ', array($this->getDeleteClause(), $this->getFromClause(), $this->getWhereClause(),
            $this->getLimitClause()));
    }


    // Returns the finalized query string for the appropriate query type
    public function buildQueryString()
    {
        switch ($this->type)
        {
            case SQLQUERY_SELECT:
                return $this->buildSelectQueryString(); 
                break; 

            case SQLQUERY_UPDATE:
                return $this->buildUpdateQueryString();
                break; 

            case SQLQUERY_INSERT:
                return $this->buildInsertQueryString();
                break; 

            case SQLQUERY_REPLACE:
                return $this->buildReplaceQueryString();
                break; 

            case SQLQUERY_DELETE:
                return $this->buildDeleteQueryString();
                break;

            case SQLQUERY_CUSTOM:
                return $this->custom_sql; 
                break; 
        }

        trigger_error('Could not call SQLQuery::buildQueryString because of bad query type', E_USER_ERROR);
    }


    // Executes the SQL query and returns the results
    public function execute()
    {
        // Build query string
        $query_string = $this->buildQueryString(); 

        // Log SQL query if needed
        if (!Logger::busy() && Config::read('Logger.log_sqlqueries') && componentExists('Logger'))
        {
            Logger::log(sprintf('SQL query executed: %s', $query_string), LOGGER_CATEGORY_SQLQUERY);
        }

        // Note: Since prepared statements technically aren't being used in this case, 
        // the inputs must be escaped beforehand

        return ($this->type != SQLQUERY_CUSTOM) ? 
            call_user_func_array(array('Database', 'query'), array($query_string)) :
            call_user_func_array(array('Database', 'query'), array_merge(array($this->custom_sql), $this->custom_args));
    }


    // Ignores all other parameters and runs explicitly specified SQL code
    public function explicit()
    {
        $args = func_get_args();
        $this->type = SQLQUERY_CUSTOM; 
        $this->custom_sql = array_shift($args);
        $this->custom_args = $args; 

        return $this; 
    }


    // Initializes a SELECT query with selected fields
    public function select($fields)
    {
        $this->type = SQLQUERY_SELECT; 
        $this->fields = $fields; 

        return $this; 
    }


    // Initializes an INSERT query with starting fields
    public function insert($fields)
    {
        $this->type = SQLQUERY_INSERT; 
        $this->fields = $fields; 

        return $this; 
    }


    // Initializes a REPLACE query with starting fields
    public function replace($fields)
    {
        $this->type = SQLQUERY_REPLACE; 
        $this->fields = $fields; 

        return $this; 
    }


    // Initializes an UPDATE query with updated fields
    public function update($fields)
    {
        $this->type = SQLQUERY_UPDATE; 
        $this->fields = $fields; 

        return $this; 
    }


    // Initializes a DELETE query
    public function delete()
    {
        $this->type = SQLQUERY_DELETE; 
        $this->fields = array(); 

        return $this; 
    }


    // Sets the FROM field
    public function from($from)
    {
        $this->from = $from; 

        return $this; 
    }


    // Sets the WHERE field
    public function where($fields)
    {
        $this->where = $fields; 
        $this->where_condition = $fields['_condition']; 
        unset($this->where['_condition']);

        return $this; 
    }


    // Sets the WHERE field with AND condition
    public function whereAnd($fields)
    {
        return $this->where(array_merge($fields, array('_condition' => 'AND')));
    }


    // Sets the WHERE field with OR condition
    public function whereOr($fields)
    {
        return $this->where(array_merge($fields, array('_condition' => 'OR')));
    }


    // Sets the JOIN field
    public function join($table, $on)
    {
        $this->join_table = $table; 
        $this->join_on = $on; 

        return $this; 
    }


    // Sets the ORDER BY field
    public function order($by)
    {
        $this->order = $by; 

        return $this; 
    }


    // Sets the LIMIT field
    public function limit($limit)
    {
        if ($limit > 0)
            $this->limit = $limit; 

        return $this; 
    }
}

?>

Also another thing - how can I handle WHERE conditions? Like WHERE conditions should support AND, OR, and nested ANDs and ORs, and also a variety of operators (<, >, >=, <=, !=) and LIKE, NOW(), etc. There's just so many of them though, and I can't think of a way to implement all that without resorting to ugly hacks.

share|improve this question

2 Answers 2

I usually use an existing solution too, but it is good practice to write your own. In your specific problem I would use a separate class for expression. Something like the Expression class of onPHP. You can make one class for binary expression and other classes for special expressions like "equals", "in", etc. For difficult expressions with a lot of logic you can also write expression chains with logical links such as "and" and "or". One more advice, check the type in special methods, for example: If you have an "order" for an insert query, throw an exception and you will need less tests.

share|improve this answer

I usually prefer to use existing solutions, can you look at the PHP Doctrine Query Builder, which has its own DQL language inspired by HQL (Hibernate Query Language), while its focused on objects it may provide useful pointers at https://github.com/doctrine/dbal/tree/master/lib/Doctrine/DBAL/Query

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.