mikuso/epdo

An extension to PHP's PDO library, for a better quality of life. Designed specifically for use with MySQL/MariaDB.

EPDO

An extension to PDO, for a better quality of life. Designed specifically for use with MySQL/MariaDB.

This library introduces 2 new classes:

• EPDO - an extended version of PDO. Can work as a drop-in replacement.
• EPDOResult - a class representing a result set

In addition, all instances of EPDO have some sensible presets:

• PDO::ATTR_ERRMODE = PDO::ERRMODE_EXCEPTION
  All errors throw exceptions.

• PDO::ATTR_EMULATE_PREPARES = false
  Allows prepare() to throw Exceptions, and allows non-string values to be returned in result sets.

Getting started

require_once 'EPDO.php';

$db = new EPDO(/* dsn */, /* user */, /* pass */);

$results = $db(/* sql */, /* ...params */);
// $results is an EPDOResult object

// iterate over the results
foreach ($results as $row) {
    // do something with $row
}

EPDO

$db( string $sql [, array ...$params ] ) : EPDOResult

Prepare, bind, execute and fetch the results of an SQL query. All result rows will be returned as stdClass objects.

See [Escaping Queries] for examples.

$db->transaction( callable $callback )

An easy way to write transactions.

All queries executed within the callback function will either:

• all succeed
• all fail

Eliminates the possibility of introducing inconsistent states into your data.

Returns the return value of the callback.

Example:

$db->transaction(function($db){
    $r1 = $db("INSERT INTO t1 SET ?", ["a" => "bc"]);

    $r2 = $db("SELECT a FROM t1 WHERE id = ?", $r1->lastId);

    echo $r2->first->a; // "bc"

    throw new Exception("oops");
    // Exception is thrown, so the transaction is rolled back
});

// The transaction failed, so no data has been modified
$db("SELECT a FROM t1 WHERE a = ?", "bc")->first; // NULL

EPDOResult

EPDOResult ($results) is an iterable just like any ordinary array, but has a few extra properties...

$results->first

Get the first result of the result set.
If no results returned, will return NULL instead.

$results->all

Get a raw array of results.
You shouldn't normally need this, as the EPDOResult object is iterable just like an array.

$results->lastId

Get the last Insert ID / Auto-Increment value generated by the query.
Works similarly to $db->lastInsertId() except that $result->lastId won't ever change.

$results->affectedRows

Get the number of rows affected by the last INSERT/UPDATE - or the number of rows returned in the result set.

$results->count

Get the number of rows returned in the result set.

$results->value

Get the value of the first column of the first row of results.

echo $results

Reveals some useful debug information about the result object.

Example:

EPDOResult:
----------------
Results        : 0
Affected Rows  : 2
Last Insert Id : 72
Query          ->
    INSERT INTO t1 (a, b) VALUES (?, ?), (?, ?)

First Result   ->
    NULL

Escaping Queries

It is not necessary to escape any user input with this library - you simply need to pass the user input as parameters, and use ? (question mark) placeholders in your SQL string.

The simplest query does not require any parameters or placeholders:

// select some rows
$db("
    SELECT *
    FROM t1
    LIMIT 5
");

Here's how you can pass a single scalar parameter with a query:

// select rows where `t1.id` > 5
$db("
    SELECT *
    FROM t1
    WHERE id > ?
", 5);

Here's how you to pass multiple scalar parameters with a query:

// select rows where `t1.id` is between 5 and 10 (inclusive)
$db("
    SELECT *
    FROM t1
    WHERE id >= ?
    AND id =< ?
", 5, 10);

You can also pass indexed arrays as parameters:

// select rows where `t1.id` is either 1,2 or 3
$db("
    SELECT *
    FROM t1
    WHERE id IN (?)
", [1,2,3]);

// equivalent to...
$db("
    SELECT *
    FROM t1
    WHERE id IN (?,?,?)
", 1, 2, 3);

To perform a batch INSERT, you can nest multiple arrays:

$db("
    INSERT INTO t1 (a, b) VALUES ?
", [
    [1,2],
    [3,4],
    [5,6]
]);

// equivalent to...
$db("
    INSERT INTO t1 (a, b) VALUES (?,?),(?,?),(?,?)
", 1, 2, 3, 4, 5, 6);

To perform an INSERT INTO ... SET, you can pass objects or associative arrays:

$db("
    INSERT INTO t1 SET ?
", [
    "a" => 1,
    "b" => 2
]);

// equivalent to...
$db("
    INSERT INTO t1 SET
        `a` = ?,
        `b` = ?
", 1, 2);

And of course, you can mix and match scalars, objects and indexed arrays:

// update all rows where `t1.id` is either 1, 2 or 3 - and `a` > 5, setting `a` = 2, `b` = 3
$db("
    UPDATE t1
    SET ?
    WHERE id IN ?
    AND a > ?
",
    [ "a" => 2, "b" => 3],
    [ 1, 2, 3 ],
    5
);

License

Copyright (c) 2017 Gareth Hughes

Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.