Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

MySQLI nested Prepared Statements

Codes here !

Moderators: macek, egami, gesf

MySQLI nested Prepared Statements

Postby Nullsig » Tue Jul 17, 2012 7:58 am

Is there a way to nest prepared statements without pulling the entire result set from the higher order queries into memory on the PHP server?

Basically this is what I am doing:
Code: Select all
$stateStmt->bind_param('i',$repID);
if($stateStmt->execute()){
   $stateStmt->bind_result($currentState);
   while($stateStmt->fetch()){
      $stateUsed = false; //flag since we only echo out the state once
      
      $arg3 = $currentState . "%";
      
      //pull a list of cities for the current state
      $cityStmt->bind_param('is',$repID,$arg3);
      if($cityStmt->execute()){
         $cityStmt->bind_result($igcode,$currentCity);
         while($cityStmt->fetch()){
            $cityUsed = false; //flag since we only echo out the city once.


The $stateStmt query and loop execute correctly. As soon as I go to execute the second statement "$cityStmt->execute()" I get a "Commands out of Sync" error.
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

Re: MySQLI nested Prepared Statements

Postby Nullsig » Tue Jul 17, 2012 8:06 am

Answered my own question.

After the each prepared statement execution before the bind result (preferably) you need to use $stmt->store_result();
User avatar
Nullsig
php-forum Fan User
php-forum Fan User
 
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.