Does PDO 'fetchAll' actually remove the contents of the query result?

PHP
1

I’m switching over to PDO (from mysqli) and at the same time experimenting with the best way to access the results. I tried:

$result = $stmt->fetchAll(PDO::FETCH_ASSOC); //
echo “RESULT is:
”; print_r($result); echo ‘

’;

and I got a zero based array of all the result rows printed out on screen as expected, like this:
RESULT is:
Array ( [0] => Array ( [table_id] => mem [table_name] => members [tab_id] => member_id [hdr_id] => Member_ID [cohort] => Members ) [1] => Array ( [table_id] => qes [table_name] => q1_results [tab_id] => prospect_id [hdr_id] => Response_ID [cohort] => Respondents ) [2] => Array ( [table_id] => reg [table_name] => prospects [tab_id] => prospect_id [hdr_id] => Registrant_ID [cohort] => Registrants ) )

Then I tried:

$i = 1;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
	echo 'Row ' . $i . ' is:<br />'; print_r($row); echo '<br /><br />';
	$i++;
}

and I got neat rows with each row as an associative array, also as expected., like this:
Row 1 is:
Array ( [table_id] => mem [table_name] => members [tab_id] => member_id [hdr_id] => Member_ID [cohort] => Members )

Row 2 is:
Array ( [table_id] => qes [table_name] => q1_results [tab_id] => prospect_id [hdr_id] => Response_ID [cohort] => Respondents )

Row 3 is:
Array ( [table_id] => reg [table_name] => prospects [tab_id] => prospect_id [hdr_id] => Registrant_ID [cohort] => Registrants )

When I tried running BOTH snippets, I only got the first one on screen, the separate rows do not appear. This makes me wonder if the ‘fetchAll’ is actually removing the contents rather than copying them, so that when the second snippet runs there’s nothing left for it to fetch. I find that rather surprising. Is it correct? Or do I need to reset a pointer between snippets?

I should make it clear I don’t actually intend to use BOTH snippets, it’s a matter of deciding which approach works best in the circumstances, but I just stumbled on this effect and was surprised.

2 
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);

foreach($results as $result) {
  echo "<pre>" . print_r($result, 1) . "</pre>\n";
}
3

Thank you. Your suggestion shows another way of getting the results, but it doesn’t answer my question about WHY, after the ‘fetchAll’ there appears to be nothing to ‘fetch’ in a second attempt. Since starting this post I have found other references to the same problem.
It seems the best answer may be to save the array from ‘fetchAll’ and navigate it with PHP.

4

If your question is more “technical curiosity” there may be some way to reset the result pointer to “data_seek” back to the beginning. I have never tried it, but it looks like it might be possible with a scrollable cursor and maybe

PDO::FETCH_ORI_FIRST (integer)
Fetch the first row in the result set. Valid only for scrollable cursors.

http://php.net/manual/en/pdo.constants.php

But if it is that you want to use the results more than once, then I think assigning the values to an array or object would be the easier way to code it.

5

Thank you. Yes, I’ve settled for saving the ‘fetchAll’ array and navigating with PHP. Mere mention of scrollable cursors puts me off anything else.

My original question was entirely prompted by my surprise that ‘fetchAll’ seemed to have drained the content from the MySQL result!

6

Yes, the behaviour is correct. The PDOStatement object has no method for changing or resetting the pointer. Once you fetch a row from a statement you can’t fetch it again so after fetching all rows the statement becomes drained and almost useless. The only thing you can do with it is closeCursor() on it and execute it again - but then you make another round trip to the database, which is not what you want.

Scrollable cursors don’t change the fact that rows are drained from the statement. There might be some very little hope for PDO::FETCH_ORI_ABS where you specify by index which row from the statement to fetch and trying fetching the same row multiple times - but PDO::FETCH_ORI_ABS doesn’t work with MySQL at all so the case is closed. I haven’t tried it with other databases.

1 Like
7

Update: Out of curiosity I checked the behaviour of PostgreSQL and yes, PDO::FETCH_ORI_ABS works there so it’s possible to fetch rows multiple times from a statement:

$stmt = $pdo->prepare("SELECT * FROM mytable LIMIT 3", array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();

// fetch rows backward
$i = 3;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, $i)) {
	print_r($row);
	$i--;
}

// fetch rows forward
$i = 1;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC, PDO::FETCH_ORI_ABS, $i)) {
	print_r($row);
	$i++;
}

However, although the statement isn’t executed again, each fetch() needs to make a round trip to the database so it doesn’t seem to be the most efficient way. The result set of a “cursored” statement is kept on the database server, not in some php buffer. I suppose cursors are most useful when used within SQL procedures because then there is no connection overhead.

1 Like
8

Thank you for these additional insights. As you say, additional trips to the SQL server seem to be unnecessary if one saves the ‘fetchAll’ array. So that’s the course I’ve gone for.

9

Assigning to an array has worked for me in my admittedly limited experience. I have had some troubles in the past with mega (and I mean big) arrays slowing things down a bit. What I did then was to unset the array as soon as I was done with it. But I can almost guarantee that I could have written better SQL / PHP to better effect.

10

@Lemon_Juice At least for mysql, there are no roundtrips to database. All the requested data is stored on the PHP side, at least as long as you’re using a buffered query (and I believe that for the unbuffered query these merry-go-round tricks are simply unavailable).

It is not additional trips which you have to avoid (as there are actually none) but intermixing of the layers.

Having an array, you can use it anywhere, without the need of using a database API to get the data. It will make your application more flexible and less tightly coupled.

11

You are right but you are talking about a different case here. I was saying that there are roundtrips to the database when using scrollable cursors - then each fetch() creates a connection to the database. From my tests MySQL PDO doesn’t support scrollable cursors at all - you can use PDO::FETCH_ORI_XXX constants but they are silently ignored, so the point is moot. With PostrgreSQL PDO cursors work and that is where each fetch() creates a roundtrip to the database.

1 Like
12

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.