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