php-sql-query-builder-master.zip_php sql

SQL Query Builder ================= [](https://travis-ci.org/nilportugues/php-sql-query-builder) [](https://coveralls.io/r/nilportugues/sql-query-builder) [](https://scrutinizer-ci.com/g/nilportugues/sql-query-builder/?branch=master) [](https://insight.sensiolabs.com/projects/89ec1003-4227-43a2-8432-67a9fc2d3ba3) [](https://packagist.org/packages/nilportugues/sql-query-builder) [](https://packagist.org/packages/nilportugues/sql-query-builder) [](https://packagist.org/packages/nilportugues/sql-query-builder) [](https://paypal.me/nilportugues) An elegant lightweight and efficient SQL Query Builder with fluid interface SQL syntax supporting bindings and complicated query generation. **Works without establishing a connection to the database.** <a name="index_block"></a> * [1. Installation](#block1) * [2. The Builder](#block2) * [2.1. Generic Builder](#block2.1) * [2.2. MySQL Builder](#block2.2) * [2.3. Human Readable Output](#block2.3) * [3. Building Queries](#block3) * [3.1. SELECT Statement](#block3.1) * [3.1.1. Basic SELECT statement](#block3.1.1) * [3.1.2. Aliased SELECT statement](#block3.1.2) * [3.1.3. SELECT with WHERE statement](#block3.1.3) * [3.1.4. Complex WHERE conditions](#block3.1.4) * [3.1.5. JOIN & LEFT/RIGHT/INNER/CROSS JOIN SELECT statements](#block3.1.5) * [3.1.6. COUNT rows](#block3.1.6) * [3.2. INSERT Statement](#block3.2) * [3.2.1. Basic INSERT statement](#block3.2.1) * [3.3. UPDATE Statement](#block3.3) * [3.3.1. Basic UPDATE statement](#block3.3.1) * [3.3.2. Elaborated UPDATE statement](#block3.3.2) * [3.4. DELETE Statement](#block3.4) * [3.4.1. Empty table with DELETE statement](#block3.4.1) * [3.4.2. Basic DELETE statement](#block3.4.2) * [3.4.3. Elaborated DELETE statement](#block3.4.3) * [3.5. INTERSECT Statement](#block3.5) * [3.6. MINUS Statement](#block3.6) * [3.7. UNION Statement](#block3.7) * [3.8. UNION ALL Statement](#block3.8) * [4. Advanced Quering](#block4) * [4.1. Filtering using WHERE](#block4.1) * [4.1.1. Changing WHERE logical operator](#block4.2) * [4.1.2. Writing complicated WHERE conditions](#block4.2) * [4.3. Grouping with GROUP BY and HAVING](#block4.3) * [4.3.1 Available HAVING operators](#block4.3.1) * [4.4. Changing HAVING logical operator](#block4.4) * [4.5. Columns as SELECT statements](#block4.5) * [4.6. Columns being Values](#block4.6) * [4.7. Columns using FUNCTIONS](#block4.7) * [5. Commenting queries](#block5) * [6. Quality Code](#block6) * [7. Author](#block7) * [8. License](#block8) <a name="block1"></a> ## 1. Installation [↑](#index_block) The recommended way to install the SQL Query Builder is through [Composer](http://getcomposer.org). Run the following command to install it: ```sh php composer.phar require nilportugues/sql-query-builder ``` <a name="block2"></a> ## 2. The Builder [↑](#index_block) The SQL Query Builder allows to generate complex SQL queries standard using the `SQL-2003` dialect (default) and the `MySQL` dialect, that extends the `SQL-2003` dialect. <a name="block2.1"></a> ### 2.1. Generic Builder [↑](#index_block) The Generic Query Builder is the default builder for this class and writes standard SQL-2003. **All column aliases are escaped using the `'` sign by default.** #### Usage: ```php <?php use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select()->setTable('user'); echo $builder->write($query); ``` #### Output: ```sql SELECT user.* FROM user ``` <a name="block2.2"></a> ### 2.2. MySQL Builder [↑](#index_block) The MySQL Query Builder has its own class, that inherits from the SQL-2003 builder. All columns will be wrapped with the tilde **`** sign. **All table and column aliases are escaped using the tilde sign by default.** #### Usage: ```php <?php use NilPortugues\Sql\QueryBuilder\Builder\MySqlBuilder; $builder = new MySqlBuilder(); $query = $builder->select()->setTable('user'); echo $builder->write($query); ``` #### Output: ```sql SELECT user.* FROM `user` ``` <a name="block2.3"></a> #### 2.3. Human Readable Output [↑](#index_block) Both Generic and MySQL Query Builder can write complex SQL queries. Every developer out there needs at some point revising the output of a complicated query, the SQL Query Builder includes a human-friendly output method, and therefore the `writeFormatted` method is there to aid the developer when need. Keep in mind `writeFormatted` is to be avoided at all cost in production mode as it adds unneeded overhead due to parsing and re-formatting of the generated statement. #### Usage: ```php <?php use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select()->setTable('user'); echo $builder->writeFormatted($query); ``` #### Output: ```sql SELECT user.* FROM user ``` More complicated examples can be found in the documentation. <a name="block3"></a> ## 3. Building Queries [↑](#index_block) <a name="block3.1"></a> ### 3.1. SELECT Statement [↑](#index_block) <a name="block3.1.1"></a> #### 3.1.1. Basic SELECT statement [↑](#index_block) #### Usage: ```php <?php use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setColumns(['user_id','name','email']); echo $builder->write($query); ``` #### Output: ```sql SELECT user.user_id, user.name, user.email FROM user ``` <a name="block3.1.2"></a> #### 3.1.2. Aliased SELECT statement [↑](#index_block) #### Usage: ```php <?php use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setColumns(['userId' => 'user_id', 'username' => 'name', 'email' => 'email']); echo $builder->write($query); ``` #### Output: ```sql SELECT user.user_id AS 'userId', user.name AS 'username', user.email AS 'email' FROM user ``` <a name="block3.1.3"></a> #### 3.1.3. SELECT with WHERE statement [↑](#index_block) Default logical operator for filtering using `WHERE` conditions is `AND`. #### Usage: ```php <?php use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->setColumns([ 'userId' => 'user_id', 'username' => 'name', 'email' => 'email' ]) ->where() ->greaterThan('user_id', 5) ->notLike('username', 'John'); echo $builder->writeFormatted($query); ``` #### Output: ```sql SELECT user.user_id AS 'userId', user.name AS 'username', user.email AS 'email' FROM user WHERE (user.user_id < :v1) AND (user.username NOT LIKE :v2) ``` <a name="block3.1.4"></a> #### 3.1.4. Complex WHERE conditions [↑](#index_block) #### Usage: ```php <?php use NilPortugues\Sql\QueryBuilder\Builder\GenericBuilder; $builder = new GenericBuilder(); $query = $builder->select() ->setTable('user') ->where() ->equal