Connecting to MySQL from PHP

This chapter is about the MySQL side of PHP applications. The PHP basics of PDO are in the PHP course (What is PDO, Database Connections). Here you will see what is specific to MySQL: the right connection options, real MySQL types, error codes, transactions and deadlocks.

The connection file

Keep the connection in one file that every page includes:

1
2
3
4
5
6
7
8
9
10
11

<?php
$host = getenv('DB_HOST') ?: '127.0.0.1';
$port = getenv('DB_PORT') ?: '3306';
$name = getenv('DB_NAME') ?: 'shop';
$dsn  = "mysql:host=$host;port=$port;dbname=$name;charset=utf8mb4";

return new PDO($dsn, getenv('DB_USER') ?: 'shop_app', getenv('DB_PASS') ?: '', [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
]);

Every other lesson in this chapter starts with $pdo = require __DIR__ . '/db.php';. In these lessons, the examples run against a real MySQL 8.4 server and a small phpl database that is reset before each example (the user name and password come from the environment, as in your own setup).

The DSN, part by part

Why charset=utf8mb4 matters

Without it, the connection can use the server’s default or an old character set, and text with emoji or many scripts turns into ? or gives an “Incorrect string value” error (Character Sets). Check what you got:

1
2
3
4

<?php
$pdo = require __DIR__ . '/db.php';
$row = $pdo->query('SELECT @@character_set_client AS client, @@character_set_connection AS conn, @@character_set_results AS results')->fetch();
print_r($row);

1
2
3
4
5
6

Array
(
    [client] => utf8mb4
    [conn] => utf8mb4
    [results] => utf8mb4
)

The three options you always want

Check that the options are active:

1
2
3
4
5

<?php
$pdo = require __DIR__ . '/db.php';
var_dump($pdo->getAttribute(PDO::ATTR_ERRMODE) === PDO::ERRMODE_EXCEPTION);
var_dump($pdo->getAttribute(PDO::ATTR_EMULATE_PREPARES));
echo $pdo->getAttribute(PDO::ATTR_DRIVER_NAME), "\n";

1
2
3

bool(true)
int(0)
mysql

Credentials: not in the code

The file reads the user and the password from environment variables (getenv), as in Database Users for Applications. Set them in the web server or PHP-FPM pool configuration, in a systemd unit, or in your host’s control panel. Do not commit them. The application account should be shop_app (limited privileges), never root.

Handle a failed connection

1
2
3
4
5
6
7

<?php
try {
    $pdo = new PDO('mysql:host=127.0.0.1;port=1;dbname=shop;charset=utf8mb4', 'shop_app', 'wrong', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_TIMEOUT => 2]);
} catch (PDOException $e) {
    error_log('DB connection failed: ' . $e->getMessage());   // full detail goes to the server log
    echo 'Database error code: ', $e->getCode(), "\n";        // the user sees a short message
}

1

Database error code: 2002

Never show $e->getMessage() to visitors. It can contain host names and user names. Log it, and show a general “Something went wrong” page. (2002 means the server could not be reached. 1045 means a wrong user or password. 1049 means an unknown database. 1130 means the host is not allowed.)

PHP also has a persistent connection option (PDO::ATTR_PERSISTENT). It avoids reconnect cost, but it can leave session state (variables, open transactions, locks) behind for the next request. Skip it unless you measured a connection problem.

Settings per connection

You can run settings right after connecting, with PDO::MYSQL_ATTR_INIT_COMMAND:

1
2
3
4
5
6
7

<?php
$pdo = new PDO('mysql:host=127.0.0.1;port=' . (getenv('DB_PORT') ?: 3306) . ';dbname=phpl;charset=utf8mb4', 'root', '', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_INIT_COMMAND => "SET time_zone = '+00:00', sql_mode = 'STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION'",
]);
echo $pdo->query('SELECT @@session.time_zone')->fetchColumn(), "\n";
echo $pdo->query('SELECT @@session.sql_mode')->fetchColumn(), "\n";

1
2

+00:00
STRICT_ALL_TABLES,NO_ENGINE_SUBSTITUTION

(This example connects as the test server’s root, which you should never do in an application.) Keep STRICT_ALL_TABLES or STRICT_TRANS_TABLES on, so that wrong data gives an error instead of being silently changed (SQL Modes). Using UTC for the connection time zone and converting to the user’s zone in PHP avoids daylight-saving surprises.

Encrypted connection

For a server on another machine, enable TLS (Encrypted Connections):

1
2
3
4
5
6
7

<?php
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/mysql-ca.pem',
    PDO::MYSQL_ATTR_SSL_VERIFY_SERVER_CERT => true,
];
$pdo = new PDO($dsn, $user, $password, $options);

Authentication plugin errors

MySQL 8 uses caching_sha2_password. PHP 7.4 and later with mysqlnd supports it. If you see The server requested authentication method unknown to the client, your PHP or driver is too old. Upgrade PHP. Do not switch the account to the old mysql_native_password, which is off in MySQL 8.4 and gone in 9.0 (Creating Users).