and I got a zero based array of all the result rows printed out on screen as expected, like this:
Array (  => Array ( [table_id] => mem [table_name] => members [tab_id] => member_id [hdr_id] => Member_ID [cohort] => Members )  => Array ( [table_id] => qes [table_name] => q1_results [tab_id] => prospect_id [hdr_id] => Response_ID [cohort] => Respondents )  => Array ( [table_id] => reg [table_name] => prospects [tab_id] => prospect_id [hdr_id] => Registrant_ID [cohort] => Registrants ) )
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 )
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.
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.
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
Fetch the first row in the result set. Valid only for scrollable cursors.
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.
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.
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.
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.