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.
Add a comment |
7 Answers
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?
13 Comments
A-OK
why mysql_fetch_assoc()? can I use mysql_fetch_array()
Shef
@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
@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. :)
A Friend
mysqli_data_seek($result, 0); if you're using prepared statements :)
|
For mysqli you should do the following;
$result= $con->query($sql); // $con is the connection object $result->data_seek(0); 
Comments
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
Comments
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 } 
1 Comment
Krista K
I wrote a helper function I call multiRow() which does all that and just returns and array of array rows.
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?
Comments
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.
Comments
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.
