Skip to content
A Query Builder for PDO MySQL
PHP TSQL
Branch: master
Clone or download
Fetching latest commit…
Cannot retrieve the latest commit at this time.
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
src
tests
.gitignore
LICENSE
README.md
composer.json
phpunit.xml.dist

README.md

Bloatless Query Builder

A query builder for PDO MySQL.

Installation

You can install the library using composer:

php composer.phar require bloatless/endocore-query-builder

Usage

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
            ],
            
            // 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\Endocore\Components\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')

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 user 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

You can’t perform that action at this time.