8

I have a result set I pull from a large database:

$result = mysql_query($sql);

I loop through this recordset once to pull specific bits of data and get averages using while($row = mysql_fetch_array($result)). Later in the page, I want to loop through this same recordset again and output everything - but because I used the recordset earlier, my second loop returns nothing.

I finally hacked around this by looping through a second identical recordset ($result2 = mysql_query($sql);), but I hate to make the same SQL call twice. Any way I can loop through the same dataset multiple times?

MarathonStudios
  • 3,983
  • 10
  • 40
  • 46

2 Answers2

22

Use:

mysql_data_seek($result, 0);

You get this "free", since it's already buffered.

As a separate note, you can explicitly do an unbuffered query with mysql_unbuffered_query.

Matthew Flaschen
  • 278,309
  • 50
  • 514
  • 539
  • Matt, this worked like a charm! You saved me alot of unneeded load time, thanks. – MarathonStudios Jan 09 '11 at 07:21
  • @Col, why should he copy it to an array when it's **already buffered**? If you're going to down-vote, there should be something wrong with the answer. "You could do some unnecessary copying instead" doesn't qualify. – Matthew Flaschen Jan 09 '11 at 10:50
  • @Col, you're making a lot of assumptions. How do you know he's copying all of it to an array initially? He said he only needed "specific bits." Would you still have him copy all columns if one was a BLOB? You're also assuming when he says average he means a straight arithmetic mean. It might be weighted. And even if you're right that he could use SQL's `AVG`, that doesn't answer the question. – Matthew Flaschen Jan 09 '11 at 11:09
  • @Col, yeah, I *do* actually consider whether the answer is helpful before voting, up or down. – Matthew Flaschen Jan 09 '11 at 11:10
  • What's what you live for: answer questions. No mind, no expertize, not even common sense - but just straight literal answer. That poor guy, who asked doctor for operation and messed up circumcision with castration, come to the hands of your kind. – Your Common Sense Jan 09 '11 at 11:13
  • This is the answer to the question he asked. With different info, there might be a different answer. You had no problem telling him to copy it all to an array with no idea how large the row was. And you haven't answered the question with any useful information about `AVG`. That's because there isn't enough information to do so. – Matthew Flaschen Jan 09 '11 at 11:19
0

Using SQL Cursors you can get this approach

Sudantha
  • 15,684
  • 43
  • 105
  • 161