Skip to content

method-reference.md

Latest commit

 

History

History
219 lines (171 loc) · 18.2 KB

method-reference.md

File metadata and controls

219 lines (171 loc) · 18.2 KB
Raw

Method Reference

The DB class is a static facade over a default connection. The methods below also exist on Connection instances ($db->select(...)) with the same signature, except the four marked DB only and DB::connect() (a Connection connects in its constructor). Method names link to the guide section that covers each in depth.

Contents:

Connecting

Method Returns Description
DB::connect(array $config = []) void Connect and set the default connection. Throws RuntimeException if already connected
DB::isConnected(bool $ping = false) bool Check if the default connection is set, optionally pinging the server
DB::disconnect() void Close the default connection and clear DB::$mysqli and DB::$tablePrefix
DB::clone(array $config = []) Connection Copy the default connection, sharing the same mysqli link; only tablePrefix, useSmartJoins, and useSmartStrings can be overridden
new Connection(array $config = []) Connection Create a standalone connection (connects in the constructor); use for a second database or different settings

See Configuration Options for the full list of $config keys.

Querying Data

Method Returns Description
DB::select(string $baseTable, $whereEtc = [], ...$params) SmartArrayHtml All matching rows
DB::selectOne(string $baseTable, $whereEtc = [], ...$params) SmartArrayHtml First matching row (sends LIMIT 1); empty result object when no row matches, never null
DB::count(string $baseTable, $whereEtc = [], ...$params) int SELECT COUNT(*) of matching rows

select() and selectOne() declare SmartArrayBase as their return type. The object you get is a SmartArrayHtml of SmartString values by default, or a plain-value SmartArray when useSmartStrings is false. On multi-table query() results, ->{'table.column'} reads the table.column keys that Smart Joins add. Working with Results covers both.

selectOne(), queryOne(), and count() throw if $whereEtc or the template has a top-level LIMIT or OFFSET clause ("This method doesn't support LIMIT or OFFSET"); the escape hatch is DB::query(...)->first().

Modifying Data

Method Returns Description
DB::insert(string $baseTable, array $values) int Insert one row; returns the new auto-increment ID (0 when the table has no auto-increment column)
DB::update(string $baseTable, array $values, $whereEtc, ...$params) int Update matching rows; returns rows actually changed (MySQL affected_rows), not rows matched
DB::delete(string $baseTable, $whereEtc, ...$params) int Delete matching rows; returns rows deleted
DB::transaction(callable $fn) mixed Run $fn in a transaction: commit on return, rollback and rethrow on exception; returns $fn's return value. Throws RuntimeException when nested

update() and delete() require a WHERE condition; an empty one throws. To update or delete every row on purpose, pass the literal string "TRUE". Details in Modifying Data.

Custom SQL

Method Returns Description
DB::query(string $sqlTemplate, ...$params) SmartArrayHtml Run custom SQL with placeholders
DB::queryOne(string $sqlTemplate, ...$params) SmartArrayHtml First row of custom SQL (appends LIMIT 1 to SELECT/WITH statements); empty result object when no row matches

Both return the same result objects as select(); see Querying Data above.

Table Names

Method Returns Description
DB::getBaseTable(string $table, bool $checkDb = false) string Strip tablePrefix from a table name; with $checkDb, queries the database to resolve base names that themselves start with the prefix
DB::getFullTable(string $table, bool $checkDb = false) string Prepend tablePrefix to a table name; with $checkDb, queries the database to resolve the same ambiguity

Query Helpers

Method Returns Description
DB::rawSql(string|int|float|null $value) RawSql DB only. Mark a value as literal SQL, skipping escaping and quoting (e.g., NOW()); null becomes NULL
DB::pagingSql(mixed $page, mixed $perPage = 10) RawSql DB only. LIMIT $perPage OFFSET ... clause; zero, empty, or invalid input becomes page 1 / 10 per page
DB::likeContains($input) RawSql Escaped LIKE pattern '%value%'
DB::likeStartsWith($input) RawSql Escaped LIKE pattern 'value%'
DB::likeEndsWith($input) RawSql Escaped LIKE pattern '%value'
DB::likeContainsTSV($input) RawSql Escaped LIKE pattern '%\tvalue\t%' for matching one value in a tab-separated column

The like*() methods accept string|int|float|null|SmartString and escape % and _ in the input, so a search for "50%" matches the literal text:

$news = DB::select('news', "title LIKE ?", DB::likeContains($_GET['q'] ?? ''));
// for q=50% this runs: WHERE title LIKE '%50\\%%'

pagingSql() turns a page number into a LIMIT ... OFFSET ... clause, so the page number can come straight from the URL:

