Why does iterating over the same recordset with `mysql_fetch_assoc()` fail when using it more than once?

<?php
session_start();

include '../dbconnect_form_fields.php';

$res = mysq开发者_开发技巧l_query("SELECT * FROM form_fields") or die(mysql_error());
echo "<form id='list' action='form_calc.php' method='post'>
<table width='100%' border='1'>
<tr>";
for($i=0;$i<2;$i++) {
    echo "<td><select>";

    while($row = mysql_fetch_assoc($res)){ 
        echo "<option value='".$row['id']."'>".$row['field']." ".$row['price']."</option>";
    }
    echo "</select>
</td>";
}
?>

Why does this code produce one correct select box, and one empty select box?

I want it to correctly display two identical select boxes, yet it doesn't work unless I create 2 different variables each accessing the database for the same information.

---

Because mysql_fetch_assoc() is internally incrementing its pointer in the results until it gets to the end and returns FALSE. You'd need to reset it to start iterating through it again.

The easy answer would be to build a string and echo it twice.

The best advice I could give you is to study PDO and use it.

---

You are running over all database results with the While loop. That's why the second iteration of the for loop will have no database results available anymore.

You can work around that by either storing the data in an array first, or with this ugly kludge:

 mysql_data_seek($res, 0);
 while ($row = mysql_fetch_assoc($res)) {

That resets the pointer (what @alex said), so you can receive the same results twice. (Might be ok here, because the mysql interface likely caches the data anyway.)

---

Blockquote mysql_fetch_assoc() Returns an associative array that corresponds to the fetched row and moves the internal data pointer ahead.

More info on mysql_fetch_assoc() can be found here

So construct a string as it uses up all the input:

$str = "";    
while($row = mysql_fetch_assoc($res)){ 
        $str .= "<option value='".$row['id']."'>".$row['field']." ".$row['price']."</option>";
}

echo $str;
echo $str;  

The:

 .= 

bit is short hand for add to the end of the variable. Another way of writing it would be:

$str = $str . "something";

This all could be made much more elegant, but as you're learning I will let you work that out in good time :)

---

Try this:

<?php
session_start();

include '../dbconnect_form_fields.php';

$res = mysql_query("SELECT * FROM form_fields") or die(mysql_error());
$options = array();
while($row = mysql_fetch_assoc($res)){ 
            $options[] = "<option value='".$row['id']."'>".$row['field']." ".$row['price']."</option>";
}
echo "<form id='list' action='form_calc.php' method='post'>
<table width='100%' border='1'>
<tr>";
for($i=0;$i<2;$i++) {
    echo "<td><select>";
    echo $options;
    }
    echo "</select>
</td>";
}
?>