01-Features.md

Features

Connection

While using plain PDO you have to do formalities like assembling the platform-dependent DSN string and enabling exceptions throwing on statement failures.

With ipl\Sql\Connection you just do your thing straightforward:

use ipl\Sql\Connection;
use PDO;

$connection = new Connection([                                   // (1)
    'db'         => 'mysql',                                     // (2)
    'host'       => '192.0.2.42',                                // (3)
    'port'       => '3306',                                      // (4)
    'dbname'     => 'customers',                                 // (5)
    'username'   => 'jdoe',                                      // (6)
    'password'   => '123456',                                    // (7)
    'charset'    => 'utf8',                                      // (8)
    'attributes' => [                                   // optional
        PDO::MYSQL_ATTR_SSL_CA   => '/etc/myapp/mysql/ca.pem',   // (9)
        PDO::MYSQL_ATTR_SSL_CERT => '/etc/myapp/mysql/cert.pem', // (10)
        PDO::MYSQL_ATTR_SSL_KEY  => '/etc/myapp/mysql/key.pem'   // (11)
    ]
]);

$connection->connect();                                 // optional (12)

var_dump($connection->exec(
    'SELECT * FROM customer WHERE id = ?;', [42]                 // (13)
)->fetchRow());

$connection->disconnect();                              // optional (14)

Build (1) and initialize (12) a secure (9-11) connection to a database (2-5) providing authentication credentials (6-7). Use UTF-8 as charset (8), fetch one customer (13) and finally disconnect (14).

The authentication credentials (6-7) may not be necessary. The SSL options (9-11) are only supported by the mysql database driver (2). Explicit initialization (12) and disconnection (14) are always optional.

$connection->exec() returns a PDOStatement - see its documentation for details.

Queries

Almost hardcoded queries like the one in the above example are as easy as 1-2-3. But more complex and dynamic queries either must be assembled via a lot of string concatenations, implodes and conditional statements - or:

use ipl\Sql\Delete;
use ipl\Sql\Insert;
use ipl\Sql\Select;
use ipl\Sql\Update;

$connection->prepexec(
    (new Insert())
        ->into('customer')
        ->values([
            'id'   => 42,
            'name' => 'John Deo'
        ])
);

$connection->prepexec(
    (new Select())
        ->columns(['name'])
        ->from('customer')
        ->where(['id = ?' => 42])
)->fetchAll();

$connection->prepexec(
    (new Update())
        ->table('customer')
        ->set(['name' => 'John Doe'])
        ->where(['id = ?' => 42])
);

$connection->prepexec(
    (new Delete())
        ->from('customer')
        ->where(['id = ?' => 42])
);

Insert

Insert data into a table, provided either explicitly ...

(new Insert())
    ->into('customer')
    ->values([
        'id'   => 42,
        'name' => 'John Deo'
    ])

INSERT INTO customer (id,name) VALUES(42,'John Deo')

... or by a select query:

(new Insert())
    ->into('customer')
    ->columns(['id', 'name'])
    ->select(
        (new Select())
            ->columns(['id', 'name'])
            ->from('temp_customer')
    )

INSERT INTO customer (id,name) SELECT id, name FROM temp_customer

Select

Select the name of one customer:

(new Select())
    ->columns(['name'])
    ->from('customer')
    ->where(['id = ?' => 42])

SELECT name FROM customer WHERE id = 42

Select the ID, the name and the amount of resolved orders (3, 5, 7) of all customers (4) whose name contains "Doe" (6) and with at least 42 orders (8). Order the data by amount of orders and customer name (9), skip the first 75 rows (10) and limit to 25 rows (11):

(new Select())                                                     // (1)
    ->distinct()                                                   // (2)
    ->columns(['c.id', 'c.name', 'orders' => 'COUNT(o.customer)']) // (3)
    ->from('customer c')                                           // (4)
    ->joinLeft(                                                    // (5.1)
        'order o',                                                 // (5.2)
        ['o.customer = c.id', 'o.state = ?' => 'resolved']         // (5.3)
    )                                                              // (5.4)
    ->where(['c.name LIKE ?' => '%Doe%'])                          // (6)
    ->groupBy(['c.id'])                                            // (7)
    ->having(['COUNT(o.customer) >= ?' => 42])                     // (8)
    ->orderBy(['COUNT(o.customer)', 'c.name'])                     // (9)
    ->offset(75)                                                   // (10)
    ->limit(25)                                                    // (11)

SELECT DISTINCT c.id, c.name, COUNT(o.customer) AS orders 
FROM customer c 
LEFT JOIN order o ON o.customer = c.id AND o.state = 'resolved'
WHERE c.name LIKE '%Doe%'
GROUP BY c.id 
HAVING COUNT(o.customer) >= 42
ORDER BY COUNT(o.customer), c.name 
LIMIT 25 
OFFSET 75

Update

Update specific rows of a table ...

(new Update())
    ->table('customer')
    ->set(['name' => 'John Doe'])
    ->where(['id = ?' => 42])

UPDATE customer SET name = 'John Doe' WHERE id = 42

... or all of them:

(new Update())
    ->table('customer')
    ->set(['name' => 'John Doe'])

UPDATE customer SET name = 'John Doe'

Delete

Delete specific rows from a table ...

(new Delete())
    ->from('customer')
    ->where(['id = ?' => 42])

DELETE FROM customer WHERE id = 42

... or all of them:

(new Delete())
    ->from('customer')

DELETE FROM customer