
<h1>Database API – <code>DB</code> Class</h1>
<p>The <strong>DB</strong> 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.</p>

<h2>Contents</h2>
<ul>
    <li><a href="#recipes"><strong>Recipes – real-world one-liners</strong></a></li>
    <li><a href="#connect">Connecting to a Database</a></li>
    <li><a href="#select">Select – Retrieving Data</a></li>
    <li><a href="#insert">Insert – Adding Records</a></li>
    <li><a href="#update">Update – Modifying Records</a></li>
    <li><a href="#delete">Delete – Removing Records</a></li>
    <li><a href="#query">Query – Raw SELECT Queries</a></li>
    <li><a href="#exec">Exec – DDL / Non‑returning Queries</a></li>
    <li><a href="#preparesql">PrepareSQL – Safe Variable Substitution</a></li>
    <li><a href="#dblookup">DBLookup – Single Value Lookup</a></li>
    <li><a href="#fetchone">FetchOne – Scalar Helper</a></li>
    <li><a href="#fetchall">FetchAll – Multi‑row Helper</a></li>
    <li><a href="#queryresult">QueryResult Object</a></li>
    <li><a href="#multiconn">Working with Multiple Connections</a></li>
    <li><a href="#testconnection">TestConnection – Verify a Database Connection</a></li>
    <li><a href="#dropdatabase">DropDatabase – Delete a Database</a></li>
    <li><a href="#error">Error Handling</a></li>
</ul>

<!-- =========================================== -->
<h2 id="recipes">Recipes – real-world one-liners</h2>
<p class="lead">Common jobs, the short way. Each shows the goal, the code, and the SQL it runs. Two toolsets are
in scope everywhere: the quick <strong>façade</strong> (<code>dbAll / dbRow / dbValue / dbExec</code>) and the
permission-aware <a href="<?= htmlspecialchars(rtrim((string)($baseUrl ?? ''), '/')) ?>/help/pp_server_api"><strong><code>pp()</code></strong></a>
API. Drop to <code>DB::</code> when you want its exact shape.</p>

<h3>Read</h3>
<pre><code>// 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();</code></pre>

<h3>Write</h3>
<pre><code>// 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]);</code></pre>

<h3>Count, sum, exists</h3>
<pre><code>$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();</code></pre>

<h3>A tiny counter</h3>
<pre><code>// Bump a view counter atomically (no read-then-write race)
pp()->table('posts')->where('id', $id)->increment('views');</code></pre>

<h3>Two writes that must both succeed</h3>
<pre><code>// 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]);
});</code></pre>

<h3>Filter by the person who's logged in</h3>
<pre><code>$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'));</code></pre>

<h3>Then tell the browser about it — live</h3>
<pre><code>// 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();</code></pre>

<!-- =========================================== -->
<h2 id="connect">Connecting to a Database</h2>
<p>Before using any DB method, you must initialise a connection.</p>

<h3>Central (framework) database</h3>
<pre><code>// 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</code></pre>

<h3>Project database</h3>
<pre><code>// Connects to the database belonging to project "my‑shop"
DB::Init('my-shop');</code></pre>

<h3>Multiple connections (Enterprise)</h3>
<pre><code>// 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');</code></pre>

<h3>Supported databases</h3>
<p>The same query API runs on every engine below — <?= htmlspecialchars(\PpPhp\Core\Brand::name()) ?> generates dialect-correct SQL for each. A project's engine is chosen in its connection profile; you rarely write a DSN by hand, but <code>AddConnection()</code> accepts one for any of them (as long as the matching PDO driver is installed).</p>
<table>
  <thead><tr><th>Engine</th><th>PDO driver</th><th>Default port</th><th>Notes</th></tr></thead>
  <tbody>
    <tr><td>MySQL</td><td>pdo_mysql</td><td>3306</td><td>The default for most PHP hosting.</td></tr>
    <tr><td>MariaDB</td><td>pdo_mysql</td><td>3306</td><td>Wire-compatible fork of MySQL.</td></tr>
    <tr><td>TiDB</td><td>pdo_mysql</td><td>4000</td><td>Distributed, MySQL-compatible SQL — no extra extension needed.</td></tr>
    <tr><td>PostgreSQL</td><td>pdo_pgsql</td><td>5432</td><td>Rich types, schemas, JSONB.</td></tr>
    <tr><td>CockroachDB</td><td>pdo_pgsql</td><td>26257</td><td>Distributed, PostgreSQL-compatible SQL — no extra extension needed.</td></tr>
    <tr><td>SQLite</td><td>pdo_sqlite</td><td>—</td><td>Single-file, zero-config.</td></tr>
    <tr><td>SQL Server</td><td>pdo_sqlsrv / pdo_dblib</td><td>1433</td><td>Microsoft SQL Server.</td></tr>
    <tr><td>Oracle</td><td>pdo_oci</td><td>1521</td><td>Enterprise Oracle Database.</td></tr>
  </tbody>
