Database Class

Revision: 2025-11-12

Low-level database access for SQL queries, transactions, and schema inspection. Supports MySQL, SQLite, and ArrayDB with unified interface.

Higher-Level Abstractions
For object-oriented data management, use AbstractModel (automatic validation, relationships, CRUD).
For programmatic query building, use the Query class (fluent interface).

Error Handling
Query failures throw DatabaseException. Always use try/catch in production.

Connections

Two database connections available:

Primary (db) - System configurations, internal modules
Secondary (db2) - Main application data

$db = Get::db();   // Primary
$db2 = Get::db2(); // Secondary
// Both support identical methods

ArrayDB (in-memory)

ArrayDB is an in-memory database powered by ArrayQuery. It is accessed through the Get::arrayDb() singleton, so the instance is reused within the same request. Tables are populated with addTable(), where you can also specify the auto-increment column.

use App\Get;

$db = Get::arrayDb(); // singleton

$db->addTable('users', [
    ['id' => 1, 'name' => 'Mario', 'age' => 30],
    ['id' => 2, 'name' => 'Laura', 'age' => 25],
], 'id');

$adults = $db->getResults('SELECT name FROM users WHERE age > ?', [26]);

Available methods

Metodo	Parametri	Description
`connect($data = null, array $autoIncrementColumns = [])`	ArrayEngine\|array\|null, auto_increment map	Initializes the in-memory database from ArrayEngine or from a tables array.
`addTable(string $tableName, array $data = [], ?string $autoIncrementColumn = null)`	name, rows, auto_increment column	Adds a table and optionally defines the auto-increment column.
`query(string $sql, array\|null $params = null)`	SQL, params	Executes DML SQL (SELECT/INSERT/UPDATE/DELETE) with `?` placeholders.
`getResults(string $sql, $params = null)`	SQL, params	Returns all rows as an array of objects.
`getRow(string $sql, $params = null, int $offset = 0)`	SQL, params, offset	Returns a single row.
`getVar(string $sql, $params = null, int $offset = 0)`	SQL, params, offset	Returns the first value of the selected row.
`yield(string $sql, $params = null)`	SQL, params	Iterates results with a generator for large datasets.
`nonBufferedQuery(string $table, bool $assoc = true)`	table, associative	Iterates records from a table without buffering.
`insert(string $table, array $data)`	table, data	Inserts a record and returns the id or false.
`update(string $table, array $data, array $where, int $limit = 0)`	table, data, where, limit	Updates records that match the conditions.
`delete(string $table, array $where)`	table, where	Deletes records that match the conditions.
`save(string $table, array $data, array $where)`	table, data, where	Upsert: updates if it exists, otherwise inserts.
`affectedRows()`	-	Number of rows affected by the last operation.
`insertId()`	-	Last auto-increment id inserted.
`getTables(bool $cache = true)`	cache	List of tables in memory.
`getColumns(string $table, bool $force_reload = false)`	table, reload	Columns and metadata based on the current data.
`describes(string $table, bool $cache = true)`	table, cache	Full table structure.
`showCreateTable(string $table)`	table	Fake SQL string for CREATE TABLE.
`dropTable($table)`	table	Removes the table from memory.
`renameTable($table, $new_name)`	table, new name	Renames a table in memory.
`truncateTable($table)`	table	Empties the table while keeping the structure.
`multiQuery(string $sql)`	multi SQL	Executes multiple queries separated by semicolons.
`lastQuery()`	-	Last executed query (debug).
`getLastError()`	-	Error message from the last operation.
`hasError()`	-	True if the last operation failed.
`getType()`	-	Returns the connection type: `array`.
`begin(), commit(), tearDown(), close()`	-	Provided for compatibility, no effect on ArrayDB.

Query Execution

query(string $sql, array|null $params = null) : MySQLResult|SQLiteResult|bool

Executes SQL with prepared statements. Returns result object for SELECT, bool for others.

// SELECT
$result = $db->query("SELECT * FROM #__users WHERE status = ?", ['active']);

// Multiple parameters
$result = $db->query(
    "SELECT * FROM #__users WHERE username = ? AND status = ?",
    ['john', 'active']
);

