Looping Through Query Results Multiple Times with MySQLi_Fetch_Array

How can I use mysqli_fetch_array() twice?

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

PHP: cannot loop through mysql rows more than once

This is the error

while ($row = mysql_fetch_array($result)) {
   $total_images++;
}

once you fetched the array the array pointer set to the end.

use this

 $total_images=mysql_num_rows($result);

and

$images_to_offset=mysql_num_rows($result);

OR

To reset the position of pointer Use mysql_data_seek(). it moves internal result pointer

How do I iterate over the results in a MySQLi result set?

You need to define a array and store your data into array inside loop .
Use MYSQLI_ASSOC no need for incremented value

$deviceToken=array();
while ($row = $output->fetch_array(MYSQLI_ASSOC)) {
    $deviceToken[] = $row['uid'];
}
print_r($deviceToken);
for($i=0;$i<=count($deviceToken);$i++){
echo $deviceToken[$i];
}

How to use mysql query multiple times

You should read all the rows into an array when you first run the query, and then iterate that array in your for loop e.g.

$sql_kwal = "SELECT
            id,
            kwaliteit
            FROM kwaliteit
            ORDER BY kwaliteit ASC
            ";

if(!$res_kwal = mysqli_query($mysqli, $sql_kwal)) { echo '<div class="alert alert-danger text-center" role="alert">Er is helaas iets fout gegaan.</div>'; }
$rows = array();
while ($row = mysqli_fetch_array($res_kwal)) {
    $rows[] = $row;
}

Then in your loop, replace

while ($row = mysqli_fetch_array($res_kwal)) {

with

foreach ($rows as $row) {

Note that if you have the MySQL native driver (mysqlnd) installed, you can use

$rows = mysqli_fetch_all(MYSQLI_BOTH);

in place of the while loop to load the $rows array.

Note

If you have a particularly large result set that is impractical to load into memory, then you can instead use mysqli_data_seek to reset the data pointer before you start reading from the result set. So where you have

while ($row = mysqli_fetch_array($res_kwal)) {

change it to

mysqli_data_seek($res_kwal, 0);
while ($row = mysqli_fetch_array($res_kwal)) {

Reuse a mysql query resultset on same PHP page

Honestly, you've already done 99% of the work.

You can replace this:

while ($row = mysqli_fetch_assoc($resultAuthor)) {
  if ($row['book_author_first_name_only'] == 0 ) {
    echo "<a class='fw-bold author-link' href='#" . $row['book_id'] . "'>" . $row['book_author_first_name'] . ' ' . $row['book_author_last_name'] . "</a>";
  } else {
    echo "<a class='fw-bold author-link' href='#" . $row['book_id'] . "'>" . $row['book_author_first_name'] . "</a>";
  }
}

With:

$rows = mysqli_fetch_all($resultAuthor, MYSQLI_ASSOC);

To fetch all the rows at once and store them in an array ($rows).

you'll notice this looks very similar to the 1-record-at-a-time version you were using in your while (...) { } loop.

Now you can simply iterate over this data as often as you like:

foreach ($rows as $row) {
    if ($row['book_author_first_name_only'] == 0 ) {
        echo "<a class='fw-bold author-link' href='#" . $row['book_id'] . "'>" . $row['book_author_first_name'] . ' ' . $row['book_author_last_name'] . "</a>";
    } else {
        echo "<a class='fw-bold author-link' href='#" . $row['book_id'] . "'>" . $row['book_author_first_name'] . "</a>";
    }
}

// and again
foreach ($rows as $row) {
    // ... do something else with each $row
}

// and again
foreach ($rows as $row) {
    // ... do something else with each $row
}

using php for loop for mysqli_fetch_array

There's no reason why you can't use a while loop. That being said, when you now use sizeof($list), you will get the number of columns selected from the query (this $list variable has as many elements as columns was selected). You will therefor attempt to loop (and ouput the count of) the number of columns you fetched.

Instead, you might want to do something like this below - fetch the number of rows based on mysqli_num_rows(), which is exactly that - the number of rows returned by the query.

for ($x = 0; $x <= mysqli_num_rows($result); $x++) {
    $row = mysqli_fetch_array($result);
    var_dump($row);
}

Although, the standard way - and in my opinion the best way - of doing it is via while.

while ($row = mysqli_fetch_array($result)) {
    var_dump($row);
}

How can I loop through a MySQL result set more than once using the mysql_* functions?

This is how you can do it:

$result = mysql_query(/* Your query */);
while($row = mysql_fetch_assoc($result)){
 // do whatever here...
}

// set the pointer back to the beginning
mysql_data_seek($result, 0);
while($row = mysql_fetch_assoc($result)){
 // do whatever here...
}

However, I would have to say, this doesn't seem the right way to handle this. Why not do the processing within the first loop?

---

Related Topics