Database API – DB Class

The DB class provides a multi‑connection, SQL‑injection‑safe database abstraction layer. Every method uses prepared statements internally. All data handling is automatic – you never need to escape values manually.

Contents

• Connecting to a Database
• Select – Retrieving Data
• Insert – Adding Records
• Update – Modifying Records
• Delete – Removing Records
• Query – Raw SELECT Queries
• Exec – DDL / Non‑returning Queries
• PrepareSQL – Safe Variable Substitution
• DBLookup – Single Value Lookup
• FetchOne – Scalar Helper
• FetchAll – Multi‑row Helper
• QueryResult Object
• Working with Multiple Connections
• TestConnection – Verify a Database Connection
• DropDatabase – Delete a Database
• Error Handling

Connecting to a Database

Before using any DB method, you must initialise a connection.

Central (framework) database

// The central database (users, projects, settings) is set up automatically
// by public/index.php. No manual call needed.
DB::Init();  // connects to the database defined in config/database.php

Project database

// Connects to the database belonging to project "my‑shop"
DB::Init('my-shop');

Multiple connections (Enterprise)

// Register additional connections
DB::AddConnection('inventory', 'mysql:host=10.0.0.5;port=3306;dbname=stock;charset=utf8mb4', 'user2', 'pass2');
DB::AddConnection('analytics', 'mysql:host=10.0.0.6;port=3306;dbname=reports;charset=utf8mb4', 'user3', 'pass3');

// Switch context
DB::SetConnection('inventory');
$rows = DB::Select('products');

// Switch back to the main project connection
DB::SetConnection('my-shop');

Supported databases

The same query API runs on every engine below — PPPHP generates dialect-correct SQL for each. A project's engine is chosen in its connection profile; you rarely write a DSN by hand, but AddConnection() accepts one for any of them (as long as the matching PDO driver is installed).

// TiDB speaks the MySQL protocol — same DSN shape, port 4000:
DB::AddConnection('scale', 'mysql:host=tidb.internal;port=4000;dbname=app;charset=utf8mb4', 'user', 'pass');

// CockroachDB speaks the PostgreSQL protocol — same DSN shape, port 26257:
DB::AddConnection('cloud', 'pgsql:host=crdb.internal;port=26257;dbname=app', 'user', 'pass');

Select – Retrieving Data

DB::Select($table, $where, $sort) returns a QueryResult object or false on failure.

Example 1 – Fetch all rows with a simple WHERE array

$rs = DB::Select('cars', ['make' => 'Toyota', 'model' => 'RAV4']);
while ($row = $rs->fetchAssoc()) {
    echo $row['id'] . ' — ' . $row['price'];
}
// SQL: SELECT * FROM cars WHERE make = 'Toyota' AND model = 'RAV4'

Example 2 – WHERE as a raw string

$rs = DB::Select('cars', 'price < 20000');
while ($row = $rs->fetchAssoc()) {
    echo $row['id'] . ': ' . $row['make'];
}
// SQL: SELECT * FROM cars WHERE price < 20000

Example 3 – Sorting (single field)

$rs = DB::Select('cars', [], 'name ASC');

Example 4 – Sorting (multiple fields, mixed directions)

$rs = DB::Select('cars', [], ['make ASC', 'price DESC']);

Example 5 – No WHERE, no sort (get everything)

$rs = DB::Select('employees');
// SQL: SELECT * FROM employees

Example 6 – Loop with fetchNumeric (indexed array)

$rs = DB::Select('cars', ['make' => 'Toyota']);
while ($row = $rs->fetchNumeric()) {
    echo $row[0];  // first column
}
// SQL: SELECT * FROM cars WHERE make = 'Toyota'

Insert – Adding Records

DB::Insert($table, $data) returns the new auto‑increment ID (or false).

Example 1 – Simple insert

$id = DB::Insert('cars', [
    'make'  => 'Toyota',
    'model' => 'RAV4',
    'price' => 16000
]);
echo "New car ID: $id";

