mysqli error

--0016364d1dab51d13d046da6c6f7
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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.

$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

--0016364d1dab51d13d046da6c6f7--

Re: mysqli error

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.
>
> > $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)?
>
> 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

Re: mysqli error

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.
>>
>> >> $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

Re: mysqli error

> 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.
>
> > $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



--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php