</table>
<pre><code>// 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');</code></pre>

<!-- =========================================== -->
<h2 id="select">Select – Retrieving Data</h2>
<p><code>DB::Select($table, $where, $sort)</code> returns an <strong>array of rows</strong> (each an associative array),
or <code>false</code> on failure — so you <code>foreach</code> over it. Every value in a WHERE array is safely bound;
you never build SQL by hand. (Need a streaming recordset loop? Use <a href="#query"><code>DB::Query()</code></a>.)</p>

<h3>Example 1 – Fetch all Toyota RAV4s (WHERE array)</h3>
<pre><code>// 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'</code></pre>

<h3>Example 2 – Cheaper than £20,000 (WHERE as a raw string)</h3>
<pre><code>foreach (DB::Select('cars', 'price < 20000') as $row) {
    echo $row['id'] . ': ' . $row['make'];
}
// SQL: SELECT * FROM cars WHERE price < 20000</code></pre>

<h3>Example 3 – Sort by one field</h3>
<pre><code>$cars = DB::Select('cars', [], 'name ASC');
// SQL: SELECT * FROM cars ORDER BY name ASC</code></pre>

<h3>Example 4 – Sort by two fields, mixed directions</h3>
<pre><code>$cars = DB::Select('cars', [], ['make ASC', 'price DESC']);
// SQL: SELECT * FROM cars ORDER BY make ASC, price DESC</code></pre>
<p class="help-note">Each sort entry is a plain string like <code>'price DESC'</code>. For a builder that takes the
direction as a separate argument, see <a href="<?= htmlspecialchars(rtrim((string)($baseUrl ?? ''), '/')) ?>/help/pp_server_api"><code>pp()->table()->orderBy('price', 'desc')</code></a>.</p>

<h3>Example 5 – Everything (no WHERE, no sort)</h3>
<pre><code>$all = DB::Select('employees');
// SQL: SELECT * FROM employees</code></pre>

<h3>Example 6 – Just the count</h3>
<pre><code>$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);</code></pre>

<!-- =========================================== -->
<h2 id="insert">Insert – Adding Records</h2>
<p><code>DB::Insert($table, $data)</code> returns the new auto‑increment ID (or <code>false</code>).</p>

<h3>Example 1 – Simple insert</h3>
<pre><code>$id = DB::Insert('cars', [
    'make'  => 'Toyota',
    'model' => 'RAV4',
    'price' => 16000
]);
echo "New car ID: $id";</code></pre>

<h3>Example 2 – Insert with a date</h3>
<pre><code>$id = DB::Insert('orders', [
    'customer_id' => 42,
    'total'       => 299.95,
    'created_at'  => date('Y-m-d H:i:s')
]);</code></pre>

<h3>Example 3 – Insert from form data</h3>
<pre><code>$id = DB::Insert('contacts', [
    'name'  => $_POST['name'],
    'email' => $_POST['email'],
    'phone' => $_POST['phone']
]);</code></pre>

<h3>Example 4 – Copy a record (AfterAdd event)</h3>
<pre><code>// 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);</code></pre>

<!-- =========================================== -->
<h2 id="update">Update – Modifying Records</h2>
<p><code>DB::Update($table, $data, $where)</code> returns the number of affected rows (or <code>false</code>).</p>
<div class="alert alert-warning"><i class="bi bi-exclamation-triangle"></i> <strong>Important:</strong> The third parameter (WHERE) is mandatory. Without it, no rows are updated.</div>

<h3>Example 1 – Update by primary key (array WHERE)</h3>
<pre><code>$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</code></pre>

<h3>Example 2 – Update by primary key (string WHERE)</h3>
<pre><code>$affected = DB::Update('cars', ['price' => 18000], 'id = 50');
// SQL: UPDATE cars SET price=18000 WHERE id=50</code></pre>

<h3>Example 3 – Update multiple rows</h3>
<pre><code>$affected = DB::Update('orders', ['status' => 'shipped'], 'ship_date < CURDATE()');
echo "$affected order(s) marked as shipped.";</code></pre>

