> 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)?
You do not initialise your statement object. I would guess this is a
large part of the problem, although I'm not that familiar with prepared
statements in MySQLi.
See the documentation for mysqli::stmt_init[1] and the example for
mysqli_stmt::prepare[2] in the php documentation.
[1] http://docs.php.net/manual/en/mysqli.stmt-init.php
[2] http://docs.php.net/manual/en/mysqli-stmt.prepare.php
> Thanks,
> Kevin
--
Niel Archer
niel.archer (at) blueyonder.co.uk
|