PHPackages bloatless/query-builder - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. bloatless/query-builder ActiveLibrary[Database & ORM](/categories/database) bloatless/query-builder ======================= A query builder for PDO MySQL. v1.0.0(4y ago)420MITPHPPHP ^8.0 Since Nov 17Pushed 4y ago1 watchersCompare [ Source](https://github.com/bloatless/query-builder)[ Packagist](https://packagist.org/packages/bloatless/query-builder)[ Docs](https://bloatless.org)[ RSS](/packages/bloatless-query-builder/feed)WikiDiscussions master Synced 1mo ago READMEChangelog (2)Dependencies (1)Versions (4)Used By (0) [![](https://camo.githubusercontent.com/c32f73a2d2e53972aa4187a1ffa7e720e107f2a72a7a1f1e4e4a8737eab4338e/68747470733a2f2f626c6f61746c6573732e6f72672f696d672f6c6f676f2e737667)](https://camo.githubusercontent.com/c32f73a2d2e53972aa4187a1ffa7e720e107f2a72a7a1f1e4e4a8737eab4338e/68747470733a2f2f626c6f61746c6573732e6f72672f696d672f6c6f676f2e737667) Bloatless Query Builder ======================= [](#bloatless-query-builder) A query builder for PDO MySQL and SQLite. Installation ------------ [](#installation) ### Using phar [](#using-phar) Download the latest phar from the [releases page](https://github.com/bloatless/query-builder/releases) and include/use the lib in your project like any other PHP class: ``` require_once '/path/to/query-builder.phar'; $factory = new \Bloatless\QueryBuilder\QueryBuilderFactory($myConfig); $queryBuilder = $factory->make(); ``` ### Using composer [](#using-composer) You can install the library using composer: ``` composer require bloatless/query-builder ``` ### Manually [](#manually) Clone or download the files from GitHub into your local project. You can than include/use the library within your project: ``` require_once '/path/to/src/QueryBuilderFactory.php'; $factory = new \Bloatless\QueryBuilder\QueryBuilderFactory($myConfig); $queryBuilder = $factory->make(); ``` Usage ----- [](#usage) - [Query Builder](#query-builder) - [Connections](#connections) - [Factory](#factory) - [SELECT](#select) - [A simple select](#a-simple-select) - [Table and column alias](#table-and-column-alias) - [Get specific columns](#get-specific-columns) - [First row only](#first-row-only) - [Single column as array](#single-column-as-array) - [Counting rows](#counting-rows) - [Joins](#joins) - [Group by](#group-by) - [Order by](#order-by) - [Having](#having) - [Limit and Offset](#limit-and-offset) - [Distinct](#distinct) - [UPDATE](#update) - [DELETE](#delete) - [WHERE](#where) - [Simple where](#simple-where) - [Or where](#or-where) - [Where in](#where-in) - [Where not in](#where-not-in) - [Or where in](#or-where-in) - [Or where not in](#or-where-not-in) - [Where between](#where-between) - [Or where between](#or-where-between) - [Where null](#where-null) - [Where not null](#where-not-null) - [Or where null](#or-where-null) - [Or where not null](#or-where-not-null) - [Where raw](#where-raw) - [Or where raw](#or-where-raw) - [INSERT](#insert) - [Single row](#single-row) - [Multiple rows](#multiple-rows) - [Last insert id](#last-insert-id) - [RAW Queries](#raw-queries) - [Raw select queries](#raw-select-queries) - [Other raw queries](#other-raw-queries) - [Reset](#reset) - [Security](#security) ### Query Builder [](#query-builder) This documentation explains the complete usage API of the Bloatless Query Builder. #### Connections [](#connections) You can define multiple database connections in your projects `config.php` file. ``` $config = [ 'db' => [ 'connections' => [ 'db1' => [ 'driver' => 'mysql', 'host' => 'localhost', 'database' => 'db1', 'username' => 'root', 'password' => 'your-password', 'charset' => 'utf8', // Optional 'timezone' => 'Europe/Berlin', // Optional ], 'db2' => [ 'driver' => 'sqlite', 'database' => '/path/to/sqlite.db', ] // add additional connections here... ], 'default_connection' => 'db1', ] ]; ``` #### Factory [](#factory) The QueryBuilder factory needs to be initialized using a config array providing the connection credentials: ``` $db = new \Bloatless\QueryBuilder\QueryBuilder\Factory($config['db']); ``` Once initialized the factory can be used to provide query-builder objects for various database operations: ``` $selectQueryBuilder = $db->makeSelect(); $updateQueryBuilder = $db->makeUpdate(); $deleteQueryBuilder = $db->makeDelete(); $insertQueryBuilder = $db->makeInsert(); $rawQueryBuilder = $db->makeRaw(); ``` With no arguments provided the default database connection is used. If you want to use a different connection you can pass the connection name as an argument. ``` $updateQueryBuilder = $db->makeUpdate('db2'); ``` #### SELECT [](#select) ##### A simple select [](#a-simple-select) ``` $rows = $db->makeSelect()->from('customers')->get(); ``` ##### Table and column alias [](#table-and-column-alias) Aliases can be used on table names as well as on column names. ``` $rows = $db->makeSelect() ->cols(['customer_id AS id', 'firstname', 'lastname']) ->from('customers AS c') ->get(); ``` ##### Get specific columns [](#get-specific-columns) ``` $rows = $db->makeSelect() ->cols(['customer_id', 'firstname', 'lastname']) ->from('customers') ->get(); ``` ##### First row only [](#first-row-only) ``` $row = $db->makeSelect() ->from('customers') ->whereEquals('customer_id', 42) ->first(); ``` ##### Single column as array [](#single-column-as-array) ``` $names = $db->makeSelect() ->from('customers') ->pluck('firstname'); ``` Will fetch an array containing all first names of the `customers` table. You can specify a second column which will be used for the keys of the array: ``` $names = $db->makeSelect() ->from('customers') ->pluck('firstname', 'customer_id'); ``` Will fetch an array of all first names using the `customer_id` as array key. ##### Counting rows [](#counting-rows) ``` $rowCount = $db->makeSelect() ->from('customers') ->count(); ``` ##### Joins [](#joins) You can join tables using the `join`, `leftJoin` or `rightJoin` methods. You can of course join multiple tables. ``` $rows = $db->makeSelect() ->from('customers') ->join('orders', 'customers.customer_id', '=', 'orders.customer_id') ->get(); ``` ##### Group by [](#group-by) ``` $rows = $db->makeSelect() ->from('orders') ->groupBy('customer_id') ->get(); ``` ##### Order by [](#order-by) ``` $rows = $db->makeSelect() ->from('customers') ->orderBy('firstname', 'desc') ->get(); ``` ##### Having [](#having) ``` $rows = $db->makeSelect() ->from('orders') ->having('amount', '>', 10) ->orHaving('cart_items', '>' 5) ->get(); ``` ##### Limit and Offset [](#limit-and-offset) ``` $rows = $db->makeSelect() ->from('orders') ->limit(10) ->offset(20) ->get(); ``` ##### Distinct [](#distinct) ``` $rows = $db->makeSelect() ->distinct() ->from('orders') ->get(); ``` #### UPDATE [](#update) ``` $rows = $db->makeUpdate() ->table('customers') ->whereEquals('customer_id', 42) ->update([ 'firstname' => 'Homer' ]); ``` #### DELETE [](#delete) ``` $rows = $db->makeDelete() ->from('customers') ->whereEquals('customer_id', 42) ->delete(); ``` #### WHERE [](#where) You can use various where clauses on all `select`, `update` and `delete` queries: ##### Simple where [](#simple-where) ``` $rows = $db->makeSelect() ->from('customers') ->where('customer_id', '=', 42) ->where('customer_id', '>', 10) ->whereEquals('customer_id', 42) ->get(); ``` ##### Or where [](#or-where) ``` ->orWhere('customer_id', '>', 5) ``` ##### Where in [](#where-in) ``` ->whereIn('customer_id', [1,2,3]) ``` ##### Where not in [](#where-not-in) ``` ->whereNotIn('customer_id', [1,2,3]) ``` ##### Or where in [](#or-where-in) ``` ->orWhereIn('customer_id', [1,2,3]) ``` ##### Or where not in [](#or-where-not-in) ``` ->orWhereNotIn('customer_id', [1,2,3]) ``` ##### Where between [](#where-between) ``` ->whereBetween('customer_id', 5, 10) ``` ##### Or where between [](#or-where-between) ``` ->orWhereBetween('customer_id', 5, 10) ``` ##### Where null [](#where-null) ``` ->whereNull('customer_id') ``` ##### Where not null [](#where-not-null) ``` ->whereNotNull('customer_id') ``` ##### Or where null [](#or-where-null) ``` ->orWhereNull('customer_id') ``` ##### Or where not null [](#or-where-not-null) ``` ->orWhereNotNull('customer_id') ``` ##### Where raw [](#where-raw) ``` ->whereRaw('TIMESTAMPDIFF(HOUR, `time`, NOW())