$page  = $_GET['page'] ?? 1;
$users = DB::select('users', "ORDER BY name :pagingSQL", [
    ':pagingSQL' => DB::pagingSql($page, 25),
]);
// for page 1 this runs: ORDER BY name LIMIT 25 OFFSET 0

Encryption

Encryption is opt-in: set encryptionKey in the connect config and MEDIUMBLOB columns auto-encrypt on insert()/update() and auto-decrypt on read. These helpers cover values that bypass those methods.

Method Returns Description
DB::encryptValue($value) string|null Encrypt a value in PHP, matching what insert()/update() produce; null in, null out (before any key check). Anything else throws RuntimeException when encryptionKey is not set
DB::decryptExpr(string $column) string DB only. SQL expression to decrypt a column server-side: decryptExpr('email') → AES_DECRYPT(`email`, @ek). The {{column}} template syntax generates this for you
DB::decryptRows(array &$rows, array $keysOrFetchFields) void Decrypt raw mysqli rows in place; pass $result->fetch_fields() to auto-detect MEDIUMBLOB columns, or name the keys yourself (column names for associative rows, field indexes for numeric rows)
DB::getEncryptedColumns(array $fetchFields) array DB only. The MEDIUMBLOB columns in a result, from $result->fetch_fields(), keyed by field index: [2 => 'apiToken']

encryptValue() accepts string|int|float|null|SmartString. Use it for exact matches on encrypted columns (the encryption is deterministic):

$user = DB::selectOne('users', ['token' => DB::encryptValue($searchToken)]);

Constants

Constant Value Description
DB::DATETIME 'Y-m-d H:i:s' date() format for MySQL DATETIME columns (2026-03-16 14:30:00)
DB::DATE 'Y-m-d' date() format for MySQL DATE columns (2026-03-16)
DB::TIME 'H:i:s' date() format for MySQL TIME columns (14:30:00) 
DB::insert('news', ['title' => 'Launch day', 'publishDate' => date(DB::DATETIME)]);
// INSERT INTO `news` SET `title` = 'Launch day', `publishDate` = '2026-03-16 14:30:00'

Properties

Property Type Description
DB::$mysqli ?MysqliWrapper The underlying connection (a mysqli subclass) for direct access: DB::$mysqli->insert_id, DB::$mysqli->query($ddl), and DB::$mysqli->lastQuery (last SQL sent, for debugging); null when disconnected
DB::$tablePrefix string The prefix prepended to table names, as set at connect ('' by default)

Parameter Forms

$baseTable is the table name without the prefix; tablePrefix is prepended automatically. Names may only contain a-z, A-Z, 0-9, _, and -; anything else throws InvalidArgumentException.

$whereEtc takes an array of conditions or an SQL string that uses ? or :name placeholders for values; The Three WHERE Forms shows each one. It can be omitted to match all rows in select(), selectOne(), and count():

// Array: column => value pairs, joined with AND
// (null values become IS NULL, array values become IN (...))
$users = DB::select('users', ['status' => 'active', 'city' => 'Vancouver']);
// WHERE `status` = 'active' AND `city` = 'Vancouver'

// String: SQL with placeholders; the WHERE keyword is optional,
// and ORDER BY / GROUP BY / LIMIT can follow
$users = DB::select('users', "status = ? ORDER BY name LIMIT 10", 'active');
// WHERE status = 'active' ORDER BY name LIMIT 10

...$params takes up to 3 positional values passed directly for ? placeholders. For 4 or more values, use named placeholders in a single array:

// Up to 3 values: pass them directly
$users = DB::select('users', "status = ? AND age > ?", 'active', 25);
// WHERE status = 'active' AND age > 25

// 4+ values: one array of named placeholders
$users = DB::select('users', "city = :city AND status = :status AND age BETWEEN :min AND :max", [
    ':city'   => 'Vancouver',
    ':status' => 'active',
    ':min'    => 18,
    ':max'    => 65,
]);
// WHERE city = 'Vancouver' AND status = 'active' AND age BETWEEN 18 AND 65

Mixing ? and :name in one query throws.

$sqlTemplate is raw SQL using ::table for prefixed table names, ? and :name for values, `?` and `:name` for identifiers, ::? and :::name for values with the table prefix added ('user%' → 'cms_user%'), and {{column}} for encrypted-column reads. Quotes, inline numbers, and hex literals are rejected before the query runs (the allowed exceptions: a trailing literal LIMIT 10, and empty string literals like != ''). Full rules in Placeholders and Joins and Custom SQL.

$values (for insert()/update()) is an associative array of column names to values. Use DB::rawSql() for SQL expressions:

DB::insert('news', ['title' => 'Launch day', 'createdDate' => DB::rawSql('NOW()')]);
// INSERT INTO `news` SET `title` = 'Launch day', `createdDate` = NOW()

← Troubleshooting | Documentation Index | Next: AI Reference →