How to Iterate Through a PDO Result Set Multiple Times?

Reiterating Through a PDO Result Set from the Beginning

When transitioning from MySQL SELECT methods to PDO methods, resetting the array pointer to iterate through the result set multiple times can be challenging. This is due to the difference in how these methods handle array pointers.

In the provided code, the first while loop successfully iterates through the fetched array starting from row zero. However, the second while loop returns an empty set since PDO's fetch() method advances the array pointer on each call.

To achieve the desired behavior, one can store the results in an array and then iterate through that array multiple times. Here's an example:

$pdo = new PDO('mysql:host=' . $host . ';dbname='.$database, $username, $password);
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$stmt = $pdo->prepare('SELECT * FROM mytable WHERE active = 1 ORDER BY name ASC');
$stmt->setFetchMode(PDO::FETCH_ASSOC);
$stmt->execute();

$rows = $stmt->fetchAll();

foreach ($rows as $r) {
    // first run
}

foreach ($rows as $r) {
    // second run
}

By using fetchAll(), all rows from the result set are stored in the $rows array. This allows you to iterate through the array multiple times without resetting the array pointer manually.

The above is the detailed content of How to Iterate Through a PDO Result Set Multiple Times?. For more information, please follow other related articles on the PHP Chinese website!