phpgt/database

Database API organisation.

, _(*1)

Database API organisation.

Encloses your application's database scripts within a simple and standardised interface, separating database access from your application logic., _(*2)

The first parameter to any database functions is always the query name, which represents a query file on disk - either a raw SQL file or a PHP representation of a query using SqlBuilder., _(*3)

, _(*4)

Example usage

This library organises SQL access through a consistent API. To execute an example query located at src/query/user/getById.sql, the following pattern is used:, _(*5)

$userRow = $db->fetch("user/getById", 105);

Examples of CRUD operations:, _(*6)

// "fetchAll" method returns an iterable ResultSet of Row objects.
$bookResultSet = $db->fetchAll("shopitem/getItemsInCategory", "books");

foreach($bookResultSet as $bookRow) {
    echo "Book title: ", $bookRow->getString("title"), PHP_EOL;
    echo "Book price: £", ($bookRow->getFloat("price") + $bookRow->getFloat("vat")), PHP_EOL;

    if($bookRow->offerEnds) {
        echo "Item on offer until: ", $bookRow->getDateTime("offerEnds")->format("dS M Y");
    }
}

// "Create" method always returns the last inserted ID:
$newCustomerId = $db->create("customer/new", [
    "first_name" => "Marissa",
    "last_name" => "Mayer",
    "dob" => new DateTime("1975-05-30"),
]);

// "Update" or "delete" methods always return the number of affected rows:
$numberOfItemsAffected = $db->update("shop/item/increasePrice", [
    "percent" => 12.5,
    "max_increase" => 20.00,
]);

$numberOfDeletedReviews = $db->delete(
    "remove/deleteOlderThan",
    new DateTime("-6 months")
);

// Individual type-safe fields can be pulled from queries that return only one column:
$userName = $db->fetchString("user/getUsernameById", 105);

Features at a glance

• Automatic database migrations
• Encapsulation of queries using QueryCollections
• Bind parameters by name or sequentially
• Fully configurable
• Type safe getters