CODEWITHSUNDEEP
phpmysql
user3871
user3871

Reputation: 12718

PHP: cannot loop through mysql rows more than once

I'm working on pagination, and for some reason I cannot use mysql_fetch_array to loop through a result more than once.

//both $page and $imagesPerPage != 0
$offset = $page * $imagesPerPage
while ($row = mysql_fetch_array($result)) {
   $total_images++;
}
echo $total_images;
//echos correct amount
$row = null;


$images_to_offset = 0;
while ($row = mysql_fetch_array($result) && $images_to_offset < $offset) {
   $images_to_offset++;
}
echo $images_to_offset;
//echos 0... which is wrong

Should I be using a different php function to get row data in a database?

Thanks!

Upvotes: 6

Views: 3576

Answers (7)

mr_lewjam
mr_lewjam

Reputation: 1190

You can only loop through the results array once, after that they effectively 'disappear'. the way to loop over the results multiple times is to store them into a new array during the first loop, then loop over the new array as many times as you want...

Upvotes: 1

Nelson Ben&#237;tez Le&#243;n
Nelson Ben&#237;tez Le&#243;n

Reputation: 50563

Try using mysql_num_rows() , that way you don't have to iterate the $result twice which is giving you error in the second loop as you have to reset the result pointer. So do it like this which only iterates once:

//both $page and $imagesPerPage != 0
$offset = $page * $imagesPerPage
$total_images = mysql_num_rows($result);
echo $total_images;
//echos correct amount
$row = null;


$images_to_offset = 0;
while ($row = mysql_fetch_array($result) && $images_to_offset < $offset) {
   $images_to_offset++;
}
echo $images_to_offset;

As a side note, you should try to migrate to MySQLi or PDO_MySQL to access mysql as the interface you are using is now deprecated, see the red box in http://es.php.net/manual/en/function.mysql-num-rows.php

Upvotes: 1

StaticVariable
StaticVariable

Reputation: 5283

This is the error

while ($row = mysql_fetch_array($result)) {
   $total_images++;
}

once you fetched the array the array pointer set to the end.

use this 

 $total_images=mysql_num_rows($result);

and 

$images_to_offset=mysql_num_rows($result);

OR

To reset the position of pointer Use mysql_data_seek(). it moves internal result pointer

Upvotes: 8

Bjoern
Bjoern

Reputation: 16304

mysql_fetch_array not only returns an array that corresponds to the fetched row, it also moves the internal data pointer ahead.

There are more than one way of dealing with this, the most obvious is to "park" your result in an array itself and then work from there. Or query 2 times. Or use mysql_data_seek. In your case, maybe mysql_num_rows is more appropriate, since your code indicates you only want to know how many rows you have to iterate through, and thats what this function is there for.

Whatever decide, keep in mind that use of the mysql extension is discouraged. Instead, the MySQLi or PDO_MySQL extension should be used.

Upvotes: 1

JvdBerg
JvdBerg

Reputation: 21856

You have to 'rewind' the array, by using the mysql_data_seek function:

$offset = $page * $imagesPerPage
while ($row = mysql_fetch_array($result)) {
   $total_images++;
}
echo $total_images;
//echos correct amount
$row = null;

mysql_data_seek(); // <-- rewind to the beginning

$images_to_offset = 0;
while ($row = mysql_fetch_array($result) && $images_to_offset < $offset) {
   $images_to_offset++;
}
echo $images_to_offset;

Upvotes: 1

Sebass van Boxel
Sebass van Boxel

Reputation: 2602

You could point the pointer back to the first row with mysql_data_seek.

mysql_data_seek($result, 0);

Also see: https://www.php.net/manual/en/function.mysql-data-seek.php

Upvotes: 2

Jonathan M
Jonathan M

Reputation: 17451

If you wish to start fetching from the beginning after you've already fetched, you'll need you use mysql_data_seek().

Also, please note that the mysql line of functions have been deprecated, and the community is encouraging use instead of MySQLi or PDO_MySQL lines of functions.

Upvotes: 4

Related Questions