PHP Reference ยท Databases (PDO)
The PDO class
Reference for the PHP PDO class: DSN strings for MySQL, PostgreSQL and SQLite, constructor options, error modes, default fetch mode, exec(), quote() and driver subclasses.
PDO (PHP Data Objects) is PHP's common interface for talking to relational databases. One PDO object represents one connection. The same methods work whether the driver behind it is MySQL, PostgreSQL, SQLite or something else, so queries, prepared statements and transactions look alike across databases.
You create the connection from a DSN (data source name: the driver name, a colon and driver-specific settings), an optional user name and password, and an array of options. Each driver needs its own extension, such as pdo_mysql, pdo_pgsql or pdo_sqlite.
$pdo = new PDO( 'mysql:host=127.0.0.1;dbname=shop;charset=utf8mb4', 'app_user', 'secret', [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION], );
Try it
Press Run (or Ctrl + Enter) to see the result here.
Program input ยท what you'd type while the program runs
Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.
Press Run (or Ctrl + Enter) to see the result here.
Program input ยท what you'd type while the program runs
Type the answers before you press Run, one per line. Each time the program reads a line (with fgets(STDIN)), it gets the next line from this box.
Syntax
new PDO(string $dsn, ?string $username = null, ?string $password = null, ?array $options = null) PDO::exec(string $statement): int|false PDO::query(string $query, ?int $fetchMode = null): PDOStatement|false PDO::prepare(string $query, array $options = []): PDOStatement|false PDO::quote(string $string, int $type = PDO::PARAM_STR): string|false PDO::getAttribute(int $attribute): mixed PDO::setAttribute(int $attribute, mixed $value): bool PDO::connect(string $dsn, ?string $username = null, ?string $password = null, ?array $options = null): static
| Name | Description |
|---|---|
$dsn |
Driver prefix plus settings: mysql:host=...;dbname=...;charset=utf8mb4, pgsql:host=...;dbname=..., sqlite:/path/file.db or sqlite::memory:. |
$username, $password |
Credentials. Ignored by SQLite. |
$options |
Attributes applied at connection time (see below). |
Common options:
| Attribute | Values and effect |
|---|---|
PDO::ATTR_ERRMODE |
ERRMODE_EXCEPTION (throw PDOException; the default since PHP 8.0), ERRMODE_WARNING, ERRMODE_SILENT. |
PDO::ATTR_DEFAULT_FETCH_MODE |
For example PDO::FETCH_ASSOC, so each fetch returns an associative array without naming the mode. |
PDO::ATTR_EMULATE_PREPARES |
false to use the database's own prepared statements where the driver supports it. |
PDO::ATTR_PERSISTENT |
true to reuse connections between requests (use with care). |
PDO::ATTR_STRINGIFY_FETCHES |
Return numbers as strings. |
Return value: the constructor returns the connection. exec() returns the number of affected rows. quote() returns the escaped, quoted string.
Errors / Exceptions: the constructor always throws PDOException when it cannot connect or the driver is missing ("could not find driver"), regardless of the error mode.
Usage notes
- Always set the error mode explicitly in your options so the code does not depend on the default.
- Set the charset in the DSN for MySQL (
charset=utf8mb4), or text with emoji and some alphabets will be corrupted. - Do not log the exception from the constructor carelessly. Stack traces can include the DSN and password. Catch it and log only the message.
- Prefer prepared statements over
quote().quote()escapes for the current driver, but placeholders keep data and SQL separate. SeePDO::prepare(). exec()is for statements without result sets (DDL,INSERT,UPDATE,DELETE); usequery()when you want rows.- Closing: set the variable to
null(and release any statements) to close the connection; it also closes at the end of the script. - Driver subclasses (PHP 8.4).
PDO::connect()returns a driver-specific object such asPdo\Sqlite,Pdo\MysqlorPdo\Pgsql, which add driver-only methods.new PDO(...)still works and returns a plainPDO. - Persistent connections skip the connect cost but keep session state (such as open transactions) alive across requests; avoid them unless you have measured a benefit.