PHP PDOStatementInterface

last modified April 19, 2025

The PDOStatementInterface in PHP represents prepared statements and their results. It provides methods to execute queries and fetch data.

Basic Definition

PDOStatementInterface is an interface that defines methods for prepared statements. It's implemented by PDOStatement class.

Key methods include execute, fetch, fetchAll, bindParam, and bindValue. These handle query execution and result processing.

Basic PDOStatement Usage

This example shows basic prepared statement usage with PDOStatement.

pdo_basic.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 users WHERE id = ?');
    $stmt->execute([1]);
    
    $user = $stmt->fetch(PDO::FETCH_ASSOC);
    print_r($user);
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

This creates a prepared statement, executes it with a parameter, and fetches the result. The fetch method returns data as an associative array.

Binding Parameters

This demonstrates explicit parameter binding with PDOStatement.

pdo_bind.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 (:name, :price)');
    
    $name = 'Laptop';
    $price = 999.99;
    
    $stmt->bindParam(':name', $name, PDO::PARAM_STR);
    $stmt->bindParam(':price', $price, PDO::PARAM_STR);
    
    $stmt->execute();
    echo "Product inserted successfully";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

This uses bindParam to explicitly bind variables to parameters. The third parameter specifies the data type. Values are bound by reference.

Fetching Multiple Rows

This shows how to fetch multiple rows using PDOStatement.

pdo_fetch_all.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 > ?');
    $stmt->execute([500]);
    
    $products = $stmt->fetchAll(PDO::FETCH_OBJ);
    
    foreach ($products as $product) {
        echo "{$product->name}: {$product->price}\n";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

This fetches all rows matching the condition as objects. fetchAll retrieves the complete result set at once. FETCH_OBJ returns stdClass objects.

Column Binding

This demonstrates binding result columns to PHP variables.

pdo_bind_column.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 name, email FROM users WHERE id = ?');
    $stmt->execute([1]);
    
    $stmt->bindColumn('name', $name);
    $stmt->bindColumn('email', $email);
    
    if ($stmt->fetch(PDO::FETCH_BOUND)) {
        echo "Name: $name, Email: $email";
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

This binds result columns to variables. When fetch is called with FETCH_BOUND, the variables are automatically populated. Column names must match the query.

Row Count

This shows how to get the number of affected rows from a statement.

pdo_row_count.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 products SET price = price * 1.1 WHERE category = ?');
    $stmt->execute(['Electronics']);
    
    $count = $stmt->rowCount();
    echo "Updated $count products";
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

rowCount returns the number of rows affected by the last statement. For SELECT statements, behavior may vary by database driver.

Fetching Column Metadata

This demonstrates retrieving column information from a result set.

pdo_metadata.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 LIMIT 1');
    $stmt->execute();
    
    $meta = $stmt->getColumnMeta(0);
    print_r($meta);
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

getColumnMeta returns an array with information about a result column. The information includes name, type, table, and other driver-specific details.

Setting Fetch Mode

This shows how to set the default fetch mode for a statement.

pdo_fetch_mode.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 users');
    $stmt->setFetchMode(PDO::FETCH_CLASS, 'User');
    $stmt->execute();
    
    $users = $stmt->fetchAll();
    foreach ($users as $user) {
        echo $user->getName();
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

setFetchMode configures how results will be fetched. This example fetches into User class instances. The class must be defined before use.

Best Practices

Always use prepared statements to prevent SQL injection
Set error mode to exceptions for better error handling
Close cursors with closeCursor() when done
Use appropriate fetch modes for your data structure
Bind parameters explicitly for complex queries

Source

PHP PDOStatement Documentation

This tutorial covered the PDOStatementInterface 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