Example 2 – Insert with a date

$id = DB::Insert('orders', [
    'customer_id' => 42,
    'total'       => 299.95,
    'created_at'  => date('Y-m-d H:i:s')
]);

Example 3 – Insert from form data

$id = DB::Insert('contacts', [
    'name'  => $_POST['name'],
    'email' => $_POST['email'],
    'phone' => $_POST['phone']
]);

Example 4 – Copy a record (AfterAdd event)

// Run in an AfterAdd event to mirror the record to an audit table
$data = [];
$data['original_id'] = $values['id'];
$data['make']        = $values['make'];
$data['model']       = $values['model'];
DB::Insert('cars_audit', $data);

Update – Modifying Records

DB::Update($table, $data, $where) returns the number of affected rows (or false).

Example 1 – Update by primary key (array WHERE)

$affected = DB::Update('cars', [
    'make'  => 'Toyota',
    'model' => 'RAV4',
    'price' => 17500
], ['id' => 50]);
echo "$affected row(s) updated.";
// SQL: UPDATE cars SET make='Toyota', model='RAV4', price=17500 WHERE id=50

Example 2 – Update by primary key (string WHERE)

$affected = DB::Update('cars', ['price' => 18000], 'id = 50');
// SQL: UPDATE cars SET price=18000 WHERE id=50

Example 3 – Update multiple rows

$affected = DB::Update('orders', ['status' => 'shipped'], 'ship_date < CURDATE()');
echo "$affected order(s) marked as shipped.";

Example 4 – Update with a counter

$affected = DB::Update('products', ['view_count' => $currentViews + 1], ['id' => $productId]);

Delete – Removing Records

DB::Delete($table, $where) returns the number of deleted rows (or false).

Example 1 – Delete by primary key

$deleted = DB::Delete('cars', ['id' => 50]);
echo "$deleted row(s) deleted.";

Example 2 – Delete by raw condition

$deleted = DB::Delete('sessions', 'expires < NOW()');
echo "$deleted expired session(s) removed.";

Example 3 – Delete with multiple conditions (array)

$deleted = DB::Delete('temp_logs', ['user_id' => 5, 'status' => 'archived']);

Query – Raw SELECT Queries

DB::Query($sql, $params) runs a raw SQL statement that returns data. Returns a QueryResult or false.

Example 1 – Basic SELECT with bound parameters

$rs = DB::Query('SELECT * FROM cars WHERE make = ? AND price > ?', ['Toyota', 15000]);
while ($row = $rs->fetchAssoc()) {
    echo $row['model'];
}

Example 2 – JOIN query

$sql = "SELECT o.id, c.name, o.total
        FROM orders o
        JOIN customers c ON o.customer_id = c.id
        WHERE o.status = ?
        ORDER BY o.created_at DESC";
$rs = DB::Query($sql, ['pending']);
while ($row = $rs->fetchAssoc()) {
    echo "Order #{$row['id']} by {$row['name']} — \${$row['total']}
";
}

Example 3 – Aggregate query

$rs = DB::Query('SELECT make, COUNT(*) AS cnt, AVG(price) AS avg_price FROM cars GROUP BY make');
while ($row = $rs->fetchAssoc()) {
    echo "{$row['make']}: {$row['cnt']} cars, avg \${$row['avg_price']}
";
}

Example 4 – Get a single value from a QueryResult

$rs = DB::Query('SELECT COUNT(*) FROM users WHERE active = ?', [1]);
$total = $rs->value();   // returns first column of first row
echo "Active users: $total";

Exec – DDL / Non‑returning Queries

DB::Exec($sql) runs a statement that does not return a result set (INSERT, UPDATE, DELETE, ALTER, TRUNCATE). Returns the number of affected rows.

Example 1 – Raw INSERT

$affected = DB::Exec("INSERT INTO log (event, user_id, created_at) VALUES ('login', 42, NOW())");
echo "Rows: $affected";

