MySQLI nested Prepared Statements

Codes here !

Moderators: macek, egami, gesf

User avatar
Nullsig
php-forum Fan User
php-forum Fan User
Posts: 981
Joined: Thu Feb 17, 2011 6:52 am
Location: Racine, WI

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();


Return to “mySQL & php coding”

Who is online

Users browsing this forum: Bing [Bot] and 2 guests