Skip to content
Tutorials Center
My learning

Topics

Lesson 88 of 147 ยท Databases

PDO Basics: Connecting and Querying

Learn PDO step by step: DSNs and connection options, exec and query, fetch, fetchAll and fetchColumn, fetch modes, error modes and PHP 8.4's PDO::connect.

In PHP and Databases you opened your first PDO connection and ran a query. This lesson slows down and looks at each piece properly: the options you should set on every connection, the difference between exec() and query(), the many ways to fetch rows, how PDO reports errors, and the driver-specific subclasses added in PHP 8.4.

Everything here runs against SQLite in memory, so each example creates its own small table and fills it with data. The PHP code is the same for MySQL or PostgreSQL; only the DSN changes.

One rule before we begin: all SQL in this lesson is fixed text written by the programmer. As soon as any part of a query comes from a user (a search term, an ID from the URL, a form field), you must use the prepared statements from the next lesson. Pasting user input into SQL strings is how SQL injection happens.

A connection with sensible options

Here's the connection code you'll use for the rest of the chapter, shown for MySQL first (read-only, since our sandbox has no MySQL server):

PHP
<?php
declare(strict_types=1);

$pdo = new PDO(
    'mysql:host=127.0.0.1;dbname=shop;charset=utf8mb4',
    getenv('DB_USER'),
    getenv('DB_PASSWORD'),
    [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
        PDO::ATTR_EMULATE_PREPARES => false,
    ]
);

The options array is the fourth argument. What each option does:

Option Value Why
PDO::ATTR_ERRMODE PDO::ERRMODE_EXCEPTION Every failed query throws a PDOException. This is the default since PHP 8.0; setting it explicitly documents your intent.
PDO::ATTR_DEFAULT_FETCH_MODE PDO::FETCH_ASSOC Rows come back as ['id' => 1, 'name' => '...'], without duplicate numbered keys.
PDO::ATTR_EMULATE_PREPARES false MySQL (and PostgreSQL): use the server's real prepared statements, so values travel separately from the SQL instead of being escaped into it by PDO.

ATTR_EMULATE_PREPARES is specific to MySQL (and PostgreSQL); the SQLite driver doesn't support it and throws an exception if you try to read it. The SQLite version of the connection is shorter:

PHP
$pdo = new PDO('sqlite::memory:', options: [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
]);

The options: named argument skips the user name and password, which SQLite doesn't need. You can also change attributes after connecting with $pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC).

exec() and query()

PDO has two methods for running fixed SQL directly:

  • $pdo->exec($sql) runs a statement that doesn't return rows (CREATE TABLE, INSERT, UPDATE, DELETE) and returns the number of affected rows as an int.
  • $pdo->query($sql) runs a statement that returns rows (SELECT) and gives you a PDOStatement, the result set you then fetch rows from.
PHP Creating a table with exec() and reading it with query()

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.