<h3>Example 4 – Update with a counter</h3>
<pre><code>$affected = DB::Update('products', ['view_count' => $currentViews + 1], ['id' => $productId]);</code></pre>
<p class="help-note">That reads the old value first. For a <strong>race-safe</strong> counter that bumps the column in
the database itself, use <a href="<?= htmlspecialchars(rtrim((string)($baseUrl ?? ''), '/')) ?>/help/pp_server_api"><code>pp()->table('products')->where('id', $productId)->increment('view_count')</code></a>.</p>

<!-- =========================================== -->
<h2 id="delete">Delete – Removing Records</h2>
<p><code>DB::Delete($table, $where)</code> returns the number of deleted rows (or <code>false</code>).</p>

<h3>Example 1 – Delete by primary key</h3>
<pre><code>$deleted = DB::Delete('cars', ['id' => 50]);
echo "$deleted row(s) deleted.";</code></pre>

<h3>Example 2 – Delete by raw condition</h3>
<pre><code>$deleted = DB::Delete('sessions', 'expires < NOW()');
echo "$deleted expired session(s) removed.";</code></pre>

<h3>Example 3 – Delete with multiple conditions (array)</h3>
<pre><code>$deleted = DB::Delete('temp_logs', ['user_id' => 5, 'status' => 'archived']);</code></pre>

<!-- =========================================== -->
<h2 id="query">Query – Raw SELECT Queries</h2>
<p><code>DB::Query($sql, $params)</code> runs a raw SQL statement that <strong>returns data</strong>. Returns a <strong>QueryResult</strong> or <code>false</code>.</p>

<h3>Example 1 – Basic SELECT with bound parameters</h3>
<pre><code>$rs = DB::Query('SELECT * FROM cars WHERE make = ? AND price > ?', ['Toyota', 15000]);
while ($row = $rs->fetchAssoc()) {
    echo $row['model'];
}</code></pre>

<h3>Example 2 – JOIN query</h3>
<pre><code>$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']}<br>";
}</code></pre>

<h3>Example 3 – Aggregate query</h3>
<pre><code>$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']}<br>";
}</code></pre>

<h3>Example 4 – Get a single value from a QueryResult</h3>
<pre><code>$rs = DB::Query('SELECT COUNT(*) FROM users WHERE active = ?', [1]);
$total = $rs->value();   // returns first column of first row
echo "Active users: $total";</code></pre>

<!-- =========================================== -->
<h2 id="exec">Exec – DDL / Non‑returning Queries</h2>
<p><code>DB::Exec($sql)</code> runs a statement that <strong>does not return a result set</strong> (INSERT, UPDATE, DELETE, ALTER, TRUNCATE). Returns the number of affected rows.</p>

<h3>Example 1 – Raw INSERT</h3>
<pre><code>$affected = DB::Exec("INSERT INTO log (event, user_id, created_at) VALUES ('login', 42, NOW())");
echo "Rows: $affected";</code></pre>

<h3>Example 2 – Raw UPDATE</h3>
<pre><code>$affected = DB::Exec("UPDATE orders SET status='shipped' WHERE ship_date = CURDATE()");
echo "$affected order(s) shipped today.";</code></pre>

<h3>Example 3 – DDL (ALTER TABLE)</h3>
<pre><code>DB::Exec("ALTER TABLE users ADD COLUMN phone VARCHAR(20) AFTER email");
echo "Column added.";</code></pre>

<h3>Example 4 – TRUNCATE</h3>
<pre><code>DB::Exec("TRUNCATE TABLE temp_import");
echo "Table cleared.";</code></pre>

<div class="alert alert-info"><i class="bi bi-info-circle"></i> <strong>Tip:</strong> Use <code>DB::Exec()</code> for one‑off DDL. For safe INSERT/UPDATE/DELETE with user data, prefer <code>DB::Insert()</code>, <code>DB::Update()</code>, <code>DB::Delete()</code>.</div>

<!-- =========================================== -->
<h2 id="preparesql">PrepareSQL – Safe Variable Substitution</h2>
<p><code>DB::PrepareSQL($sql, ...$args)</code> fills positional placeholders <code>:1</code>, <code>:2</code>, … with your
values and returns a safe SQL string. It decides quoting <strong>by the value's type</strong>: a string is escaped and
quoted for you, a number is inserted as-is. <strong>Never add your own quotes</strong> around a placeholder — it would
double them.</p>
<p>It also expands <a href="<?= htmlspecialchars(rtrim((string)($baseUrl ?? ''), '/')) ?>/help/sql_variables"><strong>SQL variables</strong></a>
like <code>:userid</code>, <code>:username</code>, <code>:user.field</code> and <code>:session.key</code> — so a query can
depend on the logged-in user with no extra code.</p>