Example 2 – Raw UPDATE

$affected = DB::Exec("UPDATE orders SET status='shipped' WHERE ship_date = CURDATE()");
echo "$affected order(s) shipped today.";

Example 3 – DDL (ALTER TABLE)

DB::Exec("ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email");
echo "Column added.";

Example 4 – TRUNCATE

DB::Exec("TRUNCATE TABLE temp_import");
echo "Table cleared.";

PrepareSQL – Safe Variable Substitution

DB::PrepareSQL($sql, ...$args) substitutes :1, :2, :session.xxx placeholders with properly escaped values. This protects against SQL injection and handles apostrophes automatically.

Example 1 – Single parameter

$sql = DB::PrepareSQL("SELECT * FROM users WHERE id = :1", $userId);
$rs  = DB::Query($sql);
// If $userId is 25: SELECT * FROM users WHERE id = 25

Example 2 – Multiple parameters (text values need quotes!)

$sql = DB::PrepareSQL(
    "SELECT * FROM customers WHERE age > :1 AND last_name = ':2'",
    20,
    "O'Rourke"
);
$rs = DB::Query($sql);
// SQL: SELECT * FROM customers WHERE age > 20 AND last_name = 'O\'Rourke'
// Note: text values must be wrapped in single quotes in the template!

Example 3 – Session variable

$sql = DB::PrepareSQL("SELECT * FROM orders WHERE sales_rep = ':session.username'");
$rs  = DB::Query($sql);
// SQL: SELECT * FROM orders WHERE sales_rep = 'jsmith'

Example 4 – Number without quotes = number

$sql = DB::PrepareSQL("SELECT * FROM products WHERE price > :1", 100);
// SQL: SELECT * FROM products WHERE price > 100

Example 5 – Text without quotes → converted to 0 (safety)

$sql = DB::PrepareSQL("SELECT * FROM products WHERE price > :1", "abc");
// SQL: SELECT * FROM products WHERE price > 0
// Non‑numeric values in unquoted placeholders are converted to 0

DBLookup – Single Value Lookup

DB::DBLookup($sql, ...$args) combines PrepareSQL with a scalar fetch. Returns the first value of the first row, or null.

Example 1 – Look up a ZIP code

$zip = DB::DBLookup("SELECT zip FROM users WHERE userid = :1", 25);
echo $zip;  // e.g. "90210"

Example 2 – Count records

$count = DB::DBLookup("SELECT COUNT(*) FROM orders WHERE status = ':1'", 'pending');
echo "Pending orders: $count";

Example 3 – SUM / aggregate

$total = DB::DBLookup("SELECT SUM(amount) FROM payments WHERE user_id = :1", 42);
echo "Total paid: \${$total}";

Example 4 – Check if a record exists

$exists = DB::DBLookup("SELECT 1 FROM users WHERE email = ':1'", $_POST['email']);
if ($exists) {
    echo "Email already registered.";
}

FetchOne – Scalar Helper

DB::FetchOne($sql, $params) runs a query with bound parameters and returns the first column of the first row.

Example

$name = DB::FetchOne('SELECT name FROM users WHERE id = ?', [$userId]);
echo $name;  // e.g. "John Smith"

FetchAll – Multi‑row Helper

DB::FetchAll($sql, $params) runs a query with bound parameters and returns all rows as an array.

Example

$users = DB::FetchAll('SELECT id, name, email FROM users WHERE active = ?', [1]);
foreach ($users as $user) {
    echo "{$user['name']} ({$user['email']})
";
}

QueryResult Object

Returned by DB::Select(), DB::Query(). Provides iteration methods.

Example – Iterating with fetchAssoc

$rs = DB::Query('SELECT id, name, email FROM users');
while ($row = $rs->fetchAssoc()) {
    echo "<tr><td>{$row['id']}</td><td>{$row['name']}</td></tr>";
}

Example – Getting a single scalar