A few things to notice:

  • Nowdoc strings (<<<'SQL') are ideal for multi-line SQL: no escaping, no accidental variable interpolation, and the closing marker's indentation is removed from every line (Strings covers heredoc and nowdoc).
  • A PDOStatement can be used directly in foreach, which fetches one row per iteration. It's the simplest way to walk through a result.
  • Storing money as integer cents avoids float rounding problems; divide by 100 only for display. (The Math Functions lesson explains why floats and money don't mix.)

Fetch modes: what a row looks like

A fetch mode decides the shape of each row. Without the FETCH_ASSOC option, PDO uses FETCH_BOTH, which gives every column twice:

PHP FETCH_BOTH, FETCH_ASSOC, FETCH_NUM and FETCH_OBJ

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.

FETCH_BOTH doubles the memory a result uses and makes print_r() output and json_encode() results confusing, which is why nearly every project switches the default to FETCH_ASSOC. FETCH_NUM is occasionally handy with destructuring ([$id, $name] = $row), and FETCH_OBJ returns stdClass objects if you prefer $row->name syntax.

You can pass a mode to a single fetch() or fetchAll() call, as above, or set it for a whole statement with $statement->setFetchMode(...).

fetch(), fetchAll() and fetchColumn()

There are three main ways to pull data out of a statement, and choosing the right one makes code shorter and clearer:

Method Returns Use it for
fetch() The next row, or false when there are no more One row (a user by ID), or looping over a large result one row at a time
fetchAll() An array of all rows ([] if none) Small to medium results you want as an array
fetchColumn() The first column of the next row, or false A single value: a count, a sum, a name
PHP Fetching one row, all rows and a single value

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.

The var_dump($missing) line shows the one result that trips people up: when no row matches, fetch() returns false, not null or an empty array. Always check before using the row: if ($row === false) { /* 404 */ }.

Notice also that COUNT(*) came back as int(4). Since PHP 8.1, the SQLite and MySQL drivers return integer and float columns as PHP int and float. Older PHP versions returned most values as strings, so you'll see (int) casts sprinkled through legacy code. NULL in the database always becomes PHP null.

Fetch modes that reshape the result

fetchAll() accepts some extra modes that save you a loop:

PHP FETCH_COLUMN, FETCH_KEY_PAIR, FETCH_GROUP and FETCH_UNIQUE

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.

  • FETCH_COLUMN returns a plain list of the first column.
  • FETCH_KEY_PAIR needs exactly two columns and turns them into key => value.
  • FETCH_GROUP uses the first column as the key and collects matching rows under it; combined with FETCH_COLUMN, each group is a list of the second column's values.
  • FETCH_UNIQUE uses the first column as the key for each row, so you can look rows up by ID.

Rows as objects of your own classes

Associative arrays are convenient, but a typed object documents the shape of your data and lets the editor and static analysis catch typos. The cleanest modern approach is to fetch arrays and build readonly objects from them with named arguments. PDO can also create objects itself with FETCH_CLASS:

PHP Mapping rows to typed objects

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.

Option 1 works with any class, including readonly ones, because the constructor does the work: new Product(...$row) passes ['id' => 1, 'name' => 'Desk lamp', 'priceCents' => 2499] as named arguments, which is why the SQL aliases price_cents AS priceCents. If a column is missing or has the wrong type, you get a clear error from the constructor.

Option 2, fetchObject() (or fetchAll(PDO::FETCH_CLASS, ProductRow::class)), creates the object without calling the constructor first and writes each column into the property of the same name, then calls the constructor. That's quick to write, but properties get their values before your constructor can validate anything, and the column names must match the property names exactly, so many teams prefer option 1. Larger apps often use a data mapper or ORM (Doctrine, Eloquent) that does this mapping for every table.

How PDO reports errors

When SQL fails (a typo, a missing table, a violated constraint), what happens depends on the error mode:

Mode What a failure does
PDO::ERRMODE_EXCEPTION (default since PHP 8.0) Throws a PDOException with the SQLSTATE code and the database's message
PDO::ERRMODE_WARNING Emits a PHP warning and returns false
PDO::ERRMODE_SILENT (default before PHP 8.0) Just returns false; you must check errorInfo() yourself
PHP Errors as exceptions and the silent alternative

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.

getCode() returns the five-character SQLSTATE as a string: HY000 for general errors and 23000 for integrity constraint violations such as a duplicate unique value or a NULL in a NOT NULL column. The message adds the driver's own details. Checking for 23000 is a common way to turn "email already registered" into a friendly validation message instead of an error page.

The silent mode shows why exceptions became the default: query() returned false, and if the code had gone on to call ->fetchAll() on it, the real cause would have been buried under an unrelated "call to a member function on bool" error. Stay with exceptions, and let a top-level handler log anything you don't catch (Custom Exceptions and Error Handling Strategy).

Never show $e->getMessage() from a database exception to visitors. It can contain table names, column names and parts of the query. Log it, and show a generic "Something went wrong" page with a 500 status.

PDO::connect() and driver subclasses (PHP 8.4)

Until PHP 8.3, every connection was a plain PDO object, and driver-specific features hid behind generic methods with names like sqliteCreateFunction(). PHP 8.4 added a subclass for each driver: Pdo\Mysql, Pdo\Pgsql, Pdo\Sqlite, Pdo\Firebird, Pdo\Odbc and Pdo\Dblib. The new static method PDO::connect() reads the DSN and returns an object of the right subclass, and you can also instantiate a subclass directly:

PHP PDO::connect() and Pdo\Sqlite

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.

Because each subclass extends PDO, all existing code that type-hints PDO keeps working. Use the subclasses when you need driver-specific features, or as a type declaration (function report(Pdo\Pgsql $db)) when code genuinely only works with one database. Calling new Pdo\Sqlite() with a MySQL DSN throws an exception, so the type always matches the actual connection.

The createFunction() call registers a PHP closure as an SQL function, something only SQLite can do because it runs inside the PHP process.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Which method should you use to run CREATE TABLE or a fixed DELETE statement that returns no rows?

    exec() runs SQL that produces no result set and returns the affected-row count. query() is for statements that return rows.
  2. 2. With no fetch mode configured, what does $pdo->query('SELECT id, name FROM users')->fetch() return for one row?

    The default is FETCH_BOTH, which returns each column by name and by number. Set PDO::ATTR_DEFAULT_FETCH_MODE to PDO::FETCH_ASSOC to avoid it.
  3. 3. What does fetch() return when the query matched no rows?

    fetch() and fetchColumn() return false when there is no (more) row. fetchAll() returns an empty array.
  4. 4. Which call turns SELECT id, name FROM categories into [1 => 'Books', 2 => 'Games']?

    FETCH_KEY_PAIR uses the first column as the key and the second as the value. It requires exactly two columns.
  5. 5. What does PDO::connect('sqlite::memory:') return in PHP 8.4+?

    PDO::connect() picks the driver subclass from the DSN. Pdo\Sqlite extends PDO, so code that expects PDO still works.

Summary

  • Connect with new PDO($dsn, $user, $password, $options) and set ERRMODE_EXCEPTION and FETCH_ASSOC; for MySQL also ATTR_EMULATE_PREPARES => false and charset=utf8mb4.
  • exec() runs statements without results and returns the affected-row count; query() returns a PDOStatement for SELECT. Use them only for fixed SQL; user input needs prepared statements.
  • fetch() gets one row (false when none), fetchAll() gets an array of rows, fetchColumn() gets one value. A statement can also be looped with foreach.
  • Fetch modes shape rows: FETCH_ASSOC, FETCH_NUM, FETCH_OBJ, and for fetchAll() also FETCH_COLUMN, FETCH_KEY_PAIR, FETCH_GROUP and FETCH_UNIQUE. Map rows to typed objects with new Product(...$row) or FETCH_CLASS.
  • Failed SQL throws PDOException; getCode() gives the SQLSTATE (23000 for constraint violations). Log the message, never show it to users.
  • PHP 8.4 added driver subclasses such as Pdo\Sqlite and Pdo\Mysql, and PDO::connect() returns the right one for a DSN.

Next: Prepared Statements and SQL Injection