PHP PDOStatement::closeCursor Method

last modified April 19, 2025

The PDOStatement::closeCursor method frees up the connection to the server. This allows other SQL statements to be executed while the current statement still has unfetched rows.

Basic Definition

PDOStatement::closeCursor clears the result set of a PDOStatement object. This is useful when you need to execute another query before finishing with the current result set.

Syntax: public PDOStatement::closeCursor(): bool. The method returns true on success or false on failure. It's particularly important for databases that don't support multiple active statements.

Basic Usage

This shows the simplest use case of closeCursor to free database resources.

close_cursor_basic.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $stmt = $pdo->query('SELECT * FROM large_table');
    
    // Process some rows
    for ($i = 0; $i < 10; $i++) {
        $row = $stmt->fetch();
        echo "Row {$i}: {$row['id']}\n";
    }
    
    // Free up the connection
    $stmt->closeCursor();
    
    // Execute another query
    $pdo->query('UPDATE stats SET processed = 1');
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

This example fetches 10 rows from a large result set then calls closeCursor. This allows the subsequent UPDATE query to execute without waiting for all rows to be fetched.

Multiple Statements

Demonstrates using closeCursor when executing multiple statements on the same connection.

close_cursor_multiple.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    
    // First query
    $stmt1 = $pdo->query('SELECT * FROM users WHERE active = 1');
    while ($user = $stmt1->fetch()) {
        echo "Active user: {$user['name']}\n";
    }
    $stmt1->closeCursor();
    
    // Second query
    $stmt2 = $pdo->query('SELECT * FROM products WHERE stock < 10');
    while ($product = $stmt2->fetch()) {
        echo "Low stock: {$product['name']}\n";
    }
    $stmt2->closeCursor();
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

This shows proper resource cleanup between two queries. Each statement is closed before moving to the next one. This prevents potential "Commands out of sync" errors in MySQL.

With Unfetched Results

Shows how closeCursor can be used when not all results are fetched.

close_cursor_unfetched.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $stmt = $pdo->query('SELECT * FROM large_dataset');
    
    // Only process first 100 rows
    $count = 0;
    while ($row = $stmt->fetch() && $count++ < 100) {
        processRow($row);
    }
    
    // Close cursor even though more rows exist
    $stmt->closeCursor();
    
    // Execute another query
    $pdo->exec('DELETE FROM temp_data');
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

function processRow($row) {
    // Process row data
}

Here we intentionally don't fetch all rows from the result set. closeCursor is called to free resources before executing another statement. This prevents memory leaks with large result sets.

In Transactions

Demonstrates using closeCursor within a database transaction.

close_cursor_transaction.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $pdo->beginTransaction();
    
    // First operation
    $stmt1 = $pdo->query('SELECT * FROM accounts WHERE balance > 1000');
    $richAccounts = $stmt1->fetchAll();
    $stmt1->closeCursor();
    
    // Second operation
    $stmt2 = $pdo->prepare('UPDATE accounts SET flagged = 1 WHERE id = ?');
    foreach ($richAccounts as $account) {
        $stmt2->execute([$account['id']]);
    }
    
    $pdo->commit();
} catch (PDOException $e) {
    $pdo->rollBack();
    echo "Error: " . $e->getMessage();
}

This example shows proper cursor management within a transaction. The first query's cursor is closed before processing the results and executing updates. This maintains clean transaction boundaries.

With Prepared Statements

Shows closeCursor usage with prepared statements that are executed multiple times.

close_cursor_prepared.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $stmt = $pdo->prepare('SELECT * FROM orders WHERE customer_id = ?');
    
    $customerIds = [5, 8, 12];
    foreach ($customerIds as $id) {
        $stmt->execute([$id]);
        $orders = $stmt->fetchAll();
        processOrders($orders);
        $stmt->closeCursor();
    }
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

function processOrders($orders) {
    // Process customer orders
}

This demonstrates proper cursor cleanup when reusing a prepared statement. After each execution and result processing, closeCursor is called to reset the statement for the next execution.

Error Handling

Shows how to handle errors when using closeCursor.

close_cursor_error.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $stmt = $pdo->query('SELECT * FROM products');
    
    // Process some rows
    while ($row = $stmt->fetch()) {
        echo "Product: {$row['name']}\n";
    }
    
    // Attempt to close cursor
    if (!$stmt->closeCursor()) {
        throw new Exception('Failed to close cursor');
    }
    
    // Execute another query
    $pdo->exec('UPDATE inventory SET checked = NOW()');
} catch (PDOException $e) {
    echo "Database error: " . $e->getMessage();
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}

This example checks the return value of closeCursor and throws an exception if it fails. Proper error handling ensures you're aware of any cursor cleanup issues.

With Stored Procedures

Demonstrates closeCursor usage when calling stored procedures that return multiple result sets.

close_cursor_procedure.php

<?php

declare(strict_types=1);

try {
    $pdo = new PDO('mysql:host=localhost;dbname=testdb', 'user', 'password');
    $stmt = $pdo->query('CALL get_sales_report()');
    
    // Process first result set
    echo "Daily Sales:\n";
    while ($row = $stmt->fetch()) {
        echo "{$row['day']}: {$row['amount']}\n";
    }
    
    $stmt->closeCursor();
    
    // Move to next result set
    if ($stmt->nextRowset()) {
        echo "\nMonthly Sales:\n";
        while ($row = $stmt->fetch()) {
            echo "{$row['month']}: {$row['amount']}\n";
        }
    }
    
    $stmt->closeCursor();
} catch (PDOException $e) {
    echo "Error: " . $e->getMessage();
}

This shows proper cursor management with stored procedures that return multiple result sets. closeCursor is called between processing each result set to ensure clean transitions.

Best Practices

Always Use: Call closeCursor when done with a result set.
Before New Queries: Clean up previous statements first.
With Large Results: Essential for memory management.
In Loops: Close cursors when reusing statements.
Error Handling: Check return values for problems.

Source

PHP PDOStatement::closeCursor Documentation

This tutorial covered the PDOStatement::closeCursor method with practical examples showing its importance in database operations.

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