Just wanted to run this up the flag pole and see who salutes it....
I was thinking about (with some instigation by something I read on-line) about how it could potentially be problematic to have a DB query result stored in an array (or object, or JSON string) to be iterated over elsewhere in the code, on the possibility that if a lot of rows are returned (for an undefined value of "a lot"), you could be storing a lot of data in memory (e.g. PHP arrays are not known for being memory-efficient).
So the first option that comes to mind is to just return the query result object (e.g. a PDO::Statement object or MySQLi::mysqli_result object), and let the client code iterate over it. But that seems "wrong" as I don't want the client code to know/care what DB interface I'm using. So now I'm thinking I could create a user-defined class implementing Iterator that would be used to make query results available to any other code, that the client code could foreach() or while() over as needed.
First question: Does that sound like a reasonable approach, or is it overkill, or is there a "why don't you use this obvious solution?" response?
Secondly, if this seems like a good approach, would you make some sort of Factory pattern that would detect what type of result object it is (e.g. PDO or MySQLi) and instantiate the correct concrete class -- presumably extending PDO::Statement and implementing an abstract class (or interface?), or would you take a different approach?
