Whenever I run the following code, I get the error: "Commands out of sync;
you can't run this command now" as I try to execute my prepared Update
statement.
<?php
$fpiDataAddr = fopen('outputAddr.txt','r') or die("can not open In File ");
//Connect to mySQL server
$mysqli = new mysqli('localhost', 'user', 'pswd', 'db');
if ($mysqli->connect_error) { die('Could not connect:
'.$mysqli->connect_error); }
else{ echo "Connected successfully\n"; }
$seqno = 0;
$k = 'Kev';
$sql1 = 'SELECT UNIQUE_NUM, AM_CITY FROM db.kb_addr WHERE UNIQUE_NUM = ?';
$sth1 = $mysqli->prepare($sql1);
$sql2 = 'UPDATE db.kb_addr SET AM_CITY = ? WHERE UNIQUE_NUM = ?';
$sth2 = $mysqli->prepare($sql2);
while($inrec = fgetcsv($fpiDataAddr,0,',','"')){
if($seqno == 0){
$x= count($inrec);
$arrFields = array();
for ($y = 0; $y < $x; $y++) {
$arrFields[$inrec[$y]] = $y; //creates associative array that
associates fields with the index in $inrec
}
echo "Array of Field Names From Header Record in Input data is \n";
print_r($arrFields);
$seqno++;
continue; }
$key = 0+$inrec[$arrFields['Unique #']];
//Select Statement
$sth1->bind_param('i',$key);
$sth1->execute();
$sth1->bind_result($un,$ac);
$sth1->fetch();
//Update Statement
$sth2->bind_param('si',$k,$key);
echo "after bind: ".$sth2->error."\nThe object error is:
$mysqli->error\n";
$sth2->execute();
echo "after execute: ".$sth2->error."\nThe object error is:
$mysqli->error\n";
if($seqno > 1000) break;
$seqno++;
}
fclose($fpiDataAddr) or die("can not close file");
//disconnect
$sth1->close();
$sth2->close();
$mysqli->close();
?>
However, if I close $sth1 (the select statement) before executing $sth2 (the
update statement), it works, but since I just closed $sth1, I have to
prepare it again. This is pretty inefficient considering the large data set
that I'm working with and the fact that I have to prepare and close my
select statement every single time I loop through. Is there any way that I
can run these statements error-free without having to close the select
statement ($sth1) every single time I want to execute my update statement
($sth2)?
Thanks,
Kevin
