66

For whatever reason, I need to go through a MySQL result set twice. Is there a way to do it?

I don't want to run the query twice and I don't want to have to rewrite the script so that it stores the rows somewhere and then reuses them later.

Dharman
  • 30,962
  • 25
  • 85
  • 135
A-OK
  • 3,184
  • 10
  • 34
  • 42

7 Answers7

112

This is how you can do it:

$result = mysql_query(/* Your query */);
while($row = mysql_fetch_assoc($result)){
 // do whatever here...
}

// set the pointer back to the beginning
mysql_data_seek($result, 0);
while($row = mysql_fetch_assoc($result)){
 // do whatever here...
}

However, I would have to say, this doesn't seem the right way to handle this. Why not do the processing within the first loop?

Shef
  • 44,808
  • 15
  • 79
  • 90
  • 1
    why mysql_fetch_assoc()? can I use mysql_fetch_array() – A-OK Jun 22 '11 at 11:46
  • A-OK: Yes, whatever you prefer. – phant0m Jun 22 '11 at 11:47
  • Yes, you can. It's the same thing, just different ways of fetching it (column name/column number). – Andreas Eriksson Jun 22 '11 at 11:48
  • @A-OK: Yes. That part of the answer is just for context. – Lightness Races in Orbit Jun 22 '11 at 11:49
  • 1
    @A-OK: To answer your question, yes, you can use `mysql_fetch_array()`. You seem to have a peculiar taste for doing things "your way". I demonstrated with `mysql_fetch_assoc()`, because it will fetch the results only into an associative array and not both an integer indexed and associative one, which is what `mysql_fetch_array()` does. – Shef Jun 22 '11 at 11:49
  • Oh I just didn't know what mysql_fetch_assoc does. Obviously it's more practical than mysql_fetch_array so there, I learned TWO useful things today. :) – A-OK Jun 22 '11 at 11:54
  • No, it's not more practical, it's just different, that is all! Basically, `mysql_fetch_array()` is a shorthand for `mysql_fetch_array($result, MYSQL_FETCH_ASSOC)` – phant0m Jun 22 '11 at 11:56
  • As for why not do everything in the first loop, it's because I need to include a different page and a different css depending on whether the result set contains even one specific value that I'm looking for, so I need to know that before I start displaying results. Of course I could store the results in an array and reuse them later but that would require rewriting a lot of the script that I didn't write in the first place and mysql_data_seek() is just one extra line. :) – A-OK Jun 22 '11 at 11:58
  • 2
    @A-OK: Okay, then, this solution would cover your bad style of coding with some more bad style of coding, instead of inspiring you to rewrite your code in the first place. If you need a quick one, this will do it, but I would suggest you better rewrite before it's too late. :) – Shef Jun 22 '11 at 12:01
  • There's nothing wrong with looping through a result set twice. If it's the easiest and cleanest solution, do it. – Vincent Feb 25 '18 at 15:52
  • 2
    This was deprecated in PHP 5.5.0 and removed in PHP 7.0. Use `mysqli_data_seek` instead. –  Apr 12 '18 at 08:28
  • Lots of reasons to do it twice. For me, first time through to process each transaction and display some info about it, then when done, display a nice table with the results. Can't easily do both in a single loop. – Gary Samad Apr 26 '18 at 03:32
  • 6
    mysqli_data_seek($result, 0); if you're using prepared statements :) – A Friend Jul 09 '18 at 19:14
12

For mysqli you should do the following;

$result= $con->query($sql); // $con is the connection object
$result->data_seek(0);
guitarlass
  • 1,587
  • 7
  • 21
  • 45
11

Try whether mysql_data_seek() does what you need.

mysql_data_seek() moves the internal row pointer of the MySQL result associated with the specified result identifier to point to the specified row number. The next call to a MySQL fetch function, such as mysql_fetch_assoc(), would return that row.

row_number starts at 0. The row_number should be a value in the range from 0 to mysql_num_rows() - 1. However if the result set is empty (mysql_num_rows() == 0), a seek to 0 will fail with a E_WARNING and mysql_data_seek() will return FALSE

phant0m
  • 16,595
  • 5
  • 50
  • 82
4

I confess I haven't tried this, but have you tried after your first iteration

mysql_data_seek($queryresult,0);

to go to the first record?

Eran
  • 387,369
  • 54
  • 702
  • 768
BugFinder
  • 17,474
  • 4
  • 36
  • 51
4

Alternative to the data seek is to store the values into an array:

$arrayVals = array();
$result = mysql_query(/* Your query */);
while($row = mysql_fetch_assoc($result)){
    $arrayVals[] = $row;
}

// Now loop over the array twice instead

$len = count($arrayVals);
for($x = 0; $x < $len; $x++) {
    $row = $arrayVals[$x];

    // Do something here    
}

$len = count($arrayVals);
for($x = 0; $x < $len; $x++) {
    $row = $arrayVals[$x];

    // Do something else here   
}
Clintonio
  • 434
  • 5
  • 11
  • I wrote a helper function I call multiRow() which does all that and just returns and array of array rows. – Krista K Jan 11 '18 at 18:45
3

You can use mysql_data_seek to move the internal pointer to the beginning of the data set. Then, you can just iterate through it again.

Steve
  • 2,023
  • 3
  • 17
  • 25
0

Well, you could always count the number of rows you read, and then do something like this:

if (rownumber == mysql_num_rows($result)) { mysql_data_seek($result, 0); }

Don't know why you would need to, but there it is.

Andreas Eriksson
  • 8,979
  • 8
  • 47
  • 65