PHP PDOStatement Class

last modified April 19, 2025

The PDOStatement class represents a prepared statement and the result set after execution. It provides methods to bind parameters, execute queries, and fetch results.

Basic Definition

PDOStatement is a class that represents a prepared statement and its result. It is returned by PDO::prepare and PDO::query methods.

The class provides methods for binding parameters, executing statements, fetching results, and retrieving metadata about result sets.

Basic Query Execution

This example shows how to execute a simple query using PDOStatement.

pdo_basic_query.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->query('SELECT id, name FROM users');
    
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        echo "ID: {$row['id']}, Name: {$row['name']}\n";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

This creates a PDOStatement object with query. The fetch method retrieves each row as an associative array. The loop continues until all rows are processed.

Prepared Statement with Positional Parameters

This demonstrates using prepared statements with question mark placeholders.

pdo_positional_params.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->prepare('INSERT INTO products (name, price) VALUES (?, ?)');
    $stmt->execute(['Laptop', 999.99]);
    
    echo "Inserted ID: " . $pdo->lastInsertId();
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

The prepare method creates a statement template with placeholders. execute binds the values to these placeholders in order. This prevents SQL injection.

Named Parameters in Prepared Statements

This shows how to use named parameters for more readable prepared statements.

pdo_named_params.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->prepare('UPDATE users SET email = :email WHERE id = :id');
    $stmt->execute([
        'email' => 'new.email@example.com',
        'id' => 42
    ]);
    
    echo "Affected rows: " . $stmt->rowCount();
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

Named parameters start with a colon and are bound using an associative array. This makes the code more readable and maintainable than positional parameters.

Binding Parameters Explicitly

This demonstrates explicit parameter binding with bindValue method.

pdo_bind_params.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->prepare('SELECT * FROM products WHERE price > :min_price AND stock > 0');
    $stmt->bindValue(':min_price', 100, PDO::PARAM_INT);
    $stmt->execute();
    
    $products = $stmt->fetchAll(PDO::FETCH_OBJ);
    foreach ($products as $product) {
        echo "Product: {$product->name}, Price: {$product->price}\n";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

bindValue explicitly binds a value to a parameter with type specification. This is useful when you need to bind values in separate steps from execution.

Fetching Data in Different Formats

This shows various ways to fetch data using PDOStatement methods.

pdo_fetch_formats.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->query('SELECT id, name, email FROM users LIMIT 5');
    
    // Fetch as associative array
    $assoc = $stmt->fetch(PDO::FETCH_ASSOC);
    
    // Fetch all as objects of specific class
    $stmt->execute();
    $objects = $stmt->fetchAll(PDO::FETCH_CLASS, 'User');
    
    // Fetch into existing object
    $user = new User();
    $stmt->fetch(PDO::FETCH_INTO, $user);
    
    // Fetch single column
    $emails = $stmt->fetchAll(PDO::FETCH_COLUMN, 2);
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

PDOStatement offers multiple fetch styles. FETCH_ASSOC returns arrays, FETCH_CLASS maps to objects, FETCH_INTO populates existing objects, and FETCH_COLUMN gets single columns.

Transactions with PDOStatement

This demonstrates using PDOStatement within database transactions.

pdo_transactions.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $pdo->beginTransaction();
    
    $stmt1 = $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?');
    $stmt2 = $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?');
    
    $stmt1->execute([100, 1]);
    $stmt2->execute([100, 2]);
    
    $pdo->commit();
    echo "Funds transferred successfully";
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Transaction failed: " . $e->getMessage();
}

This shows a money transfer between accounts as an atomic transaction. Both updates must succeed or neither will be applied. beginTransaction starts it, commit completes it, and rollBack cancels on failure.

Handling Large Result Sets

This demonstrates processing large result sets efficiently.

pdo_large_results.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    
    $stmt = $pdo->prepare('SELECT * FROM large_table');
    $stmt->execute();
    
    // Process rows one at a time
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        processRow($row);
    }
    
    // Alternative method with cursor
    $stmt = $pdo->prepare('SELECT * FROM large_table', [
        PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL
    ]);
    $stmt->execute();
    
    $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, 0); // First row
    $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_REL, 1); // Next row
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

function processRow(array $row): void {
    // Process individual row
}

For large result sets, fetch rows one at a time instead of using fetchAll(). This reduces memory usage. The cursor approach allows random access to rows.

Best Practices

Always use prepared statements to prevent SQL injection
Specify fetch modes explicitly for predictable results
Close cursors with closeCursor() when done
Use transactions for multiple related operations
Handle errors with try-catch blocks

Source

PHP PDOStatement Documentation

This tutorial covered the PDOStatement class with practical examples showing database operations in different scenarios.

Author

My name is Jan Bodnar, and I am a passionate programmer with extensive programming experience. I have been writing programming articles since 2007. To date, I have authored over 1,400 articles and 8 e-books. I possess more than ten years of experience in teaching programming.

List all PHP PDO Functions.

Ebooks