$rs = DB::Query('SELECT COUNT(*) FROM orders');
echo $rs->value();  // e.g. 1042

Example – Dump all rows at once

$users = DB::Select('users', ['active' => 1])->fetchAll();
print_r($users);

Working with Multiple Connections

Example 1 – Switch between two projects

// Query from the primary project
DB::Init('shop');
$orders = DB::Select('orders', ['status' => 'new']);

// Now fetch inventory from a second database
DB::SetConnection('warehouse');
$stock  = DB::Select('products', ['qty < 10']);

// Return to the primary project
DB::SetConnection('shop');
DB::Update('orders', ['status' => 'reviewed'], ['id' => 5]);

Example 2 – Switch back to the central database

DB::SetConnection('');  // or: DB::SetConnection('default');
$users = DB::Select('users', ['role' => 'developer']);

Example 3 – Insert into two databases in one operation

// Insert into primary project
$orderId = DB::Insert('orders', ['customer_id' => 10, 'total' => 99.95]);

// Switch and insert into audit database
DB::SetConnection('audit');
DB::Insert('order_log', ['order_id' => $orderId, 'action' => 'created', 'at' => date('Y-m-d H:i:s')]);

// Return to primary
DB::SetConnection('shop');

Error Handling

All DB methods return false on failure. Call DB::LastError() to retrieve the error message.

Example 1 – Check for errors

$rs = DB::Select('non_existent_table');
if ($rs === false) {
    die('Database error: ' . DB::LastError());
}

Example 2 – Insert with error logging

$id = DB::Insert('orders', ['customer_id' => 5, 'total' => 199.99]);
if ($id === false) {
    error_log('Failed to insert order: ' . DB::LastError());
    echo 'Something went wrong. Please try again.';
} else {
    echo "Order #$id placed!";
}

Example 3 – Try‑catch on Exec (DDL failures throw)

try {
    DB::Exec("ALTER TABLE users DROP COLUMN phone");
} catch (\Exception $e) {
    echo "Column may not exist: " . $e->getMessage();
}

TestConnection – Verify a Database Connection

DB::TestConnection($host, $port, $dbName, $user, $pass) tests whether a database is reachable and the credentials are valid. It does not register a new connection in the registry. Returns true on success, false on failure. Use DB::LastError() to get the error message.

Example 1 – Test during project creation

$host = '127.0.0.1';
$port = '3306';
$dbName = 'my_new_project';
$user = 'root';
$pass = 'secret';

if (DB::TestConnection($host, $port, $dbName, $user, $pass)) {
    echo "Connection works – you can create the project.";
} else {
    echo "Connection failed: " . DB::LastError();
}

Example 2 – AJAX test connection endpoint

header('Content-Type: application/json');
$ok = DB::TestConnection(
    $_POST['host'],
    $_POST['port'],
    $_POST['dbname'],
    $_POST['user'],
    $_POST['pass']
);
echo json_encode([
    'success' => $ok,
    'message' => $ok ? 'Connection successful.' : DB::LastError()
]);

DropDatabase – Delete a Database

DB::DropDatabase($host, $port, $dbName, $user, $pass) permanently deletes an entire database. This is a destructive operation – use with caution, usually when deleting a project. Throws a \PDOException on failure.

Example 1 – Drop the database of a deleted project

try {
    DB::DropDatabase(
        $project['db_host'],
        $project['db_port'],
        $project['db_name'],
        $project['db_user'],
        $project['db_pass']
    );
    echo "Database dropped.";
} catch (\PDOException $e) {
    error_log('Could not drop database: ' . $e->getMessage());
    // Continue with project deletion even if the DB drop fails
}

Example 2 – Combined with project deletion

if ($deleteDbCheckboxChecked) {
    try {
        DB::DropDatabase('127.0.0.1', '3306', 'old_project_db', 'root', '');
    } catch (\PDOException $e) {
        // log and move on
    }
}
// Then delete the project directory and the central DB entry

🔗 Related Help Topics

Security API Email API SMS API