In PHP, how do you open a PDO connection to MySQL, and what should its options array set?
answer
- driver prefix, then key=value pairs
- charset=utf8mb4 inside the DSN
- ERRMODE_EXCEPTION, default since 8.0
- FETCH_ASSOC as the default fetch mode
- constructor always throws PDOException
basics
~10 sCall new PDO($dsn, $user, $password, $options) with a DSN such as mysql:host=db;dbname=clinic;charset=utf8mb4. Set PDO::ATTR_ERRMODE to ERRMODE_EXCEPTION and PDO::ATTR_DEFAULT_FETCH_MODE to FETCH_ASSOC; a failed connection throws PDOException.
solid answer
~40 sThe constructor takes a **DSN**, a user name, a password and an **options array**: `new PDO('mysql:host=db;port=3306;dbname=clinic;charset=utf8mb4', $user, $password, $options)`. The DSN names the driver before the colon and passes driver settings as `key=value` pairs; for MySQL, `charset=utf8mb4` belongs there so the connection speaks full UTF-8 from the first query. In the options I set `PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION`, which has been the default since PHP 8.0 but documents the intent, and `PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC`, because the default `FETCH_BOTH` duplicates every column. If the server is unreachable or rejects the credentials, the constructor throws `PDOException` whatever the error mode; I log it and show a generic error, never the message itself.
code
php · 18 lines<?php
declare(strict_types=1);
$dsn = sprintf(
'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4',
getenv('DB_HOST'), (int) getenv('DB_PORT'), getenv('DB_NAME'),
);
try {
$pdo = new PDO($dsn, getenv('DB_USER'), getenv('DB_PASSWORD'), [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);
} catch (PDOException $e) {
error_log('DB connect failed: ' . $e->getMessage());
http_response_code(503);
exit('The booking service is temporarily unavailable.');
}go deeper
Know the four constructor arguments, a MySQL DSN with charset=utf8mb4, and that a failed connection throws PDOException that you catch and log.
Explain why ERRMODE_EXCEPTION and a default fetch mode go in the options array, and why the constructor throws regardless of the error mode.
Keep credentials out of code and responses, share one connection per request, and read driver errors such as could not find driver correctly.
Standardise connection creation in one factory so error mode, charset, timeouts and fetch defaults are consistent across every service that touches the database.
## The constructor PDO (PHP Data Objects) is PHP's database-neutral access layer. A connection is an object: ```php $pdo = new PDO(string $dsn, ?string $username = null, ?string $password = null, ?array $options = null); ``` The connection opens immediately, stays open while the object is referenced, and closes when the last reference goes away or the request ends. Most applications create **one PDO object per request** and pass it to whatever needs it, rather than connecting inside every function. ## The DSN A **DSN** (data source name) is the driver name, a colon, and driver-specific settings separated by semicolons. For a clinic-booking app on MySQL: ``` mysql:host=db.internal;port=3306;dbname=clinic;charset=utf8mb4 ``` | Key | Meaning | |---|---| | `host`, `port` | where the server listens | | `dbname` | the database to select | | `charset` | the connection character set; use `utf8mb4` for full UTF-8 | | `unix_socket` | a socket path, instead of host and port | Setting the character set in the DSN matters: the connection encoding is negotiated at connect time, and PHP-side settings such as `default_charset` or `mb_internal_encoding()` do not change what the database connection uses. ## The options array The fourth argument is a map of attribute constants to values, applied as the connection is created: - **`PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION`**: failed statements throw `PDOException`. This is the default since PHP 8.0, when it replaced the old "silent" mode, but writing it down makes the contract visible to readers and to code that may run under a different setup. - **`PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC`**: rows come back as arrays keyed by column name. The built-in default, `PDO::FETCH_BOTH`, returns every column twice, by name and by position. - **`PDO::ATTR_TIMEOUT`**: a timeout in seconds, whose exact meaning is driver-dependent. - Driver-specific options, such as the MySQL driver's `Pdo\Mysql::ATTR_INIT_COMMAND`, which runs one SQL statement right after connecting. Two related options have their own topics: whether prepared statements are emulated, and persistent connections. ## When the connection fails 1. The constructor **always** throws `PDOException` on failure, whatever `ATTR_ERRMODE` says, because there is no object yet on which to report an error. 2. The message includes the driver's error text, which can reveal host names or user names, so it belongs in a log, not in the HTTP response. 3. The password parameter is marked `#[\SensitiveParameter]`, so it is replaced in stack traces of an uncaught exception, but the DSN and the user name are not. 4. `could not find driver` means the PDO driver extension for that database (here `pdo_mysql`) is not loaded, which is an installation problem rather than a network one. ## Checking what you connected to A few read-only attributes help when a connection behaves unexpectedly: - `$pdo->getAttribute(PDO::ATTR_DRIVER_NAME)` returns the driver in use, for example `'mysql'`. - `$pdo->getAttribute(PDO::ATTR_SERVER_VERSION)` returns the server's version string. - `$pdo->getAttribute(PDO::ATTR_ERRMODE)` and `PDO::ATTR_DEFAULT_FETCH_MODE` confirm the options actually took effect. - `PDO::getAvailableDrivers()` lists the drivers compiled into this PHP. ## Mistakes that show up in code review 1. Leaving the charset out of the DSN, then seeing mangled accented names in patient records. 2. Wrapping the constructor in `try`/`catch` and swallowing the exception, so later code runs with an undefined `$pdo`. 3. Printing `$e->getMessage()` in the response, which leaks host and user names. 4. Creating a second connection deep inside a helper, so a request suddenly holds two database connections. ## Where credentials come from Never hard-code them in the repository. Read them from the environment or from a configuration file outside the web root, and build the DSN from those values. How configuration is loaded is a separate topic; what matters here is that the PDO call receives them as plain strings. ## A checklist interviewers listen for - DSN with the right driver prefix and `charset=utf8mb4` for MySQL. - Exception error mode, stated explicitly. - A default fetch mode other than `FETCH_BOTH`. - A `catch` for `PDOException` at connection time that logs and fails cleanly. - One connection per request, shared, not opened repeatedly.
- Why set ERRMODE_EXCEPTION explicitly when it is already the default in PHP 8?It documents the contract the code relies on, so a reader sees that failures throw rather than return `false`. It also keeps behaviour the same if the connection is created by code written for older PHP or reconfigured with `setAttribute()` elsewhere. It costs one line.
- What does the PDOException message 'could not find driver' tell you?PHP has no PDO driver loaded for the prefix in the DSN, for example `pdo_mysql` or `pdo_pgsql` is missing from the build or its ini is not enabled. `PDO::getAvailableDrivers()` lists the drivers this PHP actually has. It is an installation issue, not a credential or network problem.
saying these in an interview costs you the question
- new PDO() returns false when the database is unreachable
- The connection charset is set by php.ini's default_charset
- PDO's default fetch mode already returns name-keyed arrays
- Echoing $e->getMessage() to the user is fine for connection errors
- Open a new PDO connection inside every function that runs a query