Queries

Most work with the database is reading and writing rows. The adapter runs SQL through a statement object, which binds parameters, executes the query and returns the result in the shape you ask for. Queries can be plain SQL strings or objects built with the Query component.

Setup

Assume $db is a DatabaseAdapter, obtained as described in the Introduction.

execute() runs a query and returns the statement. prepare() creates a statement without running it, which lets you bind values or fetch rows later:

$stmt = $db->prepare('SELECT * FROM posts WHERE state = ?');

$posts = $stmt->execute([1])->all();

Table prefixes are covered in Connections.

Showcase

A typical read and write sequence, using positional placeholders:

// Read a single value.
$count = (int) $db->prepare(
    'SELECT COUNT(*) FROM posts WHERE author_id = ?'
)->execute([$authorId])->result();

// Read rows as arrays.
$rows = $db->prepare(
    'SELECT id, title FROM posts WHERE author_id = ? ORDER BY id'
)->execute([$authorId])->all();

// Run a write statement.
$db->execute(
    'UPDATE posts SET state = ? WHERE id = ?',
    [0, 10],
);

Each placeholder is bound to the value at the same position in the array. Always pass values as parameters, not as string fragments in the SQL.

Fetching Results

A statement offers several ways to read the result. Pick the one that matches the shape you need:

• all(): every row, returned as a Collection.
• get(): the first row, or null when there is none.
• fetch(): the next row each time it is called, or null at the end.
• result(): the first column of the first row, or null.
• loadColumn(): one column across all rows, as a Collection.
• getIterator(): iterates over the rows without loading them all.

Without a class argument, rows are returned as arrays. The result methods all(), get(), fetch() and getIterator() take an optional class name as their first argument.

Hydrating Rows into Objects

Pass a class name, and each row is hydrated into a new instance of that class. Columns are matched to properties by name:

use App\Entity\Post;

$posts = $db->prepare('SELECT * FROM posts')
    ->execute()
    ->all(Post::class);

$post = $db->prepare('SELECT * FROM posts WHERE id = ?')
    ->execute([1])
    ->get(Post::class);

The same argument works with fetch() and getIterator():

$stmt = $db->prepare('SELECT * FROM posts')->execute();

while ($post = $stmt->fetch(Post::class)) {
    // ...
}

INFO

The class is created with new, without constructor arguments. The class must be constructible without any arguments, and the hydrator writes the row values to its properties afterwards.

Hydration uses SimpleHydrator by default. It writes to properties through reflection, so private and protected properties receive values too. Declare a property for every selected column:

class Post
{
    public int $id;

    public string $title;

    private string $state;
}

TIP

A column without a matching declared property creates a dynamic property on the object. PHP 8.2 and later report this as a deprecation. Declare each column, or select only the columns the class declares.

Objects that implement AccessorAccessibleInterface are hydrated through their accessors, and ArrayAccess objects receive the values as array keys. To replace the hydrator for a driver, call setHydrator() on the driver with any object that implements HydratorInterface.

Iterating Large Results

getIterator() yields one hydrated row at a time, so a large table can be processed without holding every row in memory:

$logs = $db->prepare('SELECT * FROM logs')
    ->getIterator(Log::class);

foreach ($logs as $log) {
    // ...
}

result(true) throws an exception when no row is found, instead of returning null. Use it when a missing value is an error.

The iterator methods, batching and cursor pagination are described in Iteration. The ORM offers the same methods on its entity queries, see Iterating Large Results.

Query Builder

createQuery() returns a Query object bound to the adapter's platform, so the generated SQL follows the current database dialect. Build the query with chained calls, then run it with the same fetch methods as a statement:

$posts = $db->createQuery()
    ->select('id', 'title')
    ->from('posts')
    ->where('state', 1)
    ->order('id', 'DESC')
    ->all(Post::class);

$post = $db->createQuery()
    ->select('*')
    ->from('posts')
    ->where('id', 1)
    ->get(Post::class);

Each builder method returns the query, so the chain can continue until a fetch method ends it. get(), all(), result(), loadColumn() and execute() run the query directly. getIterator() and count() are available as well.

A query can also be passed to prepare() or execute() like a string, when you need to bind values through the statement first:

$stmt = $db->prepare($query);

getQuery() returns the last query the adapter has used for building, and getLastQuery() returns the last query executed. Both are useful for debugging.

Transactions

