bloatless/query-builder

A query builder for PDO MySQL.

v1.0.0 2022-01-02 18:24 UTC

Requires

• php: ^8.0
• ext-pdo: *

Requires (Dev)

• phpunit/phpunit: ^8.3

MIT

• Simon Samtleben

• php
• mysql
• pdo
• query builder
• bloatless

README

Bloatless Query Builder

A query builder for PDO MySQL and SQLite.

Installation

Using phar

Download the latest phar from the releases page 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

You can install the library using composer:

composer require bloatless/query-builder

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

• Query Builder
  • Connections
  • Factory
  • SELECT
    • A simple select
    • Table and column alias
    • Get specific columns
    • First row only
    • Single column as array
    • Counting rows
    • Joins
    • Group by
    • Order by
    • Having
    • Limit and Offset
    • Distinct
  • UPDATE
  • DELETE
  • WHERE
    • Simple where
    • Or where
    • Where in
    • Where not in
    • Or where in
    • Or where not in
    • Where between
    • Or where between
    • Where null
    • Where not null
    • Or where null
    • Or where not null
    • Where raw
    • Or where raw
  • INSERT
    • Single row
    • Multiple rows
    • Last insert id
  • RAW Queries
    • Raw select queries
    • Other raw queries
  • Reset
  • Security

Query Builder

This documentation explains the complete usage API of the Bloatless Query Builder.

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

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

A simple select

$rows = $db->makeSelect()->from('customers')->get();

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

$rows = $db->makeSelect()
    ->cols(['customer_id', 'firstname', 'lastname'])
    ->from('customers')
    ->get();

First row only

$row = $db->makeSelect()
    ->from('customers')
    ->whereEquals('customer_id', 42)
    ->first();

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

$rowCount = $db->makeSelect()
    ->from('customers')
    ->count();

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

$rows = $db->makeSelect()
    ->from('orders')
    ->groupBy('customer_id')
    ->get();

Order by

$rows = $db->makeSelect()
    ->from('customers')
    ->orderBy('firstname', 'desc')
    ->get();

Having

$rows = $db->makeSelect()
    ->from('orders')
    ->having('amount', '>', 10)
    ->orHaving('cart_items', '>' 5)
    ->get();

Limit and Offset

$rows = $db->makeSelect()
    ->from('orders')
    ->limit(10)
    ->offset(20)
    ->get();

Distinct

$rows = $db->makeSelect()
    ->distinct()
    ->from('orders')
    ->get();

UPDATE

$rows = $db->makeUpdate()
    ->table('customers')
    ->whereEquals('customer_id', 42)
    ->update([
        'firstname' => 'Homer'
    ]);

DELETE

$rows = $db->makeDelete()
    ->from('customers')
    ->whereEquals('customer_id', 42)
    ->delete();

WHERE

You can use various where clauses on all select, update and delete queries:

Simple where

$rows = $db->makeSelect()
    ->from('customers')
    ->where('customer_id', '=', 42)
    ->where('customer_id', '>', 10)
    ->whereEquals('customer_id', 42)
    ->get();

Or where

->orWhere('customer_id', '>', 5)

Where in

->whereIn('customer_id', [1,2,3])

Where not in

->whereNotIn('customer_id', [1,2,3])

Or where in

->orWhereIn('customer_id', [1,2,3])

Or where not in

->orWhereNotIn('customer_id', [1,2,3])

Where between

->whereBetween('customer_id', 5, 10)

Or where between

->orWhereBetween('customer_id', 5, 10)

Where null

->whereNull('customer_id')

Where not null

->whereNotNull('customer_id')

Or where null

->orWhereNull('customer_id')

Or where not null

->orWhereNotNull('customer_id')

Where raw

->whereRaw('TIMESTAMPDIFF(HOUR, `time`, NOW()) <= 24')

->whereRaw('customer_id = :id', ['id' => 10])

Or where raw

->orWhereRaw('TIMESTAMPDIFF(HOUR, `time`, NOW()) <= 24')

INSERT

Single row

$customerId = $db->makeInsert()
    ->into('customers')
    ->row([
        'firstname' => 'Homer',
        'lastname' => 'Simpson',
    ]);

When inserting a single row, the auto-increment value of the newly added row will be returned.

Multiple rows

You can insert multiple rows at once using the rows method:

$db->makeInsert()
    ->into('customers')
    ->rows([
        [
            'firstname' => 'Homer',
            'lastname' => 'Simpson',
        ],
        [
            'firstname' => 'Marge',
            'lastname' => 'Simpson',
        ],
    ]);

Last insert id

In case you need to fetch the id if the last insert manually you can use the getLastInsertId method:

$id = $insertQueryBuilder->getLastInsertId();

RAW Queries

There will always be some kind of queries you can not build using the methods of a query builder. In those cases you can utilize the RawQueryBuilder which allows you to execute raw queries to the database.

Raw select queries

$rows = $db->makeRaw()
    ->prepare("SELECT * FROM `orders` WHERE `customer_id` = :id", [
        'id' => 42,
    ])
    ->get();

Other raw queries

$db->makeRaw()
    ->prepare("UPDATE `customers` SET `firstname` = :name WHERE `customer_id` = :id", [
        'name' => 'Homer',
        'id' => 42,
    ])
    ->run();

Reset

All query builders have a reset method. This method can be used to clear all previously set values without the need of creating a new QueryBuilder object.

$builder = $db->makeSelect()
    ->from('customers')
    ->whereEquals('customer_id', 42);

$builder->reset();

...

Security

All query builders internally use PDO parameter binding to reduce the risk of injection attacks as much as possible. Additionally, table names as well as field names are quoted - so you don't have to worry about that. This works on simple table names or when using aliases. Nevertheless, you should always try to avoid using user input within sql statements!

License

MIT