How can I use mysqli_fetch_array() twice?

Question

I am using the entries of a db to fill a row and a column in a table. But I cannot access the SQL returned data twice using mysqli_fetch_array() twice. This doesn't work:

//Copy the result
$db_res = mysqli_query( $db_link, $sql );
$db_res2=$db_res;

//Top row
while ($row = mysqli_fetch_array( $db_res, MYSQL_ASSOC))
{
        echo "<td>". $row['Title'] . "</td>";
}

//leftmost column
while ($row = mysqli_fetch_array( $db_res2, MYSQL_ASSOC))
{
                    echo "<tr>";
        echo "<td>". $row['Title'] . "</td>";
                    .....
                    echo "</tr>";
}

How can I apply mysqli_fetch_array twice on the same result?

Answer 1

$squery = mysqli_query($con,"SELECT * FROM table");

while($s = mysqli_fetch_array($query)){
 ....
}
// add this line
mysqli_data_seek( $query, 0 );

while($r = mysqli_fetch_array($query)){
 ...
}

try it.....

Answer 2

Yes. mysqli_fetch_array() moves the pointer forward each time you call it. You need mysqli_data_seek() to set the pointer back to the start and then call mysqli_fetch_array() again.

So before calling the function a second time, do:

mysqli_data_seek($db_res, 0);

Answer 3

You should always separate data manipulations from output.

Select your data first:

$db_res = mysqli_query( $db_link, $sql );
$data   = array();
while ($row = mysqli_fetch_assoc($db_res))
{
    $data[] = $row;
}

Note that in the recent PHP versions you can use fetch_all() instead of the explicit loop:

$db_res = mysqli_query( $db_link, $sql );
$data   = $db_res->fetch_all(MYSQLI_ASSOC);

Then use it as many times as you wish:

//Top row
foreach ($data as $row)
{
        echo "<td>". $row['Title'] . "</td>";
}

//leftmost column
foreach ($data as $row)
{
                    echo "<tr>";
        echo "<td>". $row['Title'] . "</td>";
                    .....
                    echo "</tr>";
}