mysqli来自更新查询中使用的选择查询

Question

I am having problems with only being able to have one result set from mysqli open at once. In particular I am trying to loop through a select query and update columns within that query after performing an action.

$db = new mysqli($DBServer, $DBUser, $DBPass , $DBName);
$sql = 'SELECT UPRN, POSTCODE FROM T_TEMP';
$stmt = $db->prepare($sql);
$stmt -> Execute();

<Create an array from the above select statement as i understand that mysqli can 
only hold one result set at once (seems odd). I am unsure how to do this such that
i can then reference UPRN and POSTCODE later>

$stmt->Close();

$sql = 'update T_TEMP set LAT = ?, LONG = ? where UPRN = ?';
$stmt = $db ->prepare($sql);  

<loop through that array built above grabbing UPRN and POSTCODE as you go through>

$postcode = urlencode(<Reference the postcode in the array>);
$request_url = "http://maps.googleapis.com/maps/api/geocode/xml?address=".$postcode."&sensor=false"; 
$xml = simplexml_load_file($request_url);

$lat = round(floatval($xml->result->geometry->location->lat),4);
$long = round(floatval($xml->result->geometry->location->lng),4);

$stmt -> bind_param('ddi',$lat,$long,$UPRN);

$stmt -> Execute();

<end loop>

I am struggling with getting the results of the first query into an array and then referencing that array within the loop so i can set the values. Any help much appreciated!

Answer 1

Don't use mysqli. Use PDO instead. In this latter case this desired code would be of one single line:

include 'db.php';
$sql  = 'SELECT UPRN, POSTCODE FROM T_TEMP';
$stmt = $db->prepare($sql);
$stmt->execute();
$array = $stmt->fetchAll(); // here you are