PHP: why can't I loop twice on mysqli_fetch_ar

Question

This question already has answers here:

Mysqli query doesn't work twice (2 answers)

Closed 2 years ago.

I'm retrieving some data from a mysql database. There are two loops which uses the same result set.

while ($row = mysqli_fetch_array($newsQuery)) {
     echo "<a href='news-article.php?articleId=" .$row["news_id"]."' class='list-group-item active'>".$row["news_title"]."</a>";
}

This loop ends with success. then in the same page I have the following loop.

while ($row = mysqli_fetch_array($newsQuery)) {
    echo $row["news_content"];
}

This loop doesn't return anything even if there are content in the table. When I try it in the first loop. the content is displayed correctly. Any idea on what I'm doing wrong.

Answer 1

Because you already fetch the value of $newsQuery Try this code

//temporary save the news content into array
$content = array();
$x=0;
while ($row = mysqli_fetch_array($newsQuery)) {
 echo "<a href='news-article.php?articleId=" .$row["news_id"]."' class='list-group-item     active'>".$row["news_title"]."</a>";
 $content[$x]= $row["news_content"];
 $x++;
}

and if you want to retrieve it

//now retrieve it
for($y=0; $y<count($content); $y++)
{
 echo $content[$y]+"<br/>";

}

Answer 2

From PHP's mysqli_fetch_array DOCS:

Returns an array that corresponds to the fetched row or NULL if there are no more rows for the resultset represented by the result parameter.

You are using a 'while' loop on $row = mysqli_fetch_array($newsQuery)

This means the loop will keep going untill mysqli_fetch_array($newsQuery) returns NULL.

This is the reason why you cant use that loop again, since mysqli has finished fetching the results and the mysqli_fetch_array($newsQuery) now returns NULL untill you make a new query.

Try populating a variable with the results first, then loop on that variable:

$results = array();
while ($row = mysqli_fetch_array($newsQuery)) {
     $results[] = $row;
}

foreach ($results as $key => $row) {
    echo "<a href='news-article.php?articleId=" .$row["news_id"]."' class='list-group-item active'>".$row["news_title"]."</a>";
}


foreach ($results as $key => $row) {
    echo $row["news_content"];
}

Answer 3

While it is best to avoid logic that results in looping through a result set twice, if necessary you need to reset the result set back to the start:-

mysqli_data_seek($newsQuery, 0);