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
- Recipes – real-world one-liners
- 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
- Error Handling
Recipes – real-world one-liners
Common jobs, the short way. Each shows the goal, the code, and the SQL it runs. Two toolsets are
in scope everywhere: the quick façade (dbAll / dbRow / dbValue / dbExec) and the
permission-aware pp()
API. Drop to DB:: when you want its exact shape.
Read
// One value — the price of car #50
$price = dbValue('SELECT price FROM cars WHERE id = ?', [50], 0);
// One row — a whole customer
$cust = dbRow('SELECT * FROM customers WHERE id = ?', [$id]);
echo $cust['email'];
// Many rows — all open orders, newest first
foreach (dbAll('SELECT * FROM orders WHERE status = ? ORDER BY created DESC', ['open']) as $o) {
echo $o['ref'];
}
// The same read, permission-aware + row-security scoped, via pp()
$open = pp()->table('orders')->where('status', 'open')->orderBy('created', 'desc')->get();
Write
// Update car #50 (DB:: — data + key arrays, exactly like the classic form)
DB::Update('cars', ['make' => 'Toyota', 'model' => 'RAV4', 'price' => 16000], ['id' => 50]);
// Insert and get the new id
$id = DB::Insert('cars', ['make' => 'Kia', 'model' => 'Niro', 'price' => 21000]);
// Insert the permission-gated way (needs 'add' rights)
$id = pp()->insert('cars', ['make' => 'Kia', 'model' => 'Niro', 'price' => 21000]);
// Give every open Toyota order a discount — one bulk statement
$n = pp()->table('orders')->where('status', 'open')->where('make', 'Toyota')
->update(['discount' => 10]);
Count, sum, exists
$toyotas = dbValue('SELECT COUNT(*) FROM cars WHERE make = ?', ['Toyota'], 0);
$revenue = pp()->table('orders')->where('status', 'paid')->sum('total');
$hasDupes = pp()->table('users')->where('email', $email)->exists();
A tiny counter
// Bump a view counter atomically (no read-then-write race)
pp()->table('posts')->where('id', $id)->increment('views');
Two writes that must both succeed
// Create an order and its first line item — or neither
pp()->transaction(function ($pp) {
$orderId = $pp->insert('orders', ['customer_id' => $pp->param('cid'), 'created' => now()]);
$pp->insert('order_items', ['order_id' => $orderId, 'sku' => 'A1', 'qty' => 1]);
});
Filter by the person who's logged in
$mine = pp()->table('tickets')->where('assigned_to', pp()->userId())->orderBy('due')->get();
$mineToo = dbAll('SELECT * FROM tickets WHERE assigned_to = ?', [pp()->userId()]);
// or with an SQL variable — great inside a Designer WHERE tab / lookup:
$mine3 = dbAll(DB::PrepareSQL('SELECT * FROM tickets WHERE assigned_to = :userid'));
Then tell the browser about it — live
// In a Custom Button's Server stage: do the work, and drive the page from PHP
$n = pp()->table('orders')->where('status', 'pending')->update(['status' => 'shipped']);
pp()->toast('success', $n . ' order(s) shipped')->reloadGrid();
Connecting to a Database
Before using any DB method, you must initialise a connection.
Your app's database
// The app's own database is connected automatically on every page —
// no manual call needed. Calling it again is harmless:
DB::Init(); // connects to the app's database (set up by the installer)
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 app's main connection
DB::SetConnection('default');
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).
| Engine | PDO driver | Default port | Notes |
|---|---|---|---|
| MySQL | pdo_mysql | 3306 | The default for most PHP hosting. |
| MariaDB | pdo_mysql | 3306 | Wire-compatible fork of MySQL. |
| TiDB | pdo_mysql | 4000 | Distributed, MySQL-compatible SQL — no extra extension needed. |
| PostgreSQL | pdo_pgsql | 5432 | Rich types, schemas, JSONB. |
| CockroachDB | pdo_pgsql | 26257 | Distributed, PostgreSQL-compatible SQL — no extra extension needed. |
| SQLite | pdo_sqlite | — | Single-file, zero-config. |
| SQL Server | pdo_sqlsrv / pdo_dblib | 1433 | Microsoft SQL Server. |
| Oracle | pdo_oci | 1521 | Enterprise Oracle Database. |
// 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 an array of rows (each an associative array),
or false on failure — so you foreach over it. Every value in a WHERE array is safely bound;
you never build SQL by hand. (Need a streaming recordset loop? Use DB::Query().)
Example 1 – Fetch all Toyota RAV4s (WHERE array)
// Get every car where make = Toyota AND model = RAV4
foreach (DB::Select('cars', ['make' => 'Toyota', 'model' => 'RAV4']) as $row) {
echo $row['id'] . ' — ' . $row['price'];
}
// SQL: SELECT * FROM cars WHERE make = 'Toyota' AND model = 'RAV4'
Example 2 – Cheaper than £20,000 (WHERE as a raw string)
foreach (DB::Select('cars', 'price < 20000') as $row) {
echo $row['id'] . ': ' . $row['make'];
}
// SQL: SELECT * FROM cars WHERE price < 20000
Example 3 – Sort by one field
$cars = DB::Select('cars', [], 'name ASC');
// SQL: SELECT * FROM cars ORDER BY name ASC
Example 4 – Sort by two fields, mixed directions
$cars = DB::Select('cars', [], ['make ASC', 'price DESC']);
// SQL: SELECT * FROM cars ORDER BY make ASC, price DESC
Each sort entry is a plain string like 'price DESC'. For a builder that takes the
direction as a separate argument, see pp()->table()->orderBy('price', 'desc').
Example 5 – Everything (no WHERE, no sort)
$all = DB::Select('employees');
// SQL: SELECT * FROM employees
Example 6 – Just the count
$howMany = count(DB::Select('cars', ['make' => 'Toyota']));
// (or, cheaper, let the database count — see dbValue below)
$howMany = dbValue('SELECT COUNT(*) FROM cars WHERE make = ?', ['Toyota'], 0);
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]);
That reads the old value first. For a race-safe counter that bumps the column in
the database itself, use pp()->table('products')->where('id', $productId)->increment('view_count').
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.";
DB::Exec() for one‑off DDL. For safe INSERT/UPDATE/DELETE with user data, prefer DB::Insert(), DB::Update(), DB::Delete().PrepareSQL – Safe Variable Substitution
DB::PrepareSQL($sql, ...$args) fills positional placeholders :1, :2, … with your
values and returns a safe SQL string. It decides quoting by the value's type: a string is escaped and
quoted for you, a number is inserted as-is. Never add your own quotes around a placeholder — it would
double them.
It also expands SQL variables
like :userid, :username, :user.field and :session.key — so a query can
depend on the logged-in user with no extra code.
? query — dbAll(), dbRow(),
dbValue(), or DB::Query($sql, [$params]) — no quoting rules to remember. Reach for
PrepareSQL only when you must build a SQL string (e.g. to log it).
Example 1 – A number (inserted raw)
$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 – A string (auto-quoted — do NOT add quotes)
$sql = DB::PrepareSQL(
"SELECT * FROM customers WHERE age > :1 AND last_name = :2",
20, // number → 20
"O'Rourke" // string → 'O''Rourke' (escaped + quoted for you)
);
// SQL: SELECT * FROM customers WHERE age > 20 AND last_name = 'O''Rourke'
// ⚠ Writing last_name = ':2' would produce ''O''Rourke'' — broken. Leave it as :2.
Example 3 – The same thing with bound parameters (recommended)
$rows = dbAll(
'SELECT * FROM customers WHERE age > ? AND last_name = ?',
[20, "O'Rourke"]
);
// Placeholders are ?; values go in the array; quoting is automatic. Nothing to remember.
Example 4 – Filter by the logged-in user
// Three good ways — pick whichever reads best:
// a) SQL variable (expanded to the logged-in name/id, quoted safely)
$mine = dbAll(DB::PrepareSQL("SELECT * FROM orders WHERE sales_rep = :username"));
// b) a plain bound parameter
$mine = dbAll('SELECT * FROM orders WHERE sales_rep = ?', [pp()->username()]);
// c) permission-aware, via the query builder
$mine = pp()->table('orders')->where('sales_rep', pp()->username())->get();
See SQL Variables
for the full list (:userid, :user.field, :session.key …) and where they work.
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.
| Method | Description | Example |
|---|---|---|
fetchAssoc() |
Next row as associative array, or null when exhausted. |
$row = $rs->fetchAssoc(); echo $row['name']; |
fetchNumeric() |
Next row as indexed array, or null. |
$row = $rs->fetchNumeric(); echo $row[0]; |
value($field) |
Returns a single value from the first row. | $zip = $rs->value('zip'); |
fetchAll() |
Returns all remaining rows as array. | $rows = $rs->fetchAll(); |
rowCount() |
Number of affected rows (for UPDATE/DELETE). | echo $rs->rowCount(); |
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 – Read from a second database, then come back
// The app's own database is the 'default' connection
$orders = DB::Select('orders', ['status' => 'new']);
// Fetch inventory from a second database you registered (DB::AddConnection)
DB::SetConnection('warehouse');
$stock = DB::Select('products', ['qty < 10']);
// Return to the app's database
DB::SetConnection('default');
DB::Update('orders', ['status' => 'reviewed'], ['id' => 5]);
Example 2 – Run something on another connection and switch back automatically
// DB::On() runs the callback on that connection and always restores the previous one
$lowStock = DB::On('warehouse', fn () => DB::FetchAll('SELECT sku, qty FROM products WHERE qty < ?', [10]));
Example 3 – Insert into two databases in one operation
// Insert into the app's database
$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 the app's database
DB::SetConnection('default');
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 – Check credentials before you save them
$host = '127.0.0.1';
$port = '3306';
$dbName = 'reports';
$user = 'root';
$pass = 'secret';
if (DB::TestConnection($host, $port, $dbName, $user, $pass)) {
echo "Connection works – the settings can be saved.";
} else {
echo "Connection failed: " . DB::LastError();
}
Example 2 – AJAX test connection endpoint
// Only signed-in administrators, and only from your own pages (CSRF) — an open endpoint
// that tries any host and password would let strangers probe your network.
\PpPhp\Core\Security::RequireAuth();
\PpPhp\Core\Security::RequireCsrf();
if (!\PpPhp\Core\Security::IsAdmin()) { http_response_code(403); exit; }
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()
]);