How to use mysqli_fetch_array() twice?

Question

I am using entries from the database to populate the rows and columns in the table. But I cannot access the data returned by SQL using mysqli_fetch_array() twice. I need to loop through mysqli results multiple times. 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 to apply mysqli_fetch_array twice to the same result?

Answer 1

You should always separate data manipulation from output.

First select your data:

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

Note that starting with PHP 5.3 you can use fetch_all() instead of an explicit loop:

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

Then use it as many times as needed:

//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>";
}

Answer 2

You don't need a while loop, and you don't need to use mysqli_fetch_array() at all!

You can simply loop multiple times over the mysqli_result object itself. It implements the Traversable interface, allowing use in foreach.

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

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

However, you should separate the database logic from the display logic, in order to achieve this, it is best to use fetch_all(MYSQLI_ASSOC) in the database logic to retrieve all records into an array.

If you extract all the data into an array, you can loop through the array as many times as needed.

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

foreach($data as $row) {
    // logic here...
}