getResults(string $sql, array|null $params = null) : array|null

Returns all rows as array of objects.

$users = $db->getResults("SELECT * FROM #__users WHERE status = ?", ['active']);
foreach ($users as $user) {
    echo $user->username;
}

getRow(string $sql, array|null $params = null, int $offset = 0) : object|null

Returns single row as object.

$user = $db->getRow("SELECT * FROM #__users WHERE id = ?", [123]);
echo $user?->username;

getVar(string $sql, array|null $params = null, int $offset = 0) : string|null

Returns single value from first column.

$count = $db->getVar("SELECT COUNT(*) FROM #__users");
$username = $db->getVar("SELECT username FROM #__users WHERE id = ?", [123]);

yield(string $sql, array|null $params = null) : \Generator|null

Returns generator to iterate large datasets without loading all in memory.

foreach ($db->yield("SELECT * FROM #__large_table") as $row) {
    processRow($row);
}

Data Manipulation

insert(string $table, array $data) : bool|int

Inserts record, returns auto-increment ID or false.

$userId = $db->insert('users', [
    'username' => 'john_doe',
    'email' => 'john@example.com',
    'created_at' => date('Y-m-d H:i:s')
]);

if ($userId) {
    echo "User created with ID: {$userId}";
}

update(string $table, array $data, array $where, int $limit = 0) : bool

Updates records matching WHERE conditions.

$db->update(
    'users',
    ['username' => 'new_username'],
    ['id' => 123]
);
echo "Updated " . $db->affectedRows() . " rows";

save(string $table, array $data, array $where) : bool|int

Upsert - updates if exists, inserts if not.

$result = $db->save(
    'settings',
    ['key' => 'site_title', 'value' => 'My Website'],
    ['key' => 'site_title']
);

if (is_int($result)) echo "Inserted with ID: {$result}";
elseif ($result === true) echo "Updated successfully";

delete(string $table, array $where) : bool

Deletes records matching WHERE conditions.

$db->delete('users', ['id' => 123]);
$db->delete('users', ['status' => 'inactive']);

affectedRows() : int

Returns rows affected by last INSERT/UPDATE/DELETE.

insertId() : int

Returns auto-increment ID from last INSERT.

Schema Inspection

getTables(bool $cache = true) : array

Returns list of table names.

getViews(bool $cache = true) : array

Returns list of view names.

getViewDefinition(string $view_name) : string|null

Returns SQL definition of a view.

getColumns(string $table_name, bool $force_reload = false) : array

Returns column information with caching.

$columns = $db->getColumns('users');
foreach ($columns as $column) {
    echo $column->Field . " - " . $column->Type;
}

describes(string $tableName, bool $cache = true) : array

Returns complete table structure: fields, primary keys, column details.

$info = $db->describes('users');
$idType = $info['fields']['id'];  // "int(11)"
$primaryKeys = $info['keys'];      // ["id"]

foreach ($info['struct'] as $field => $details) {
    echo $field . ": " . $details->Type;
}

showCreateTable(string $table_name) : array

Returns array ['type' => 'table'|'view', 'sql' => 'CREATE...'].

Table Management

dropTable(string $table) : bool

Drops table if exists.

dropView(string $view) : bool

Drops view if exists.

renameTable(string $table_name, string $new_name) : bool

Renames table.

truncateTable(string $table_name) : bool

Removes all data and resets auto-increment.

multiQuery(string $sql) : bool

Executes multiple statements separated by semicolons.

$sql = "
    CREATE TABLE #__temp (id INT, name VARCHAR(255));
    INSERT INTO #__temp VALUES (1, 'Test');
";
$db->multiQuery($sql);

Transaction Control

begin() : void

Starts transaction.

commit() : void

Commits transaction (makes changes permanent).

tearDown() : void

Rolls back transaction (cancels all changes).

try {
    $db->begin();

    $orderId = $db->insert('orders', [
        'user_id' => 123,
        'total' => 99.50
    ]);

    $db->insert('order_items', [
        'order_id' => $orderId,
        'product_id' => 1,
        'quantity' => 2
    ]);

    $db->commit();
    echo "Order created!";

} catch (Exception $e) {
    $db->tearDown();
    echo "Error: " . $e->getMessage();
}

