QueryBuilder

gistfile1.php

<?php

/**
 * Ahmad Karimpour <me@ahmadina.com>
 *
 * Created_at 2012
 *
 * Class Enum
 */
class Enum
{
    const SELECT       = 'SELECT';
    const WHERE        = 'WHERE';
    const UPDATE       = 'UPDATE';
    const DELETE       = 'DELETE';
    const INSERT       = 'INSERT INTO';
    const FROM         = 'FROM';
    const VALUES       = 'VALUES';
    const EQUAL        = '=';
    const NOT          = '!=';
    const BETWEEN      = 'BETWEEN';
    const _AND         = 'AND';
    const _OR          = 'OR';
    const IN           = 'IN';
    const ALL          = '*';
    const SET          = 'SET';
    const SEPARATE     = ' ';
    const OPENBRACKET  = '(';
    const CLOSEBRACKET = ')';
    const JOIN         = 'JOIN';
    const ORDER        = 'ORDER BY';
    const ASC          = 'ASC';
    const DESC         = 'DESC';
    const LIKE         = 'LIKE';
    const QUOTATION    = "'";
    const DISTINCT     = 'DISTINCT';
    const COMMA        = ',';
    const GROUPBY      = 'GROUPBY';
    const TABLES       = 'TABLES';
    const SHOW         = 'SHOW';
}

/**
 * Class QueryBuilder
 */
class QueryBuilder
{

    private static $_query    = '';       // query statement here
    private static $_instance = NULL;     // chaining in static menu for return $this

    /**
     * return $this for chaining
     *
     * @return Obj QueryBuilder
     */
    private static function this ()
    {
        if (self::$_instance === NULL)
        {
            self::$_instance = new self;
        }

        return self::$_instance;
    }

    /**
     * create SELECT statement
     *
     * @param  array $tables
     * @param  array $fields
     *
     * @return Obj QueryBuilder
     * @throws \Exception
     */
    public static function select ($tables , $fields , $distinct = FALSE)
    {
        //if tableName is null or empty
        if ($tables == '' && $tables == NULL)
        {
            throw new \Exception("Choice tables" , 1);
        }

        //convert array tables to string
        $str_tables = implode (',' , $tables);

        //convert array fields ro string OR select all
        $str_fields = $fields == 'all' ? Enum::ALL : implode (',' , $fields);

        //set statement SELECT
        self::$_query .= Enum::SELECT . Enum::SEPARATE . ($distinct ? Enum::DISTINCT . Enum::SEPARATE : Enum::SEPARATE) . $str_fields . Enum::SEPARATE . Enum::FROM . Enum::SEPARATE . $str_tables . Enum::SEPARATE;

        return self::this ();
    }

    /**
     * create SELECT AGGREGATE_FUNCTION(FIELD) statement
     *
     * @param  array   $tables
     * @param  array   $fields
     * @param  string  $aggregateName
     * @param  string  $aggregateField
     * @param  boolean $distinct
     *
     * @return Obj QueryBuilder
     * @throws \Exception
     */
    public static function aggregate ($tables , $fields , $aggregateName , $aggregateField , $distinct = FALSE)
    {
        //if tableName is null or empty
        if ($tables == '' && $tables == NULL)
        {
            throw new \Exception("Choice tables" , 1);
        }

        //convert array tables to string
        $str_tables = implode (',' , $tables);

        //convert array fields ro string OR select all
        $str_fields = $fields == 'all' ? Enum::ALL : implode (',' , $fields);

        //set statement SELECT AGGREGATE_FUNCTION(FIELD)
        self::$_query .= Enum::SELECT . Enum::SEPARATE . strtoupper ($aggregateName) . Enum::OPENBRACKET . $aggregateName . Enum::CLOSEBRACKET . Enum::COMMA . Enum::SEPARATE . ($distinct ? Enum::DISTINCT . Enum::SEPARATE : Enum::SEPARATE) . $str_fields . Enum::SEPARATE . Enum::FROM . Enum::SEPARATE . $str_tables . Enum::SEPARATE;

        return self::this ();
    }

    /**
     * create WHERE statement
     *
     * @param  string $fieldOne | field name
     * @param  string $order    | order keword | = <> ...
     * @param  string $fieldTwo | field name
     *
     * @return Obj QueryBuilder
     */
    public static function where ($fieldOne , $order , $fieldTwo)
    {
        //if $_query has keyword WHERE
        if (strpos (self::$_query , Enum::WHERE) === FALSE)
            //set statement WHERE
        {
            self::$_query .= Enum::WHERE . Enum::SEPARATE . $fieldOne . Enum::SEPARATE . $order . Enum::SEPARATE . Enum::QUOTATION . $fieldTwo . Enum::QUOTATION . Enum::SEPARATE;
        }
        else
            //set statement WHERE
        {
            self::$_query .= Enum::_AND . Enum::SEPARATE . $fieldOne . Enum::SEPARATE . $order . Enum::SEPARATE . Enum::QUOTATION . $fieldTwo . Enum::QUOTATION . Enum::SEPARATE;
        }

        return self::this ();
    }

