Good Evening:

I am making a transition from ColdFusion to PHP, and I am curious about a code situation I encountered. I am playing around with adding two drop-downs with two different fields from the same query. Wish CFML, it is

<cfquery name="queryName">
SELECT * FROM food
</cfquery>
Please Select Food: 
<select>
    <cfoutput query="queryName">
        <option>#name#</option>
    </cfoutput>
</select>
<br />
Or Select By Id
<select>
    <cfoutput query="queryName">
        <option>#id#</option>
    </cfouput>
</select>

But with PHP, I was only able to get it working with the following code:

$mysqli = new mysqli('localhost','root','','test');
$sql = "SELECT * FROM food";
if($result = $mysqli->query($sql)){
	echo "Please Select a food: <select>";
	while ($row = $result->fetch_object()) {
		echo "<option>$row->name</option>";
	}
}
echo "</select><br />";
$result->close();
if($result = $mysqli->query($sql)) {
	echo "Or select a fruit (id)<select>";
	while ($row = $result->fetch_object()) {
		echo "<option>$row->id</option>";
	}
	$result->close();
	echo "</select>";
}

echo $mysqli->error;
$mysqli->close();
?>

Maybe it's my ignorance, but, when ColdFusion makes the call, by syntax, I assumed it made the query once, and then output the result set as specified. However, with PHP it seems like each time I need to get a certain field, I have to repeat the query. With the possibility of many users, this would cause unnecessary calls to the database if the query fields were able to be outputted similar to ColdFusion. Is there something I'm missing to be able to make one query call and output it multiple times (field-dependent) in PHP?

For this you just need to store the data in another array.

Ex.

$mysqli = new mysqli('localhost','root','','test');
$sql = "SELECT * FROM food";
$results = array();
if ( $result = $mysqli->query($sql) ) {
  while( $row = $result->fetch_object() ) {
    $results[] = $row;
  }
}
$result->close();
	echo "Please Select a food: <select>";
        foreach( $results as $row ) {
		echo "<option>$row->name</option>";
	}
}
echo "</select><br />";
	echo "Or select a fruit (id)<select>";
	foreach( $results as $row ) {
		echo "<option>$row->id</option>";
	}
	echo "</select>";
}

echo $mysqli->error;
$mysqli->close();
?>