Database Connections

Every page that uses the database needs a connection. Write the connection code once, in a file that you include everywhere.

db.php

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

<?php

declare(strict_types=1);

$host = getenv("DB_HOST") ?: "127.0.0.1";
$name = getenv("DB_NAME") ?: "shop";
$user = getenv("DB_USER") ?: "shop_user";
$pass = getenv("DB_PASS") ?: "";

$dsn = "mysql:host=$host;dbname=$name;charset=utf8mb4";

$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,
];

try {
    return new PDO($dsn, $user, $pass, $options);
} catch (PDOException $e) {
    error_log("Database connection failed: " . $e->getMessage());
    http_response_code(500);
    exit("The site is having trouble right now. Please try again later.");
}

Use it from any page:

1
2
3
4

<?php
$pdo = require __DIR__ . "/db.php";

echo get_class($pdo);

1

PDO

require returns the value of the return statement in the included file, so $pdo receives the PDO object.

The DSN

1

mysql:host=127.0.0.1;dbname=shop;charset=utf8mb4

The three options

PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION Errors become PDOExceptions, which you can catch. Without this, PDO fails quietly in some situations, and your code carries on with missing data.

PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC Rows come back as associative arrays ($row["name"]). Without it, you get each column twice (by name and by number).

PDO::ATTR_EMULATE_PREPARES => false Uses the database’s real prepared statements instead of PHP simulating them. It is a bit safer, and numbers come back as int and float (but DECIMAL columns still come back as strings, to stay exact).

Credentials from the environment

The username and password come from environment variables with a local fallback. Never write real production passwords into code. See Environment Variables.

Where to put the file

Keep db.php outside the public web folder, or at least make sure the web server never shows its source. If PHP ever stops running on the server for some reason, a file inside the web folder could be downloaded as plain text, with your password in it.

One connection per request

Create the connection once per page and pass it around. Do not call new PDO(...) in every function, because each connection takes time.

1
2
3
4
5

<?php
$pdo = require __DIR__ . "/db.php";

$users = new UserRepository($pdo);       // pass it in
$posts = new PostRepository($pdo);

That is dependency injection in practice.

Persistent connections

PDO::ATTR_PERSISTENT => true keeps connections open between requests. It can help on busy servers but causes surprises (leftover transactions, session state). Do not turn it on unless you know why.

Closing the connection

PHP closes the connection at the end of the request. To close it earlier, set the variable to null: $pdo = null;.

Testing without MySQL

For tests, you can point the same code at SQLite by changing the DSN: new PDO("sqlite::memory:"). That is one benefit of PDO.