Harp Query

A query builder library, extending PDO to allow writing queries in object oriented style. Intelligently manages passing parameters to PDO's execute.

Quick usage example

use Harp\Query\DB;

DB::setConfig([
    'dsn' => 'mysql:dbname=test-db;host=127.0.0.1',
    'username' => 'root',
]);

$query = DB::select()
    ->from('users')
    ->where('seller', true)
    ->join('profiles', ['profiles.user_id' => 'users.id'])
    ->limit(10);

foreach ($query->execute() as $row) {
    var_dump($row);
}

echo "Executed:\n";
echo $query->humanize();

The query execute() method will generate a PDOStatement object that can be iterated over. All the variables are passed as position parameters ("seller = ?") so are properly escaped by the PDO driver.

Why?

PHP has quite a lot of excellent query builder classes already. For example Paris/Idiom, Kohana Query Builder etc. Why have another one? Here is my elevator pitch:

• Integrate with PDO - since it already has quite a lot of support for different DB drivers, harp-orm/query can use all that wealth of functionality out of the box. The base DB class extends PDO class, and the select result is actually PDOStatement object. And all of this already has great docs
• Use PSR coding standards and Symfony naming conventions for more familiar and readable codebase.
• Use PSR logging to integrate with any compatible logger.
• Support more rarely used SQL constructs, e.g. INSERT IGNORE, UNION, UPDATE JOIN etc.
• Precise methods to express intent more clearly e.g. methods like where, whereIn, whereLike allow you to write exactly what you intend, and not expect guesswork from the library. This can provide more error-free codebase.
• Fully covered with DockBlocks so static code analysis on packages built on top of it can be more accurate
• Full test coverage

Connecting to the database

Connecting to the database is a 2 step process. First you need to define the configuration for the database, later you will use that configuration to create a connection object (DB). This allows to lazy-load the connection object.

DB::setConfig([
    'dsn' => 'mysql:dbname=test-db;host=127.0.0.1',
    'username' => 'root',
]);

// ...
$db = DB::get();

After that you will use the database connection object ($db). To execute queries and retrieve data.

The available configuration options are:

• dsn
• username
• password
• driver_options

They go directly to the PDO construct method. Here's an example:

DB::setConfig([
    'dsn' => 'mysql:dbname=test-atlas;host=127.0.0.1',
    'username' => 'root',
    'password' => 'qkum4hctpwh',
    'driver_options' => [
        PDO::ATTR\_DEFAULT\_FETCH\_MODE => PDO::FETCH\_BOTH,
    ],
]);

You can connect to different databases by setting alternative configurations. The second argument of setConfig allows you to "name" a configuration. After that you can get the connection with this configuration by calling DB::get($name)

DB::setConfig(array(
    'dsn' => 'mysql:dbname=test-db;host=127.0.0.1',
    'username' => 'alternative',
), 'alternative-db');

// ...
$db = DB::get('alternative-db');

Retrieving data (Select)

Tretrieve data from the database, use Select class.

An example select:

use Harp\Query\DB;

$select = DB::select()
    ->from('users')
    ->column('users.*')
    ->where('username', 'Tom')
    ->whereIn('type', ['big', 'small'])
    ->limit(10)
    ->order('created_at', 'DESC');

$result = $select->execute();

foreach ($result as $row) {
    var_dump($row);
}

Inserting data (Insert)

Tretrieve insert new data to the database, use Insert class.

An example insert:

use Harp\Query\DB;

$insert = DB::insert()
    ->into('users')
    ->set([
        'name' => 'Tom',
        'family_name' => 'Soyer'
    ]);

$insert->execute();

echo $insert->getLastInsertId();

Deleting data (Delete)

Tretrieve delete data from the database, use Delete class.

An example delete:

use Harp\Query\DB;

$delete = DB::delete()
    ->from('users')
    ->where('score', 10)
    ->limit(10);

$delete->execute();

Updating data (Update)

Tretrieve update data in the database, use Update class.

An example update:

use Harp\Query\DB;

$update = DB::update()
    ->table('users')
    ->set(['name' => 'New Name'])
    ->where('id', 10);

$update->execute();

Detailed docs

• Select
• Insert
• Delete
• Update
• Union

License

Copyright (c) 2014, Clippings Ltd. Developed by Ivan Kerin as part of clippings.com

Under BSD-3-Clause license, read LICENSE file.