• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Handling Stored Procedures that return multiple record sets

#5
(06-01-2017, 10:11 AM)brocknessmonster Wrote: Our database engineer is fond of creating stored procedures that return multiple record sets/results. I have had success in the past with hacking the CI 2 system files to accommodate multiple recordsets but now we are using CI 3.1.X and things have changed a little bit.

There are some known hacks that are used to add this functionality to CI 2.  Can these be added to CI 3?  We do a lot of data abstraction and visualization in our applications.

Thanks.


Try this:


PHP Code:
public function test()
{
 
   $sql 'CALL your_procedure()';
 
   $results $this->multiple_result_array$sql );

 
   echo '<pre>';
 
   var_dump($results);
 
   echo '</pre>';

 
   $sql 'CALL your_procedure()';
 
   $results $this->multiple_result$sql );

 
   echo '<pre>';
 
   var_dump($results);
 
   echo '</pre>';
}

/**
 * This could go in MY_Model:
 */
public function multiple_result_array$sql )
{
 
   if( empty( $sql ) )
 
       return NULL;

 
   $i 0;
 
   $set = [];

 
   ifmysqli_multi_query$this->db->conn_id$sql ) )
 
   {
 
       do 
        
{
 
           mysqli_next_result$this->db->conn_id );
 
           
            if
FALSE != $result mysqli_store_result$this->db->conn_id ) )
 
           {
 
               $row_id 0;

 
               while$row $result->fetch_assoc() )
 
               {
 
                   $set[$i][$row_id] = $row;
 
                   $row_id++;
 
               }
 
           }

 
           $i++;
 
       
 
       while mysqli_more_results$this->db->conn_id ) );
 
   }

 
   return $set;
}

/**
 * This could go in MY_Model:
 */
public function multiple_result$sql )
{
 
   if( empty( $sql ) )
 
       return NULL;

 
   $i 0;
 
   $set = [];

 
   ifmysqli_multi_query$this->db->conn_id$sql ) )
 
   {
 
       do 
        
{
 
           mysqli_next_result$this->db->conn_id );
 
           
            if
FALSE != $result mysqli_store_result$this->db->conn_id ) )
 
           {
 
               $row_id 0;

 
               while$row $result->fetch_object() )
 
               {
 
                   $set[$i][$row_id] = $row;
 
                   $row_id++;
 
               }
 
           }

 
           $i++;
 
       
 
       while mysqli_more_results$this->db->conn_id ) );
 
   }

 
   return $set;

Reply


Messages In This Thread
RE: Handling Stored Procedures that return multiple record sets - by skunkbad - 06-05-2017, 05:16 PM

Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2019 MyBB Group.