Error Handling

hasError() : bool

Returns true if last operation generated error.

getLastError() : string

Returns last error message.

try {
    $result = $db->query("SELECT * FROM #__users");
} catch (DatabaseException $e) {
    echo "Error: " . $e->getMessage();
    error_log("DB error: " . $db->getLastError());
}

Utility Methods

lastQuery() : string

Returns last executed SQL query.

toSql(string $query, array $params) : string

Returns query with substituted parameters (debug only, NOT for execution).

qn(string $val) : string

Quotes table/column names for safe use.

$sql = "SELECT {$db->qn('username')} FROM {$db->qn('users')}";

quote(string $val) : string

Quotes values (prefer prepared statements).

Result Objects

SELECT queries return result objects with these methods:

fetchArray() / fetchAssoc() - Next row as array
fetchObject() - Next row as object
numRows() - Number of rows
numColumns() - Number of columns
reset() - Reset cursor
finalize() - Free memory

Table Prefix

Use #__ placeholder - automatically replaced with configured prefix:

// If prefix is 'wp', becomes: SELECT * FROM wp_users
$db->query("SELECT * FROM #__users");

Examples

Complete CRUD Transaction

use App\Exceptions\DatabaseException;

try {
    $db->begin();

    // Create user
    $userId = $db->insert('users', [
        'username' => 'john_doe',
        'email' => 'john@example.com'
    ]);

    // Create profile
    $db->insert('profiles', [
        'user_id' => $userId,
        'bio' => 'Hello world'
    ]);

    // Update status
    $db->update('users', ['status' => 'active'], ['id' => $userId]);

    $db->commit();
    echo "Success!";

} catch (DatabaseException $e) {
    $db->tearDown();
    error_log("Transaction failed: " . $e->getMessage());
}

Schema Inspection

// List all tables and their columns
$tables = $db->getTables();
foreach ($tables as $table) {
    echo "Table: $table\n";

    $columns = $db->getColumns($table);
    foreach ($columns as $col) {
        echo "  - {$col->Field} ({$col->Type})\n";
    }
}

Streaming Large Dataset

// Process millions of rows efficiently
foreach ($db->yield("SELECT * FROM #__logs WHERE year = ?", [2024]) as $log) {
    processLog($log);
    // Memory usage stays constant
}

Milk Admin

Core

Theme

Dynamic Table

Forms

Database Class

Connections

ArrayDB (in-memory)

Available methods

Query Execution

query(string $sql, array|null $params = null) : MySQLResult|SQLiteResult|bool

getResults(string $sql, array|null $params = null) : array|null

getRow(string $sql, array|null $params = null, int $offset = 0) : object|null

getVar(string $sql, array|null $params = null, int $offset = 0) : string|null

yield(string $sql, array|null $params = null) : \Generator|null

Data Manipulation

insert(string $table, array $data) : bool|int

update(string $table, array $data, array $where, int $limit = 0) : bool

save(string $table, array $data, array $where) : bool|int

delete(string $table, array $where) : bool

affectedRows() : int

insertId() : int

Schema Inspection

getTables(bool $cache = true) : array

getViews(bool $cache = true) : array

getViewDefinition(string $view_name) : string|null

getColumns(string $table_name, bool $force_reload = false) : array

describes(string $tableName, bool $cache = true) : array

showCreateTable(string $table_name) : array

Table Management

dropTable(string $table) : bool

dropView(string $view) : bool

renameTable(string $table_name, string $new_name) : bool

truncateTable(string $table_name) : bool

multiQuery(string $sql) : bool

Transaction Control

begin() : void

commit() : void

tearDown() : void

Error Handling

hasError() : bool

getLastError() : string

Utility Methods

lastQuery() : string

toSql(string $query, array $params) : string

qn(string $val) : string

quote(string $val) : string

Result Objects

Table Prefix

Examples

Complete CRUD Transaction

Schema Inspection

Streaming Large Dataset