Reuse a mysql query resultset on same PHP page

Question

I have a working resultset that is looped through during runtime to present a list of authors and links. Works correctly. I need to be able to display this same dataset two times in the same page. I'd rather not run the same query twice. How can I set the resultset (the display output shown below) as a var, and get the same output at both presentation points? I saw something a while back about using a for each loop, but not sure how to do that.

The query:

if($stmtr = mysqli_prepare($link, $sql8)){
  // Bind variables to the prepared statement as parameters
  mysqli_stmt_bind_param($stmtr, "s", $param_stripurlslash);

// Set parameters
  $param_stripurlslash = $stripurlslash;

// Attempt to execute the prepared statement
if(mysqli_stmt_execute($stmtr)){
    $resultAuthor = mysqli_stmt_get_result($stmtr);
    $usera = mysqli_fetch_assoc($resultAuthor, MYSQLI_ASSOC);              
} else {
    echo "Oops! Something went wrong. Please try again later.";
} 

}

The display output:

<?php                         
    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'] . '&nbsp;' . $row['book_author_last_name'] . "</a>";
      } else {
        echo "<a class='fw-bold author-link' href='#" . $row['book_id'] . "'>" . $row['book_author_first_name'] . "</a>";
      }
    }
?>

Try `mysql_data_seek($resultAuthor, 0);`. [How can I loop through a MySQL result set more than once using the mysql\_\* functions?](https://stackoverflow.com/questions/6439230/how-can-i-loop-through-a-mysql-result-set-more-than-once-using-the-mysql-funct) — Worthwelle, Dec 25 '21 at 03:30

score 1 · Accepted Answer · answered Dec 25 '21 at 03:30

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'] . '&nbsp;' . $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'] . '&nbsp;' . $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
}

Reuse a mysql query resultset on same PHP page

1 Answers1