[PHP-DB] 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.
?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
Re: [PHP-DB] 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.
?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)?
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: [PHP-DB] 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.
?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
Re: [PHP-DB] 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.
?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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