    /**
     * create WHERE OR statement
     *
     * @param  string $fieldOne | field name
     * @param  string $order    | order keword | = <> ...
     * @param  string $fieldTwo | field name
     *
     * @return Obj QueryBuilder
     * @throws \Exception
     */
    public static function whereOr ($fieldOne , $order , $fieldTwo)
    {
        //if $_query has keyword WHERE
        if (strpos (self::$_query , Enum::WHERE) === FALSE)
        {
            //set statement WHERE
            self::$_query .= Enum::WHERE . Enum::SEPARATE . Enum::_OR . Enum::SEPARATE . $fieldOne . Enum::SEPARATE . $order . Enum::SEPARATE . Enum::QUOTATION . $fieldTwo . Enum::QUOTATION . Enum::SEPARATE;
            throw new \Exception("Syntax Error : " . self::$_query , 1);
        }
        else
            //set statement WHERE
        {
            self::$_query .= Enum::_OR . Enum::SEPARATE . $fieldOne . Enum::SEPARATE . $order . Enum::SEPARATE . Enum::QUOTATION . $fieldTwo . Enum::QUOTATION . Enum::SEPARATE;
        }

        return self::this ();
    }

    /**
     * create BETWEEN statement
     *
     * @param  string $columnName | field name
     * @param  string $fieldOne
     * @param  string $fieldTwo
     *
     * @return Obj QueryBuilder
     */
    public static function between ($columnName , $fieldOne , $fieldTwo)
    {
        //if $_query has keyword WHERE
        if (strpos (self::$_query , Enum::WHERE) === FALSE)
            //set statement BETWEEN
        {
            self::$_query .= Enum::WHERE . Enum::SEPARATE . $columnName . Enum::SEPARATE . Enum::BETWEEN . Enum::SEPARATE . Enum::QUOTATION . $fieldOne . Enum::QUOTATION . Enum::_AND . Enum::SEPARATE . Enum::QUOTATION . $fieldTwo . Enum::QUOTATION . Enum::SEPARATE;
        }
        else
            //set statement BETWEEN
        {
            self::$_query .= Enum::_AND . Enum::SEPARATE . $columnName . Enum::SEPARATE . Enum::BETWEEN . Enum::SEPARATE . Enum::QUOTATION . $fieldOne . Enum::QUOTATION . Enum::_AND . Enum::SEPARATE . Enum::QUOTATION . $fieldTwo . Enum::QUOTATION . Enum::SEPARATE;
        }

        return self::this ();
    }

    /**
     * create BETWEEN OR statement
     *
     * @param  string $columnName | field name
     * @param  string $fieldOne
     * @param  string $fieldTwo
     *
     * @return Obj QueryBuilder
     * @throws \Exception
     */
    public static function betweenOr ($columnName , $fieldOne , $fieldTwo)
    {
        //if $_query has keyword WHERE
        if (strpos (self::$_query , Enum::WHERE) === FALSE)
        {
            //set statement BETWEEN
            self::$_query .= Enum::WHERE . Enum::SEPARATE . Enum::_OR . Enum::SEPARATE . $columnName . Enum::SEPARATE . Enum::BETWEEN . Enum::SEPARATE . Enum::QUOTATION . $fieldOne . Enum::QUOTATION . Enum::_AND . Enum::SEPARATE . Enum::QUOTATION . $fieldTwo . Enum::QUOTATION . Enum::SEPARATE;
            throw new \Exception("Syntax Error : " . self::$_query , 1);
        }
        else
            //set statement BETWEEN
        {
            self::$_query .= Enum::_OR . Enum::SEPARATE . $columnName . Enum::SEPARATE . Enum::BETWEEN . Enum::SEPARATE . Enum::QUOTATION . $fieldOne . Enum::QUOTATION . Enum::_AND . Enum::SEPARATE . Enum::QUOTATION . $fieldTwo . Enum::QUOTATION . Enum::SEPARATE;
        }

        return self::this ();
    }

    /**
     * create LIKE statement
     *
     * @param  string $columnName | field name
     * @param  string $pattern
     *
     * @return Obj QueryBuilder
     */
    public static function like ($columnName , $pattern)
    {
        //if $_query has keyword WHERE
        if (strpos (self::$_query , Enum::WHERE) === FALSE)
            //set statement LIKE
        {
            self::$_query .= Enum::WHERE . Enum::SEPARATE . $columnName . Enum::SEPARATE . Enum::LIKE . Enum::SEPARATE . Enum::QUOTATION . $pattern . Enum::QUOTATION . Enum::SEPARATE;
        }
        else
        {
            //set statement LIKE
            self::$_query .= Enum::_AND . Enum::SEPARATE . $columnName . Enum::SEPARATE . Enum::LIKE . Enum::SEPARATE . Enum::QUOTATION . $pattern . Enum::QUOTATION . Enum::SEPARATE;
        }

        return self::this ();
    }

