Recursive method using prepared statement

Hey guys,

I’m using a Database class I found over the web to handle all my db related calls (via http://forum.swiftirc.net/viewtopic.php?f=44&t=14482&p=130087&hilit=database#p130087).

Here is the code (trimmed to only include the necessary methods):

class Database { 

   protected $stmt;
    const user = "username",pass = "password",database  = "database",salt = "AESencryptionKey";
    public $resourceLink,$result,$stmtResult;
   
   public function  __construct() { 
      $this->resourceLink =  mysqli_connect(localhost,self::user,self::pass,self::database) or die( "Unable to connect to database.");
   }
   
   public  function __destruct() { 
      mysqli_close($this->resourceLink);
    }

   public function close() { 
      if ($this->stmt)  mysqli_stmt_close($this->stmt); 
   }

   protected function bind(array $args)
   {
       $query = array_shift($args);
      $this->stmt =  mysqli_stmt_init($this->resourceLink);
      if  (mysqli_stmt_prepare($this->stmt,$query))
      {
         if  ($args[0])
         {
            $bindrow[0] = $this->stmt; 
             for ($i=0; $i <= mysqli_stmt_param_count($this->stmt);  $i++) $bindrow[] = &$args[$i]; 
             call_user_func_array("mysqli_stmt_bind_param", $bindrow); 
          }
      }
   }
   public function select()
   {
       $this->bind(func_get_args());
      
       $this->stmtResult = mysqli_stmt_result_metadata($this->stmt);
       if (!$this->stmtResult) return;
      $nof =  mysqli_num_fields($this->stmtResult);
      $fieldMeta =  mysqli_fetch_fields($this->stmtResult);
      $this->result =  array();
      $arg = array($this->stmt);
      for ($i=0; $i  < $nof; $i++) {
         $this->result[$fieldMeta[$i]->name]  = '';
         $arg[$i+1] =  &$this->result[$fieldMeta[$i]->name];
      }
       mysqli_stmt_execute($this->stmt);
       mysqli_stmt_store_result($this->stmt);
      call_user_func_array  ('mysqli_stmt_bind_result',$arg);
      return $this->stmt;
    }

   
}

I’m having trouble executing a simply recursive method call using this class. There are three rows that need to be returned, but only the first two seem to be. Based on my shallow knowledge of prepared statements, I have a feeling it may have something to do with the fact the stored result for each method call being overwritten, as the result is stored in a member object ($this->stmt) shared between all query calls i.e. query 2’s stored result call will overwrite what was stored after query 1 was run.

I tried using a prepared statement without the aid of the class, and things appear to work fine in that instance. Perhaps because each result is stored in a local variable unique to each method call, and so it doesn’t get erased.

I was wondering if anyone has any ideas on how I can modify the class code so to be able to handle a recursive method call? If there’s any other information you need, let me know. Much thanks! :slight_smile:

A prepared statement only has one buffer to hold results. If you need to reuse it, you have to finish using it before calling execute again. You can either read everything into a variable in PHP or you can use a new statement for each call.

The problem does seem to be because $this->stmt is being overwritten by the next query. But since the select method returns the statement object, have you tried:

private function draw_template( $parent_grid_id = '0' ) {
        
        $stmt = $this->registry->cx->select( "SELECT * FROM templates_sections WHERE ID_Template = ? AND ID_Parent_Section = ? ORDER BY Location_In_Parent_Section ASC", "ii" , $this->template , $parent_grid_id );

        while( $row = $stmt->fetch() )
        {
                        
            // do stuff
            
            // recursive method call
            $this->draw_template( $id );
                  
        }
                
    }

Sorry if that’s a bit off, I’ve never actually used the mysqli functions before…

Nobody? :slight_smile:

Just for clarity, here is the code that does NOT work:

    private function draw_template( $parent_grid_id = '0' ) {
        
        $this->registry->cx->select( "SELECT * FROM templates_sections WHERE ID_Template = ? AND ID_Parent_Section = ? ORDER BY Location_In_Parent_Section ASC", "ii" , $this->template , $parent_grid_id );

        while( $row = $this->registry->cx->fetch() )
        {
                        
            // do stuff
            
            // recursive method call
            $this->draw_template( $id );
                  
        }
                
    }

But, this does:

    private function draw_template( $parent_grid_id = '0' ) {
        
        $query = "SELECT ID_Section , Section_Title, Type FROM templates_sections WHERE ID_Template = ? AND ID_Parent_Section = ? ORDER BY Location_In_Parent_Section ASC";
        $stmt = $this->registry->cx->get_current_db()->stmt_init();

        if( $stmt->prepare( $query ) )
        {
            
            $stmt->bind_param( "ii" , $this->template , $parent_grid_id  );
            $stmt->execute();   
            $stmt->store_result();        
            $stmt->bind_result( $id , $title , $type );

            while( $stmt->fetch() )
            {
                       
            
                // do stuff
                
                  // recursive method call
                $this->draw_template( $id );
                    
    
            }
            
        }
        
    }

Thank you both for your advice. Jaanboy, your tip unfortunately didn’t work :frowning: It did seem a bit too simple to be true.

And, so, I did some modification to the database class code, so that the statement and result member fields are arrays. Whenever I make any calls to any of the statement related methods, I have to input the statement index as a method argument (or if I don’t in a non-recursive context it will simply be the first element in the array). This way it knows which statement to query from.