reuse of SQL query results without rerunning query and with minimal memory use

[mc12345678]

I am working on a mod to a plugin. Currently results of a query are used to present all of the applicable information on the screen with a summary at the end; however, I would like to be able to provide the summary at the beginning.

The two methods of this I can conceive are to save the results of the query to a second variable, step through as needed to get the summary, and then use the second variable to resent the information and repeat the results. Ibelieve this would use extra memory unnecessarily.

The second option I can think of is to run the query, calculate, run the query and display; however, this has the potential of providing two different results. (The second containing more info than the first.)

Isn't there some type of command to return back to the start of the query result or to capture a pointer to the head and reset the query back to the identified position?

[lat9]

You can use a code construct similar to:

$result = $db->Execute($your_sql_code);
while(!$result->EOF) {
  <perform your summary calculations>
  $result->MoveNext();
}
$result->Move(0);  // Move to first element ...
$result->MoveNext();  // Required to not process first element twice
while(!$result->EOF) {
  <do your line-by-line display>
  $result->MoveNext();
}
unset($result);

[mc12345678]

A little unclear on the use of movenext immediately after move(0). Wouldn't I need to enter the second loop with the pointer at the first element? (Assuming that the first element is at position zero.) That way if zero elements are returned then the loop would be bypassed like the first loop would be? Or does position zero point to BOF and there is no info in it and if there were no elements returned that position "1" would be EOF?

Btw, sorry, THANK YOU for your prompt response and what looks like a functional and simple solution.

[lat9]

A little unclear on the use of movenext immediately after move(0). Wouldn't I need to enter the second loop with the pointer at the first element? (Assuming that the first element is at position zero.) That way if zero elements are returned then the loop would be bypassed like the first loop would be? Or does position zero point to BOF and there is no info in it and if there were no elements returned that position "1" would be EOF?

Btw, sorry, THANK YOU for your prompt response and what looks like a functional and simple solution.

The requirement for the MoveNext() call after the Move(0) call is based on the core code's operation. Essentially, the Move(0) kinda-sorta-not-exactly positions the pointer and the call to MoveNext() finalizes the operation. I'm want to call it a "bug" since there are at least two plugins (one of them mine) that "assume" this behavior.

[mc12345678]

The requirement for the MoveNext() call after the Move(0) call is based on the core code's operation. Essentially, the Move(0) kinda-sorta-not-exactly positions the pointer and the call to MoveNext() finalizes the operation. I'm want to call it a "bug" since there are at least two plugins (one of them mine) that "assume" this behavior.

I'm good with that. (Ie haven't actually "arrived" at the beginning until the first movenext after a move(0). ) If that's what php/mySQL needs, then that's what it will get. :) again, thank you.

[Dave224]

I ran into a similar problem troubleshooting a modification to a plugin. I used a foreach after a query to output some variables in the query results. Subsequent plugin code used a while (!$results->EOF) loop to process the query results again, but consistently fails. I discovered that !$results->EOF was false at the while loop even though there were results from the preceding foreach loop. I tried LAT9's approach but at least EOF is not restored. Removing my troubleshooting foreach loop eliminated the problem and the while loop executed normally.

I would like to understand and avoid this problem in future troubleshooting activities. Any other suggestions on how to reset the query results including EOF?

TIA. Dave
zc157c, PHP 8.0.22, mysql 5.7.39

[dbltoe]

zc157c, PHP 8.0.22, mysql 5.7.39

While 1.5.7c should not have problems with PHP 8.0, your version of the mod may not like that version.
Have you tried dropping the PHP down a notch?

[mc12345678]

I ran into a similar problem troubleshooting a modification to a plugin. I used a foreach after a query to output some variables in the query results. Subsequent plugin code used a while (!$results->EOF) loop to process the query results again, but consistently fails. I discovered that !$results->EOF was false at the while loop even though there were results from the preceding foreach loop. I tried LAT9's approach but at least EOF is not restored. Removing my troubleshooting foreach loop eliminated the problem and the while loop executed normally.

I would like to understand and avoid this problem in future troubleshooting activities. Any other suggestions on how to reset the query results including EOF?

TIA. Dave
zc157c, PHP 8.0.22, mysql 5.7.39

Seeing that you are using a version greater than the base Zen Cart 1.5.5, there is a database method called rewind that will accomplish your desired goal.

If you did a query that resulted in the variable $results containing your query results, then calling: $results->rewind(); at a point after your usage and before the plugin's while loop, then the query results should be reset to the beginning and allow "proper" processing.

Alternatively, the plugin could be updated to also use the foreach loop, though be mindful that the internal loop may have done some other actions to the query loop that need to be considered. "Normal" code though would be to change the while to a foreach and other changes likely identified: https://docs.zen-cart.com/dev/code/d...-row-responses

Mind you that wasn't exactly easy to find, but it is an example of differences.

[mc12345678]

What I did in a plugin that still bridges Zen Cart versions was to test for the existence of the method, if it existed then do the rewind, if it didn't then use the ->Move(0) operation then do a ->MoveNext() operation.

 if (method_exists($results, 'rewind')) {
 $results->rewind();
} else {
 $results->Move(0);
 $results->MoveNext();
}

With that being any time after initially touching $results, but before the next desired access of the original $results.

[Dave224]

Thank you so much mc. $results->rewind() did the trick! Donation on the way.
Dave