PHP PDO Connection and Query Helper

<?php
// db.php
function db(): PDO {
    static $pdo = null;
    if ($pdo === null) {
        $dsn = sprintf('mysql:host=%s;dbname=%s;charset=utf8mb4', getenv('DB_HOST') ?: 'localhost', getenv('DB_NAME'));
        $pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASS'), [
            PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION, // errors throw instead of failing silently
            PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
            PDO::ATTR_EMULATE_PREPARES   => false,                  // real server-side prepared statements
        ]);
    }
    return $pdo;
}

function query(string $sql, array $params = []): PDOStatement {
    $stmt = db()->prepare($sql);
    $stmt->execute($params);
    return $stmt;
}

// Usage
$shop  = query('SELECT * FROM shops WHERE id = ?', [$id])->fetch();
$total = query('SELECT SUM(amount) FROM transactions WHERE shop_id = ? AND date BETWEEN ? AND ?', [$id, $from, $to])->fetchColumn();

Credentials come from environment variables, so they never end up in the repository. utf8mb4 stores every Unicode character, including Arabic text and emoji.

Keep reading