PHP: using SQL result in a loop without re-executing query

Question

I am trying to add 3 combo boxes which all display the exact same information that comes from my MySQL db. It seems like the code I wrote makes the entire page wait until all 3 combo boxes are populated, before continuing.

<?
$query = "Select * from tblWriters order by surname";

for ($i = 1; $i <= 3; $i++) {

    $result = mysql_query($query);

    echo "<tr><td>Writer".$i." *</td><td>";
    echo "<select name='txtWriter".$i."' style='width: 200px;'>";
    echo "<option value ='' selected='selected'></option>";

    while ($row = mysql_fetch_array($result))
    {
         echo "<option value ='" . $row['id'] . "'> " . $row['surname'] . ", " . $row['name'] . "</option>";
    }

    echo "</select><td></tr>";
}
?>

I would like to optimize this piece of code, so the query will not be executed 3 times, as I believe this is where the page slows down.

If I put

$result = mysql_query($query);

outside of the for loop, the 2nd and 3rd combo box do not populate. I tried looking into resetting the pointer of the result, but I can't seem to figure out how that works.

Also, is there a way I can reuse the while loop, so I don't have to execute it 3 times?

Can someone point me in the right direction? I'm pretty new to PHP and trying to learn on my own. Any help would be much appreciated. Thanks!

Answer 1

If you move your mysql_query() out of the loop again, you can reset your mysql-result-pointer by using mysql_data_seek() at the beginning or end of your loop.

This will result in:

 mysql_query($query);
 for($i=1;$i<=3;$i++);
 {
     mysql_data_seek(0); // reset datapointer

     // output querydata
 }

I'm obliged however to point out that the mysql-extension is deprecated by now and you should use mysqli or pdo for new projects and code.

Answer 2

Cache the query result in an array, then generate your markup:

$query = "Select * from tblWriters order by surname";
$result = mysql_query($query);
$data = array();
while ($row = mysql_fetch_array($result))
{
    $data[] = $row;
}

for ($i = 1; $i <= 3; $i++) {

    echo "<tr><td>Writer".$i." *</td><td>";
    echo "<select name='txtWriter".$i."' style='width: 200px;'>";
    echo "<option value ='' selected='selected'></option>";
    foreach ($data as $row) {
       echo "<option value ='" . $row['id'] . "'> " . $row['surname'] . 
            ", " .   $row['name'] . "</option>";        
    }

    echo "</select><td></tr>";
}