How can I use mysqli_fetch_array() twice?

I am using the entries from a database to fill a row and a column in a table. But I cannot access the SQL returned data twice using mysqli_fetch_array() twice. I need to loop mysqli result more than once. 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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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 since PHP 5.3 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>";
}

Method 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);

Method 3

You don’t need the while loop and you don’t need to use mysqli_fetch_array() at all!

You can simply loop on the mysqli_result object itself many times. It implements Traversable interface that allows it to be used 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 your DB logic from your display logic and to achieve this it is best to use fetch_all(MYSQLI_ASSOC) in your DB logic to retrieve all records into an array.

If you fetch all the data into an array, you can loop that array as many times as you want.

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

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

Method 4

$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…..

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating