Display the result of an SQL search in multiple places

Question

I am trying to display the results of an SQL search in multiple places, but I can only seem to get it to print once (the first time it is called).

Here is what I have:

 $stmt = $connection->prepare("SELECT DISTINCT genre FROM movies");
 $stmt->execute();
 $results = $stmt->get_result();

 while ($row = mysqli_fetch_array($results)){
     print ($row['genre'] . "<br>");
 }

It makes sense to me that doing this "uses up" all of the results, so if I were to call the while again, there would be nothing, and therefore it would print nothing.

So, is it possible to copy the results to another variable for later use? I have tried:

$results2 = $results;


$results2 = array();
$results2 = $results;


$results2 = new stdClass();
$results2 = $results;

But I keep getting the same result... and empty array after the first call.

Do I just need to call the SQL query again? It seems like there's gotta be a better way.

http://php.net/manual/en/mysqli-result.fetch-all.php Fetch all results to an array and then do with it as you please. Or catch each row into an array in your while loop [like this](https://stackoverflow.com/questions/3351882/convert-mysqli-result-to-json) — JNevill, Mar 05 '18 at 15:21
@JNevill the fetch all is what I wanted. Please submit this as the answer and I will mark it correct. — Apolymoxic, Mar 05 '18 at 15:31
Looks like @nigelren has it covered ;) I'm glad that did the trick! — JNevill, Mar 05 '18 at 15:33
@JNevill Happy to delete my answer if you add one. Just sometimes answers get left in comments and it doesn't 'clear' the question. — Nigel Ren, Mar 05 '18 at 15:34

score 0 · Accepted Answer · answered Mar 05 '18 at 15:32

As mentioned in the comments, instead of fetching them each time, store the results and then simply loop over them each time you need to use them...

 $stmt = $connection->prepare("SELECT DISTINCT genre FROM movies");
 $stmt->execute();
 $results = $stmt->get_result();
 $movies = mysqli_fetch_all($results);

 foreach ( $movies as $row){
     print ($row['genre'] . "<br>");
 }

Display the result of an SQL search in multiple places

1 Answers1