Reuse a mysql query resultset on same PHP page [duplicate]

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? — Worthwelle
– Worthwelle, Commented Dec 25, 2021 at 3:30

Raxi · Accepted Answer · 2021-12-25 03:30:14Z

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
}

Collectives™ on Stack Overflow

Reuse a mysql query resultset on same PHP page [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related