Skip to content
Tutorials Center
My learning

Topics

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.

PHP
$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=shop;charset=utf8mb4',
    'app_user',
    'secret',
    [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION],
);

Try it

PHP Connecting to an in-memory SQLite database

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.

PHP Error modes and bad connections

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

PHP
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. See PDO::prepare().
  • exec() is for statements without result sets (DDL, INSERT, UPDATE, DELETE); use query() 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 as Pdo\Sqlite, Pdo\Mysql or Pdo\Pgsql, which add driver-only methods. new PDO(...) still works and returns a plain PDO.
  • 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.