Mysqli_fetch_assoc($result), pointer moves to the next record. Is there any way to reset the pointer to the start of the query result?

Question

Have a look at this code

Suppose you are looping through a set of mysql query results in php

while($temp = mysqli_fetch_assoc($result))
{
 echo $temp['id']; // ID Column
}

When you do $temp=mysqli_fetch_assoc($result), basically the pointer moves to the next record. Is there any way to reset the pointer to the start of the query? As after the end of this loop mysqli_fetch_assoc($result) will only return empty rows, making it unusable again. So what's the possible solution?

Answer 1

So I was stuck with this problem at work today, and the only solution I initially found was to re-query, or use temporary copy of mysql result in a variable. Neither of which were appealing.

There is a much simpler solution to this which is mysql_data_seek.

Basic syntax is mysqli_data_seek(data,row)

So in this case you just do

mysqli_data_seek($result,0);
$row=mysqli_fetch_assoc($result);// Will now return the first row.

In a similar way you could loop through it again too.

It works similarly with mysql_data_seek. Hope it was helpful.

Answer 2

1. Printing data directly out off the database loop is a very bad practice. You need to separate the business logic from the presentation layer.
2. Get the query result into array, for example, using mysqli_fetch_all()
3. Use this array as many times as you wish.

Like this

$data = $result->fetch_all(MYSQLI_ASSOC);

foreach ($data as $row) // 1st iteration
foreach ($data as $row) // 2nd iteration
foreach ($data as $row) // and so on