fetch_assoc() works only once in a loop

Question

I'm looping through 2 tables in MySQL DB (using fetch_assoc()). I would like to get the current id of the 1st table and all the ids of the second table on each iteration but I get the ids of the second table only on the first iteration. From the second iteration upwards, only the current id of the 1st table is returned. I would like to know what I'm doing wrong.

I've already tried for Loops and looked up similar questions here but none have really been of help.

<?php
$my_sqli = new mysqli('localhost', 'root', '', 'taskpro') or die(mysqli_error($my_sqli));
$data1 = $my_sqli->query("SELECT * FROM task_table") or die($my_sqli->error);
$data2 = $my_sqli->query("SELECT * FROM taskinfo") or die($my_sqli->error);

while ($row = $data1->fetch_assoc()) {
    echo "<br>";
    echo $row['id'];
    echo "<br>";

    while ($row2 = $data2->fetch_assoc()) {
        echo $row2['id'];
    } // end child loop

} // end parent loop

?>

This is the result I get

1

1234

2

3

4

5

6

7

8

9

10

Because you have consumed ALL the resultset from the second query the first time round the outer loop. Basically got to EndOfFile so there is nothing more to fetch — RiggsFolly, Oct 31 '19 at 11:19
Yes this is how it works, you need to `requery`. if you want to fetch the data again. Or kindly store both datas in array first and then do your operations. — Dhaval Purohit, Oct 31 '19 at 11:22

score 2 · Accepted Answer · answered Oct 31 '19 at 11:24

The simplest thing to do is to read all the values from query2 into an array and then output the contents of the array in the loop:

$my_sqli = new mysqli('localhost', 'root', '', 'taskpro') or die(mysqli_error($my_sqli));
$data1 = $my_sqli->query("SELECT * FROM task_table") or die($my_sqli->error);
$data2 = $my_sqli->query("SELECT * FROM taskinfo") or die($my_sqli->error);

$rows2 = $data2->fetch_all(MYSQLI_ASSOC);
while ($row = $data1->fetch_assoc()) {
    echo "<br>";
    echo $row['id'];
    echo "<br>";

    foreach ($rows2 as $row2) {
        echo $row2['id'];
    } // end child loop

} // end parent loop

If for some reason you do need to iterate over the result set in the outer loop, you can use mysqli_data_seek to reset the pointer:

while ($row = $data1->fetch_assoc()) {
    echo "<br>";
    echo $row['id'];
    echo "<br>";

    while ($row2 = $data2->fetch_assoc()) {
        echo $row2['id'];
    } // end child loop
    // reset $data2 result pointer
    $data2->data_seek(0);

} // end parent loop

Thanks for the help, I actually tried reading the results into an array (not using fetch_all() ) but with little luck. Thanks for shedding more light on this :) — konnichiwa, Oct 31 '19 at 11:31

score 0 · Answer 2 · answered Oct 31 '19 at 11:29

Basically you have exhausted the resultset of the second query after completing the first iteration of the outer query.

There are a few ways to do this, here are a couple of suggestions

First you could load all of the results from the second query into an array and then just re-process the array each time round the outer loop. However, the fetch_all() funtion is only available if you have the MYSQL Native Driver installed. (MYSQLIND)

<?php
$my_sqli = new mysqli('localhost', 'root', '', 'taskpro');
if (!$my_sqli) {
    die('Connect Error: ' . mysqli_connect_errno());
}

$data1 = $my_sqli->query("SELECT * FROM task_table") or die($my_sqli->error);
$data2 = $my_sqli->query("SELECT * FROM taskinfo") or die($my_sqli->error);

$allData2 = data2->fetch_all(MYSQLI_ASSOC);

while ($row = $data1->fetch_assoc()) {
    echo "<br>";
    echo $row['id'];
    echo "<br>";

    foreach ( $allData2 as $row ) {
        echo $row2['id'];
    }
} // end parent loop
?>

Or you could use the mysqli_result::data_seek() function to reset the pointer to the first row of the query result like this

<?php
$my_sqli = new mysqli('localhost', 'root', '', 'taskpro');
if (!$my_sqli) {
    die('Connect Error: ' . mysqli_connect_errno());
}

$data1 = $my_sqli->query("SELECT * FROM task_table") or die($my_sqli->error);
$data2 = $my_sqli->query("SELECT * FROM taskinfo") or die($my_sqli->error);

while ($row = $data1->fetch_assoc()) {
    echo "<br>";
    echo $row['id'];
    echo "<br>";

    $data2->data_seek(0);   // make sure the pointer is at the beginning before looping
    while ($row2 = $data2->fetch_assoc()) {
        echo $row2['id'];
    } 
}
?>

score 0 · Answer 3 · answered Oct 31 '19 at 19:41

First of all, you don't need the while loop at all. It is just confusing you. You could simply use foreach($data1 as $row). Having said that, it would not solve your problem.

To solve your problem call fetch_all(MYSQLI_ASSOC) on the result from query, or simply chain them together. Be careful, this works only with static queries. If you need to use variables in your SQL, you should use prepared statements.

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new mysqli('localhost', 'root', '', 'taskpro');
$mysqli->set_charset('utf8mb4');

$data1 = $mysqli->query("SELECT * FROM task_table")->fetch_all(MYSQLI_ASSOC);
$data2 = $mysqli->query("SELECT * FROM taskinfo")->fetch_all(MYSQLI_ASSOC);

foreach ($data1 as $row) {
    echo "<br>";
    echo $row['id'];
    echo "<br>";
    foreach ($data2 as $row2) {
        echo $row2['id'];
    } // end child loop
} // end parent loop

I have also removed the manual error checking from your code, which was only obstructing your code and should really not have place in your working code. Read How to get the error message in MySQLi?.

fetch_assoc() works only once in a loop

3 Answers3

Linked