How do I Duplicate MySQL Query Results?

OXiGEN

Hi,

I need to know how to duplicate query results using MySQL/PHP. The simple approach of just copying the result variable does NOT work:

$result = mysql_query("SELECT ...");
$resultcopy = $result;
while ($row = mysql_fetch_array($result)) { ... }
while ($row = mysql_fetch_array($resultcopy)) { ... }

With this approach, the first while will work as intended. However, the second while (upon the copy) appears to be using an empty result. For testing purposes, I even copy+pasted the same code within the first while to the second while (so if it worked, I would see duplicate sets of outputs).

My guess is this approach does not work because $resultcopy = $result; is simply copying the resource id, instead of duplicating the resource. So, is there a way to actually duplicate the query result resource?

As it is, I'm having to redo the same exact query before each while. This is a bit taxing since it's a complex query on 200 x 20,000 rows generally producing a 10 row result. It would be much more efficient if I could simply duplicate the 10 row result for later use. =/

I've tried searching the manual, but nothing seems to cover this. Please help. Thanks.

nemonoman

Why not fetch all the results, and then save them for when you need them -- in an array in memory, if it's just one process, or in a flat file, or a reporting table of your database set up for only this purpose?

tomhath

http://www.phpbuilder.com/manual/function.mysql-data-seek.php

OXiGEN

Thanks Tom and Nem. mysql_data_seek is a more efficient solution, and is working perfectly in test cases.

Now I just have to figure out how to get resources to persist over simultaneous iframe loads (user clicks a query in parent window, then same result goes to 2 separate iframes). Too bad $SESSION can't handle resources yet. Looks like I may have to convert the results to arrays of arrays after all, then pass the array through $SESSION. How I shall miss the simplicity of mysql_fetch_array in that event. =/

Judging from the manual, it seems nested loops will be required to imitate the natural functionality of tables (unless there's a whole set of Table functions that I'm too blind to see!). So, Nem, is there a convinient mysql result -> array type conversion function somewhere? Or will I have to manually loop mysql_fetch_array?