By infinito on

Hey,

Quick question regarding reuse of a result set in D7.

Lets say, I have the following code:

$query = db_select('table', 'n')->fields('n', array())->execute();

I'm gonna use the result set 2 times in my code. 

$first_use = $query;
while ($row = $first_use->fetchObject()) {
   .. do something
}

$second_use = $query;
while ($row = $second_use->fetchObject()) {
   .. do something
}

After running the first loop, the second loop doesnt run, since the result set is empty. Even a $second_use = clone $query; is not working. Only requerying it again from database will get the result set back.

Isnt there any way to clone a result set and then loop through it? Removing ->execute(); and and adding it to $first_use = $query->execute(); does the trick but it seems triviel way to run a query just because I want to use the result set twice? I know I can do a inital loop of the result set and save the data to an array and use this, but there must be a way to clone the result set?

⋅ Categories: Drupal 7.x

Comments

nevets’s picture

nevets commented

I would code it as

$first_use = $query;
while ($row = $first_use->fetchObject()) {
   .. do something
   .. do something else
}
infinito’s picture

infinito commented

Yes but in my actual project the same result set will be used again within another loop using it to build x numbers of tables, so it's not an option to do both actions in the first loop

goofus’s picture

goofus commented

Hello,
Yes, you can create an array of objects with php :)

You might also want to look at the PDO documentation on the php site http://php.net/manual/en/book.pdo.php. You might want to consider use of closeCursor, caching the underlying database statement and then re-executing.

Good Luck :)

WorldFallz’s picture

WorldFallz commented

See http://drupal.org/node/1251174 -- toward the bottom of the page there's examples. i think fetchAllKeyed will do it.

infinito’s picture

infinito commented

Thanks for your replies. I thought copy or cloning the $query variable into $first_use would be sufficient. I'm aware the results will be removed one by one after iteration, but I still don't understand why it won't work with my first approach?

goofus’s picture

goofus commented

Hello,
No problem :) Glad yo have helped (even if it was to simply to echo what you were thinking ).

This is my guess. I believe the result object contains an internal iterator. Once it the iterator gets to the end, the result resources are released. In other words, what you want is the ability to reset the iterator to the beginning. That's why I perused the php PDO site. I didn't see the reset mechanism, but I may have missed it :)

If you save the results in your own array, logically you will be able to do the same thing.

Good Luck

infinito’s picture

infinito commented

Yes, it must contain a reference to the results and copy / cloning only clones the properties of the object and therefore any referrenced variables aren't cloned. For now, this must be the reason and on a rainy day I will read through the PDO documentation ;)

burkejam1971’s picture

burkejam1971 commented

I have a similar issue.

I have one query that shows my results. The other query is used to populate a menu based on the results from the first query. The two queries are identical with the exception of a GROUP BY clause.

I wanted to define $query_1 then clone it to $query_2 = $query_1

Then add a GROUP BY on $query_2->groupBy("some_field");

Finally I would execute both queries.

The strange behavior put the GROUP BY on both queries?

I'm boggled, can anybody explain this behavior?

Thank you

nevets’s picture

nevets commented

$query_2 = $query_1 does not clone $query_1 into $query_2, instead query_2 is set to reference query_1. Instead you want to use $query_2 = clone $query_1

As a side note, please open new issues instead of piggybacking on old ones.