PHP: cannot loop through mysql rows more than once

Question

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!

Answer 1

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

Answer 2

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.

Answer 3

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

mysql_data_seek($result, 0); 

Also see: http://ca2.php.net/manual/en/function.mysql-data-seek.php

Answer 4

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;