    /**
     * create LIKE OR statement
     *
     * @param  string $columnName | field name
     * @param  string $pattern
     *
     * @return Obj QueryBuilder
     * @throws \Exception
     */
    public static function likeOr ($columnName , $pattern)
    {
        //if $_query has keyword WHERE
        if (strpos (self::$_query , Enum::WHERE) === FALSE)
        {
            //set statement LIKE
            self::$_query .= Enum::WHERE . Enum::SEPARATE . Enum::_OR . Enum::SEPARATE . $columnName . Enum::SEPARATE . Enum::LIKE . Enum::SEPARATE . Enum::QUOTATION . $pattern . Enum::QUOTATION . Enum::SEPARATE;
            throw new \Exception("Syntax Error : " . self::$_query , 1);
        }
        else
            //set statement LIKE
        {
            self::$_query .= Enum::_OR . Enum::SEPARATE . $columnName . Enum::SEPARATE . Enum::LIKE . Enum::SEPARATE . Enum::QUOTATION . $pattern . Enum::QUOTATION . Enum::SEPARATE;
        }

        return self::this ();
    }

    /**
     * create ORDER BY statement
     *
     * @param  string $columnsName
     * @param  string $sortType
     *
     * @return Obj QueryBuilder
     */
    public static function orderBy ($columnsName , $sortType = NULL)
    {
        //convert array columns name to string
        $str_columns = implode (',' , $columnsName);

        //set statement ORDER BY
        self::$_query .= Enum::ORDER . Enum::SEPARATE . $str_columns . Enum::SEPARATE . $sortType . Enum::SEPARATE;

        return self::this ();
    }

    /**
     * create INSERT statement
     *
     * @param  string $tableName
     * @param  string $fields
     * @param  string $values
     *
     * @return Obj QueryBuilder
     * @throws \Exception
     */
    public static function insert ($tableName , $fields , $values)
    {
        //if tableName is null or empty
        if ($tableName == '' && $tableName == NULL)
        {
            throw new \Exception("Choice tables" , 1);
        }

        //convert array fields ro string 
        $str_fields = implode (',' , $fields);

        //set quotation on values array   
        $str_values_itr = array();
        foreach ($values as $item) array_push ($str_values_itr , Enum::QUOTATION . $item . Enum::QUOTATION);

        //convert array values ro string 
        $str_valus = implode (',' , $str_values_itr);

        //set INSERT INTO statement
        self::$_query .= Enum::INSERT . Enum::SEPARATE . $tableName . Enum::SEPARATE . Enum::OPENBRACKET . $str_fields . Enum::CLOSEBRACKET . Enum::SEPARATE . Enum::VALUES . Enum::SEPARATE . Enum::OPENBRACKET . $str_valus . Enum::CLOSEBRACKET . Enum::SEPARATE;

        return self::this ();
    }

    /**
     * create UPDATE statement
     *
     * @param  string $tableName
     * @param  string $values
     *
     * @return Obj QueryBuilder
     * @throws \Exception
     */
    public static function update ($tableName , $values)
    {
        //if tableName is null or empty
        if ($tableName == '' && $tableName == NULL)
        {
            throw new \Exception("Choice tables" , 1);
        }

        $str     = '';
        $counter = 0;
        foreach ($values as $key => $value)
        {
            $str .= $key . Enum::SEPARATE . Enum::EQUAL . Enum::SEPARATE . $value . Enum::SEPARATE . ($counter == COUNT ($values) - 1 ? Enum::SEPARATE : Enum::COMMA . Enum::SEPARATE);
            $counter++;
        }

        //set UPDATE statement
        self::$_query = Enum::UPDATE . Enum::SEPARATE . Enum::SET . Enum::SEPARATE . $str . Enum::SEPARATE;

        return self::this ();
    }

    /**
     * create GROUP BY statement
     *
     * @param  string $fields
     *
     * @return Obj QueryBuilder
     * @throws \Exception
     */
    public static function groupBy ($fields)
    {
        //if fields is null or empty
        if ($fields == '' && $fields == NULL)
        {
            throw new \Exception("Choice fields" , 1);
        }

        //convert array fields ro string 
        $str_fields = implode (',' , $fields);

        //set GROUP BY statement
        self::$_query = Enum::GROUPBY . Enum::SEPARATE . $str_fields . Enum::SEPARATE;

        return self::this ();
    }

    /**
     * create HAVING statement
     *
     * @param  string $valueOne
     * @param  string $condition
     * @param  string $valueTwo
     *
     * @return Obj QueryBuilder
     */
    public static function having ($valueOne , $condition , $valueTwo)
    {
        self::$_query = Enum::HAVING . $valueOne . $condition . $valueTwo . Enum::SEPARATE;

        return self::this ();
    }

    /**
     * create SHOW TABLES statement
     *
     * @return  Obj QueryBuilder
     */
    public static function allTables ()
    {
        self::$_query = Enum::SHOW . Enum::SEPARATE . Enum::TABLES . Enum::SEPARATE;

        return self::this ();
    }

    /**
     * @return string
     */
    public function get ()
    {
        $temp         = self::$_query;
        self::$_query = '';

        return $temp;
    }
}

?>