<div class="alert alert-warning"><i class="bi bi-exclamation-triangle"></i> <strong>Prefer bound parameters.</strong>
For most code, the clearest and safest option is a bound <code>?</code> query — <code>dbAll()</code>, <code>dbRow()</code>,
<code>dbValue()</code>, or <code>DB::Query($sql, [$params])</code> — no quoting rules to remember. Reach for
<code>PrepareSQL</code> only when you must build a SQL <em>string</em> (e.g. to log it).</p>

<h3>Example 1 – A number (inserted raw)</h3>
<pre><code>$sql = DB::PrepareSQL("SELECT * FROM users WHERE id = :1", $userId);
$rs  = DB::Query($sql);
// If $userId is 25 → SELECT * FROM users WHERE id = 25</code></pre>

<h3>Example 2 – A string (auto-quoted — do NOT add quotes)</h3>
<pre><code>$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.</code></pre>

<h3>Example 3 – The same thing with bound parameters (recommended)</h3>
<pre><code>$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.</code></pre>

<h3>Example 4 – Filter by the logged-in user</h3>
<pre><code>// 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();</code></pre>
<p class="help-note">See <a href="<?= htmlspecialchars(rtrim((string)($baseUrl ?? ''), '/')) ?>/help/sql_variables">SQL Variables</a>
for the full list (<code>:userid</code>, <code>:user.field</code>, <code>:session.key</code> …) and where they work.</p>

<!-- =========================================== -->
<h2 id="dblookup">DBLookup – Single Value Lookup</h2>
<p><code>DB::DBLookup($sql, ...$args)</code> combines <code>PrepareSQL</code> with a scalar fetch. Returns the first value of the first row, or <code>null</code>.</p>

<h3>Example 1 – Look up a ZIP code</h3>
<pre><code>$zip = DB::DBLookup("SELECT zip FROM users WHERE userid = :1", 25);
echo $zip;  // e.g. "90210"</code></pre>

<h3>Example 2 – Count records</h3>
<pre><code>$count = DB::DBLookup("SELECT COUNT(*) FROM orders WHERE status = ':1'", 'pending');
echo "Pending orders: $count";</code></pre>

<h3>Example 3 – SUM / aggregate</h3>
<pre><code>$total = DB::DBLookup("SELECT SUM(amount) FROM payments WHERE user_id = :1", 42);
echo "Total paid: \${$total}";</code></pre>

<h3>Example 4 – Check if a record exists</h3>
<pre><code>$exists = DB::DBLookup("SELECT 1 FROM users WHERE email = ':1'", $_POST['email']);
if ($exists) {
    echo "Email already registered.";
}</code></pre>

<!-- =========================================== -->
<h2 id="fetchone">FetchOne – Scalar Helper</h2>
<p><code>DB::FetchOne($sql, $params)</code> runs a query with bound parameters and returns the first column of the first row.</p>

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

<!-- =========================================== -->
<h2 id="fetchall">FetchAll – Multi‑row Helper</h2>
<p><code>DB::FetchAll($sql, $params)</code> runs a query with bound parameters and returns all rows as an array.</p>

<h3>Example</h3>
<pre><code>$users = DB::FetchAll('SELECT id, name, email FROM users WHERE active = ?', [1]);
foreach ($users as $user) {
    echo "{$user['name']} ({$user['email']})<br>";
}</code></pre>

<!-- =========================================== -->
<h2 id="queryresult">QueryResult Object</h2>
<p>Returned by <code>DB::Select()</code>, <code>DB::Query()</code>. Provides iteration methods.</p>

<table class="table table-sm table-bordered">
<tr><th>Method</th><th>Description</th><th>Example</th></tr>
<tr>
    <td><code>fetchAssoc()</code></td>
    <td>Next row as associative array, or <code>null</code> when exhausted.</td>
    <td><code>$row = $rs->fetchAssoc(); echo $row['name'];</code></td>
</tr>
<tr>
    <td><code>fetchNumeric()</code></td>
    <td>Next row as indexed array, or <code>null</code>.</td>
    <td><code>$row = $rs->fetchNumeric(); echo $row[0];</code></td>
</tr>
<tr>
    <td><code>value($field)</code></td>
    <td>Returns a single value from the first row.</td>
    <td><code>$zip = $rs->value('zip');</code></td>
