5 More Advanced and Must-Use Tips and Tricks for PDO in PHP

In a previous post we discussed 5 Advanced and Must-Use Tips and Tricks for PDO in PHP. This is a continuation of that article!

Using Transactions with Savepoints

Savepoints allow you to create nested transactions within a larger transaction, providing more granular control over database operations. It’s helpful when you need to handle multiple steps and ensure partial rollbacks if needed:

try {
    $pdo->beginTransaction();

    // Step 1
    // ...

    $pdo->savepoint('step1');

    // Step 2
    // ...

    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBackTo('step1');
    echo 'Transaction failed: ' . $e->getMessage();
}

Fetching Multiple Result Sets

PDO supports fetching multiple result sets from a stored procedure or a query with multiple SELECT statements. Use nextRowset() to move to the next result set:

$stmt = $pdo->prepare('CALL get_multiple_result_sets()');
$stmt->execute();

// Fetch the first result set
$resultSet1 = $stmt->fetchAll();

// Move to the next result set
$stmt->nextRowset();

// Fetch the second result set
$resultSet2 = $stmt->fetchAll();

Handling Large Result Sets with fetch()

When dealing with large result sets, you can fetch rows one by one using the fetch() method with a specified fetch style. This reduces memory consumption and improves performance:

$stmt = $pdo->query('SELECT name, email FROM users');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo $row['name'] . ' - ' . $row['email'] . '<br>';
}

Bulk Data Insertion

When inserting a large number of records, it’s more efficient to use prepared statements with execute() inside a loop to perform bulk data insertion:

$data = [
    ['John Doe', 'john@example.com'],
    ['Jane Smith', 'jane@example.com'],
    // More data rows...
];

$stmt = $pdo->prepare('INSERT INTO users (name, email) VALUES (?, ?)');

foreach ($data as $row) {
    $stmt->execute($row);
}

Fetching into Custom Objects

Instead of fetching data into associative arrays or objects, you can define custom classes and fetch data directly into them using the FETCH_CLASS fetch mode:

class User {
    public $id;
    public $name;
    public $email;
}

$stmt = $pdo->query('SELECT * FROM users');
$results = $stmt->fetchAll(PDO::FETCH_CLASS, 'User');

---

Implementing these additional tips and tricks will further optimize your PDO-driven PHP applications. Remember to always follow best practices and thoroughly test your code to ensure it performs as expected. Continue exploring PDO’s capabilities and stay up-to-date with the latest PHP developments to enhance your programming skills.