Querying

Under the record classes and the query builder is an adapter that runs SQL and hands back arrays. Reach for it directly for a reporting query, a bulk statement, or anything the ORM would get in the way of.

Running a Raw Query#

select(), insert(), update() and delete() each take a SQL string and run it. select() returns the rows as an array of associative arrays; the other three return the number of affected rows.

Every sample on this page reaches the adapter with Record::getDb(), which hands back the connection a table class is bound to — the short way to get at one opened at bootstrap. Connecting & Adapters covers opening and registering it.

use Pop\Db\Record;

$db = Record::getDb();

$users = $db->select('SELECT * FROM `users`');

$db->insert("INSERT INTO `users` (`username`, `email`) VALUES ('testuser', 'testuser@test.com')");
$db->update("UPDATE `users` SET `logins` = 3 WHERE `username` = 'testuser'");
$db->delete("DELETE FROM `users` WHERE `username` = 'testuser'");

echo $db->getLastId();

Each of the four checks the statement's first keyword and refuses anything else, so a DELETE handed to select() throws Pop\Db\Adapter\Exception, Error: The SQL statement is not a valid SELECT statement. rather than running. The check is on the verb only — it's a guard against a mistyped method, not against a malicious string.

query() is the unrestricted form. It runs whatever it is given, returns the adapter rather than the rows, and leaves the result on the adapter for you to fetch:

use Pop\Db\Record;

$db = Record::getDb();

$db->query('SELECT * FROM users');

echo $db->getNumberOfRows();

$users = $db->fetchAll();

query() also covers statements with no result set — CREATE TABLE, SET, VACUUM. getNumberOfAffectedRows() reports on the last write and getLastId() returns the key the database generated.

For the rare string that has to go into a statement literally, escape() quotes it the way the connected driver wants:

use Pop\Db\Record;

$db = Record::getDb();

echo $db->escape("O'Brien");

That comes back O\'Brien on MySQL and O''Brien on both PostgreSQL and SQLite — each adapter defers to its own driver's escaping, which is exactly why an escaped string is not portable and a parameter is.

executeSql() sits between the two: it routes to query() when the parameter array is empty and to the prepared path when it isn't, returning the adapter either way.

Prepared Statements#

Passing a second argument to any of the four verbs turns the statement into a prepared one. The values are bound by the driver and never interpolated into the SQL:

use Pop\Db\Record;

$db = Record::getDb();

$users = $db->select('SELECT * FROM `users` WHERE `id` < ?', [10]);

$db->insert(
    'INSERT INTO `users` (`username`, `email`) VALUES (?, ?)',
    ['testuser', 'testuser@test.com']
);

Those two use MySQL's positional ? — each driver has its own placeholder form, covered below.

The long form is prepare(), bindParams(), execute(), and it's what you want when the same statement runs more than once:

use Pop\Db\Record;

$db = Record::getDb();

$db->prepare('SELECT * FROM `users` WHERE `id` = ?')
   ->bindParams([1])
   ->execute();

$user = $db->fetch();

Placeholders in a handwritten SQL string are the driver's own: MySQL takes positional ?, PostgreSQL takes numbered $1, $2, and SQLite takes named :name. Everywhere one of these methods takes a SQL string it also takes a Pop\Db\Sql builder object, which writes the placeholder the connected adapter wants — see Query Builder for a parameterized statement that runs unchanged on all three.

On PostgreSQL a prepared write reports zero affected rows, so read the outcome from the rows themselves rather than from the return value.

Fetching Results#

After query() or execute(), fetchAll() returns every remaining row and fetch() returns one at a time, which is what you want for a result set too large to hold in memory:

use Pop\Db\Record;

$db = Record::getDb();

$db->query('SELECT * FROM users');

while ($row = $db->fetch()) {
    echo $row['username'] . "\n";
}

The two share a cursor. A fetch() followed by fetchAll() on the same result returns the remaining rows, not all of them — on a three-row result that is one row and then two.

Every row is an associative array keyed by column name. What the values are typed as is not consistent across adapters, and it's the difference most likely to bite a strict comparison:

So $row['id'] === 1 is true on SQLite, true on MySQL through a prepared statement, and false on MySQL through query() and on PostgreSQL always. Compare loosely, or cast, or let a record class hand you the row — but do not write === against a column and assume it travels.

getNumberOfRows() reports the row count of the current result set, and is available before you fetch anything from it.

Transactions#

The adapter's transaction API is four methods: beginTransaction(), commit(), rollback(), and transaction() for the common case where all three-fold into one call.

use Pop\Db\Record;

$db = Record::getDb();

try {
    $db->beginTransaction();
    $db->query("INSERT INTO users (username, email) VALUES ('testuser', 'testuser@test.com')");
    $db->query("INSERT INTO user_info (user_id, city) VALUES (1, 'Austin')");
    $db->commit();
} catch (\Exception $e) {
    $db->rollback();
    echo $e->getMessage();
}

transaction() takes a callable and writes that try/catch for you. It begins, calls, and commits; if the callable throws, it rolls back and rethrows, so the exception still reaches your handler:

use Pop\Db\Record;

$db = Record::getDb();

try {
    $db->transaction(function () use ($db) {
        $db->query("INSERT INTO users (username, email) VALUES ('testuser', 'testuser@test.com')");
    });
} catch (\Exception $e) {
    echo $e->getMessage();
}

Nesting is genuinely supported. The outermost beginTransaction() issues a real BEGIN and every one inside it a SAVEPOINT; a commit at depth 2 releases that savepoint and a rollback returns to it.

use Pop\Db\Record;

$db = Record::getDb();

$db->transaction(function () use ($db) {
    $db->query("INSERT INTO users (username, email) VALUES ('outer', 'outer@test.com')");

    try {
        $db->transaction(function () use ($db) {
            $db->query("INSERT INTO users (username, email) VALUES ('inner', 'inner@test.com')");
            throw new \RuntimeException('inner failed');
        });
    } catch (\RuntimeException $e) {
        // the inner rows are gone; the outer transaction is still open
    }

    $db->query("INSERT INTO users (username, email) VALUES ('outer2', 'outer2@test.com')");
});

Run that and outer and outer2 are committed while inner is not, on MySQL, PostgreSQL and SQLite alike — the savepoint hooks are implemented on all four native adapters and on PDO. isTransaction() reports whether one is open and getTransactionDepth() returns the current depth:

use Pop\Db\Record;

$db = Record::getDb();

echo $db->getTransactionDepth();   // 0

$db->beginTransaction();
$db->beginTransaction();

echo $db->getTransactionDepth();   // 2

$db->rollback();                   // back to the savepoint
$db->rollback();                   // the real ROLLBACK

echo $db->getTransactionDepth();   // 0

The depth is 0 outside any transaction and back to 0 after the outermost commit or rollback, which makes it the thing to assert on if you suspect a code path is leaving a transaction open.

Keep DDL out of a transaction on MySQL — a CREATE TABLE, ALTER TABLE or DROP TABLE commits the open transaction as it runs.

Records & the ORM covers the record-level wrappers — Record::transaction() and the per-record startTransaction() — which drive this same machinery from a table class.

See Also#

• Query Builder — portable SQL, and the answer to the placeholder problem above
• Records & the ORM — record-level transactions and query()/execute() on a table class
• Connecting & Adapters — building the adapter these methods hang off
• Profiler — recording what the adapter runs, with parameters and timing
• pop-db README — the full adapter API surface