transaction() runs a callback inside a transaction. If the callback returns normally, the transaction is committed. If it throws, the transaction is rolled back and the exception is rethrown. The callback receives the adapter and the platform:

use Windwalker\Database\DatabaseAdapter;

$postId = $db->transaction(function (DatabaseAdapter $db) use ($data) {
    $db->writer->insertOne('posts', $data, 'id');

    $db->writer->insertOne(
        'post_logs',
        ['post_id' => $data['id'], 'action' => 'created'],
    );

    return $data['id'];
});

Use isInTransaction() to check whether a transaction is open.

Pass autoCommit: false to keep the transaction open after the callback returns. The commit or rollback is then your code's responsibility, and the platform provides both:

$db->transaction(
    function (DatabaseAdapter $db) use ($data) {
        $db->writer->insertOne('posts', $data, 'id');
    },
    autoCommit: false,
);

// Later, when the rest of the work has succeeded:
$db->getPlatform()->transactionCommit();

INFO

Exceptions still roll back an open transaction, even with autoCommit: false. Call transactionCommit() only after the work is complete.

Pass enabled: false to run the callback without a transaction. The callback receives the same arguments, which keeps one code path for environments where transactions are not wanted, such as some test setups:

$db->transaction(
    fn (DatabaseAdapter $db) => $db->writer->insertOne(
        'posts',
        $data,
        'id',
    ),
    enabled: $useTransactions,
);

WARNING

The database rollback does not undo effects outside the database. Do not send an email or call an external API inside the callback and assume it can be reverted; run such work after the transaction returns.

TIP

Keep transactions short. A long transaction holds locks and keeps a connection busy, which matters most when a connection pool is in use.

Quoting and Escaping

Values should always be bound as parameters. Quoting is needed only when SQL is built from text that cannot be a parameter, such as a column name chosen at runtime, or a literal inside a fixed expression:

• quote(): wraps a value in quotes and escapes it for the platform.
• quoteName(): quotes an identifier, such as a table or column name.
• escape(): escapes a value without adding quotes.

$db->quote("O'Reilly"); // 'O''Reilly'

$db->quoteName('title'); // `title` on MySQL

A dotted name is split into parts, and each part is quoted separately. Pass true as the second argument to keep the dot inside one quoted name:

$db->quoteName('users.name');       // ['`users`', '`name`']

$db->quoteName('users.name', true); // `users.name`

WARNING

Quoting does not protect an identifier that comes from user input. Check column and table names against a whitelist before you put them in SQL.

Handling Errors

Every error raised by the package extends DatabaseException, so one catch block covers them all. The more specific classes help when the handling differs:

• DatabaseQueryException: a query failed. With debug enabled, its message includes the SQL. The original driver error is available through getPrevious().
• StatementException: an error raised by a statement operation.
• DatabaseConnectException: a connection could not be opened, for drivers that report it as this type.

use Windwalker\Database\Exception\DatabaseException;
use Windwalker\Database\Exception\DatabaseQueryException;

try {
    $db->execute('UPDATE posts SET state = ? WHERE id = ?', [1, 10]);
} catch (DatabaseQueryException $e) {
    $logger->error($e->getMessage(), ['previous' => $e->getPrevious()]);
}

TIP

Catch DatabaseException when the reason for the failure does not matter, such as in a retry loop. Inspect getPrevious() for the driver's own error code.

Dates and NULL Values

Each platform stores dates in its own format and uses its own placeholder for an empty date. Read these values from the adapter instead of hard-coding them:

• getDateFormat(): the format for date and time values, such as Y-m-d H:i:s.
• getNullDate(): the placeholder for an empty date. The value depends on the platform, for example 1000-01-01 00:00:00 on MySQL.
• isNullDate(): true when a value means "no date". It accepts null, an empty string, the platform's null date, and a DateTimeInterface.

$date = $row->created;

if ($db->isNullDate($date)) {
    $date = null;
}

INFO

isNullDate() does not accept an integer timestamp. Convert timestamps to a string or a DateTime before you pass them in.

Counting Rows

countWith() returns the number of rows a query would return. It wraps the query in a count, so the query's own columns are not fetched. Use it for pagination when the query contains GROUP BY:

$total = $db->countWith(
    $db->createQuery()
        ->select('author_id')
        ->from('posts')
        ->group('author_id'),
);

The query builder is also supports chaining count() directly:

$total = $db->createQuery()
    ->select('author_id')
    ->from('posts')
    ->group('author_id')
    ->count();