How can I use mysqli_fetch_array() twice?

Question

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?

Dharman · Accepted Answer · 2020-04-03 18:32:31Z

15

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

edited Apr 3, 2020 at 18:32

Dharman♦

34k27 gold badges106 silver badges157 bronze badges

answered Sep 10, 2013 at 13:44

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nl-x Over a year ago

As long as no mysqli_free_result() is used, this solution would just double the memory used by the query.

Community · Accepted Answer · 2023-11-17 20:08:53Z

13

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

edited Nov 17, 2023 at 20:08

CommunityBot

11 silver badge

answered Sep 10, 2013 at 13:43

nl-x

11.9k7 gold badges36 silver badges67 bronze badges

2 Comments

powtac Over a year ago

The answer might be problematic when you call mysqli_data_seek($db_res, 0); while still looping trough the results. Then it will run "forever".

Marvin Over a year ago

@powtac They are not putting this in the loop, but rather after it.

Dharman · Accepted Answer · 2021-09-08 20:41:29Z

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...
}

Dinesh · Accepted Answer · 2013-09-10 13:52:14Z

2

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

answered Sep 10, 2013 at 13:52

Dinesh

4471 gold badge6 silver badges22 bronze badges

Collectives™ on Stack Overflow

How can I use mysqli_fetch_array() twice?

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related