PDO Connect

PDO Connect Connecting With PDO In simple terms: To use a database through PDO, you create a PDO object. You tell it which database to use (type, server, database name, character set), who you are (username and password), and how it should behave (report errors as exceptions, return rows as associative arrays, and so on).

Technically: The PDO constructor takes a DSN (Data Source Name) string, a username, a password, and an array of driver options. The DSN starts with the driver name followed by driver-specific parameters. If the connection fails, the constructor throws a PDOException.

new PDO(string $dsn, ?string $username = null, ?string $password = null, ?array $options = null) The MySQL DSN mysql:host=127.0.0.1;port=3306;dbname=gocourse_shop;charset=utf8mb4 Part Meaning mysql: Use the pdo_mysql driver host=127.0.0.1 Server address (or a hostname, Docker service name, etc.) port=3306 Port (3306 is the default and may be omitted) dbname=gocourse_shop Database to use charset=utf8mb4 Connection character set — always set it, so all languages and emoji work unix_socket=/path/mysql.sock Alternative to host for local socket connections DSNs for other databases look different: pgsql:host=localhost;dbname=shop, sqlite:/path/to/database.sqlite, sqlite::memory:.

A Recommended Connection <?php $dsn = 'mysql:host=127.0.0.1;port=3306;dbname=gocourse_shop;charset=utf8mb4';

$pdo = new PDO($dsn, 'gocourse_app', 'change-this-app-password', [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]);

echo "Driver: " . $pdo->getAttribute(PDO::ATTR_DRIVER_NAME) . "\n"; echo "Server version: " . $pdo->getAttribute(PDO::ATTR_SERVER_VERSION) . "\n"; Output (versions depend on your server) Driver: mysql Server version: 8.4.6 The Options Explained Option Value Why PDO::ATTR_ERRMODE PDO::ERRMODE_EXCEPTION Throw PDOException on errors. This is the default since PHP 8.0; setting it explicitly documents the intention PDO::ATTR_DEFAULT_FETCH_MODE PDO::FETCH_ASSOC Return rows as associative arrays. The default, FETCH_BOTH, returns every value twice (by name and by number) PDO::ATTR_EMULATE_PREPARES false Use MySQL's real prepared statements instead of PDO's client-side emulation (see below) Emulated vs Native Prepared Statements For MySQL, PDO emulates prepared statements by default: it inserts the escaped values into the SQL itself before sending it. Setting ATTR_EMULATE_PREPARES to false makes MySQL prepare the statement on the server and receive the values separately.

Emulated (default) Native (false) Safety Safe when the connection charset is set in the DSN Safe; values never become part of the SQL Syntax errors detected At execute() At prepare() LIMIT ? with string values Fails (LIMIT '2') Use PDO::PARAM_INT in both modes Same named placeholder used twice Allowed Not allowed Both are secure when used correctly. Many projects choose native prepares for stricter, more predictable behavior.

Handling Connection Failures <?php try { $pdo = new PDO( 'mysql:host=127.0.0.1;dbname=gocourse_shop;charset=utf8mb4', 'gocourse_app', 'wrong-password', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION] ); } catch (PDOException $e) { error_log('Database connection failed: ' . $e->getMessage()); http_response_code(503); echo "The service is temporarily unavailable. Please try again later.\n"; } Output (for the visitor) The service is temporarily unavailable. Please try again later. Logged Message Database connection failed: SQLSTATE[HY000] [1045] Access denied for user 'gocourse_app'@'localhost' (using password: YES) Never display the exception message to users — it reveals the host, the username, and the database. In PHP 8.2+, the password parameter of the PDO constructor is marked #[\SensitiveParameter], so it is hidden in stack traces; older versions could leak it into logs through uncaught exception traces.

Configuration From the Environment <?php // config/database.php return [ 'dsn' => sprintf( 'mysql:host=%s;port=%d;dbname=%s;charset=utf8mb4', getenv('DB_HOST') ?: '127.0.0.1', (int) (getenv('DB_PORT') ?: 3306), getenv('DB_DATABASE') ?: 'gocourse_shop' ), 'username' => getenv('DB_USERNAME') ?: 'gocourse_app', 'password' => getenv('DB_PASSWORD') ?: '', ]; Credentials come from environment variables (see $_ENV), never from code committed to version control.

A Reusable Connection Function <?php // src/pdo.php function pdo(): PDO { static $pdo = null;

if ($pdo === null) { $config = require DIR . '/../config/database.php';

$pdo = new PDO($config['dsn'], $config['username'], $config['password'], [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]); }

return $pdo; } <?php require DIR . '/src/pdo.php';

$count = pdo()->query('SELECT COUNT(*) FROM products')->fetchColumn(); echo "Products: $count\n"; Output Products: 5 The connection is created once per request and reused. The following PDO lessons use this pdo() helper. In frameworks, a dependency injection container creates the connection and passes it to the classes that need it.

PHP 8.4: Driver-Specific Connections PHP 8.4 introduced driver-specific subclasses and a factory method:

<?php $pdo = PDO::connect('mysql:host=127.0.0.1;dbname=gocourse_shop;charset=utf8mb4', 'gocourse_app', getenv('DB_PASSWORD'));

var_dump($pdo instanceof Pdo\Mysql); // bool(true) PDO::connect() returns an instance of the matching subclass (Pdo\Mysql, Pdo\Pgsql, Pdo\Sqlite, …), which also behaves as a normal PDO object. Driver-specific constants are available on these classes, such as Pdo\Mysql::ATTR_SSL_CA; the older names on the PDO class (PDO::MYSQL_ATTR_SSL_CA) still exist for compatibility.

Secure Connections to Remote Servers For managed cloud databases, encrypt the connection with TLS and verify the server certificate:

<?php $pdo = new PDO($dsn, $username, $password, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, Pdo\Mysql::ATTR_SSL_CA => '/etc/ssl/certs/db-ca.pem', // PHP 8.4+ // PDO::MYSQL_ATTR_SSL_CA => '/etc/ssl/certs/db-ca.pem', // older PHP versions ]); Persistent Connections PDO::ATTR_PERSISTENT => true keeps connections open between requests within the same PHP-FPM worker. It saves connection time, but leftover state (open transactions, locks, session variables) can leak into the next request, and many idle connections may hit the server's connection limit. Use it only after measuring that connection time is a real bottleneck.

Common Mistakes Missing charset=utf8mb4 in the DSN Effect: Garbled non-English text; with emulated prepares, also weaker escaping.

Fix: Always include the charset in the DSN.

Leaving the Default Fetch Mode Effect: Every row contains each value twice (FETCH_BOTH).

Fix: Set PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC.

Showing Connection Errors Effect: Sensitive configuration details are exposed.

Fix: Log the message; show a generic error page.

Connecting Repeatedly Effect: Slow requests and exhausted connection limits.

Fix: Create one connection per request and reuse it.

Best Practices Use a DSN with host, database name, and charset=utf8mb4. Set error mode, default fetch mode, and prepared-statement emulation explicitly. Load credentials from the environment and use a least-privilege database user. Catch connection exceptions, log details, and show users a generic message. Reuse a single connection through a helper or a DI container. Official reference: https://www.php.net/manual/en/pdo.connections.php