Jason Gerfen wrote:
> Kevin Castiglia wrote:
>> 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');
>
> Google... http://dev.mysql.com/doc/refman/5.0/en/commands-out-of-sync.html
>
>
>> 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)?
>>
I am unaccustomed to utilizing the mysqli functionality but the older
mysql functions allow you to do something within your loops without the
need to close then prepare a new connection/query each time.
mysql_pconnect()
mysql_select_db()
for( $x > $y ) {
mysql_query()
mysql_fetch_array()
mysql_free_result()
}
mysql_close()
These functions may be more appropriate for your datasets
>> Thanks,
>> Kevin
>>
>
>
--
Jas
"Tomorrow isn't promised so we live for today"
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
