← All documentation
Topics

- Working with data
- Designer components
- Field controls
- Search & filtering
- Calendars
- PDF & printing
- Email
- SMS
- Notifications
- Security
- Helper functions
- JavaScript API
- Performance

# 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).

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 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`).

Important: The third parameter (WHERE) is mandatory. Without it, no rows are updated.

### 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.";

Tip: Use `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)` 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

Golden rule: If the parameter should be treated as text, wrap `:1` in single quotes. If it should be a number, do not quote it.

## 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 – 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()
    ]);

Tip: This method uses a temporary PDO connection with a short timeout, so it never interferes with your current active connection.

## 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

Warning: `DB::DropDatabase` drops the database immediately. There is no undo. Always confirm with the user first.

### 🔗 Related Help Topics

Security API Email API SMS API