</tr>
<tr>
    <td><code>fetchAll()</code></td>
    <td>Returns all remaining rows as array.</td>
    <td><code>$rows = $rs->fetchAll();</code></td>
</tr>
<tr>
    <td><code>rowCount()</code></td>
    <td>Number of affected rows (for UPDATE/DELETE).</td>
    <td><code>echo $rs->rowCount();</code></td>
</tr>
</table>

<h3>Example – Iterating with fetchAssoc</h3>
<pre><code>$rs = DB::Query('SELECT id, name, email FROM users');
while ($row = $rs->fetchAssoc()) {
    echo "&lt;tr&gt;&lt;td&gt;{$row['id']}&lt;/td&gt;&lt;td&gt;{$row['name']}&lt;/td&gt;&lt;/tr&gt;";
}</code></pre>

<h3>Example – Getting a single scalar</h3>
<pre><code>$rs = DB::Query('SELECT COUNT(*) FROM orders');
echo $rs->value();  // e.g. 1042</code></pre>

<h3>Example – Dump all rows at once</h3>
<pre><code>$users = DB::Select('users', ['active' => 1])->fetchAll();
print_r($users);</code></pre>

<!-- =========================================== -->
<h2 id="multiconn">Working with Multiple Connections</h2>

<h3>Example 1 – Switch between two projects</h3>
<pre><code>// 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]);</code></pre>

<h3>Example 2 – Switch back to the central database</h3>
<pre><code>DB::SetConnection('');  // or: DB::SetConnection('default');
$users = DB::Select('users', ['role' => 'developer']);</code></pre>

<h3>Example 3 – Insert into two databases in one operation</h3>
<pre><code>// 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');</code></pre>

<!-- =========================================== -->
<h2 id="error">Error Handling</h2>
<p>All DB methods return <code>false</code> on failure. Call <code>DB::LastError()</code> to retrieve the error message.</p>

<h3>Example 1 – Check for errors</h3>
<pre><code>$rs = DB::Select('non_existent_table');
if ($rs === false) {
    die('Database error: ' . DB::LastError());
}</code></pre>

<h3>Example 2 – Insert with error logging</h3>
<pre><code>$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!";
}</code></pre>

<h3>Example 3 – Try‑catch on Exec (DDL failures throw)</h3>
<pre><code>try {
    DB::Exec("ALTER TABLE users DROP COLUMN phone");
} catch (\Exception $e) {
    echo "Column may not exist: " . $e->getMessage();
}</code></pre>


<!-- =========================================== -->
<h2 id="testconnection">TestConnection – Verify a Database Connection</h2>
<p><code>DB::TestConnection($host, $port, $dbName, $user, $pass)</code> tests whether a database is reachable and the credentials are valid. It does <strong>not</strong> register a new connection in the registry. Returns <code>true</code> on success, <code>false</code> on failure. Use <code>DB::LastError()</code> to get the error message.</p>

<h3>Example 1 – Test during project creation</h3>
<pre><code>$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();
}</code></pre>

<h3>Example 2 – AJAX test connection endpoint</h3>
<pre><code>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()
]);</code></pre>

<div class="alert alert-info"><i class="bi bi-info-circle"></i> <strong>Tip:</strong> This method uses a temporary PDO connection with a short timeout, so it never interferes with your current active connection.</div>

<!-- =========================================== -->
<h2 id="dropdatabase">DropDatabase – Delete a Database</h2>
<p><code>DB::DropDatabase($host, $port, $dbName, $user, $pass)</code> permanently deletes an entire database. This is a <strong>destructive operation</strong> – use with caution, usually when deleting a project. Throws a <code>\PDOException</code> on failure.</p>

<h3>Example 1 – Drop the database of a deleted project</h3>
<pre><code>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
}</code></pre>

<h3>Example 2 – Combined with project deletion</h3>
<pre><code>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</code></pre>

<div class="alert alert-danger"><i class="bi bi-exclamation-triangle-fill"></i> <strong>Warning:</strong> <code>DB::DropDatabase</code> drops the database immediately. There is no undo. Always confirm with the user first.</div>

<!-- =========================================== -->


<hr>
<h3>🔗 Related Help Topics</h3>
<p>
    <a href="Security" class="btn btn-sm btn-outline-primary me-1">Security API</a>
    <a href="Email" class="btn btn-sm btn-outline-primary me-1">Email API</a>
    <a href="SMS" class="btn btn-sm btn-outline-primary">SMS API</a>
</p>
