Mysqli_fetch_assoc($result), pointer moves to the

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. Quit that thing of printing data directly out of database loop. Learn to separate your business logic from presentation.
2. Get yourself a database wrapper, to get rid of all these ugly numerous mysqli_fetch_assoc from your code.
3. Store query result in array.
4. Use this array as many times as you wish.

Like this

$data = $db->getAll("SELECT * FROM table");

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