Handling Stored Procedures that return multiple record sets

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 = [];

    if( mysqli_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 = [];

    if( mysqli_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;
} 


i try this and it works... my problem now is how to echo/view the records on my view using <?php foreach ($data as $rows): ?>

this is my data...

Code:

[color=#000000]

array(1) {
 [0]=>
 array(4) {
   [0]=>
   object(stdClass)#31 (9) {
     ["student"]=>
     string(2) "17"
     ["section"]=>
     string(8) "Hinam-is"
     ["subject"]=>
     string(21) "Afro Asian Literature"
     ["loadid_id"]=>
     string(2) "12"
     ["grade1"]=>
     string(2) "80"
     ["grade2"]=>
     string(2) "79"
     ["grade3"]=>
     string(1) "0"
     ["grade4"]=>
     string(1) "0"
     ["average"]=>
     string(5) "39.75"
   }
   [1]=>
   object(stdClass)#32 (9) {
     ["student"]=>
     string(2) "17"
     ["section"]=>
     string(8) "Hinam-is"
     ["subject"]=>
     string(18) "Araling Panlipunan"
     ["loadid_id"]=>
     string(1) "9"
     ["grade1"]=>
     string(2) "90"
     ["grade2"]=>
     string(2) "85"
     ["grade3"]=>
     string(2) "75"
     ["grade4"]=>
     string(2) "95"
     ["average"]=>
     string(5) "86.25"
   }
   [2]=>
   object(stdClass)#33 (9) {
     ["student"]=>
     string(2) "17"
     ["section"]=>
     string(8) "Hinam-is"
     ["subject"]=>
     string(40) "Music, Arts, Physical Education, Health "
     ["loadid_id"]=>
     string(2) "11"
     ["grade1"]=>
     string(2) "81"
     ["grade2"]=>
     string(2) "83"
     ["grade3"]=>
     string(2) "89"
     ["grade4"]=>
     string(2) "90"
     ["average"]=>
     string(5) "85.75"
   }
   [3]=>
   object(stdClass)#34 (9) {
     ["student"]=>
     string(2) "17"
     ["section"]=>
     string(8) "Hinam-is"
     ["subject"]=>
     string(21) "Philippine Literature"
     ["loadid_id"]=>
     string(1) "8"
     ["grade1"]=>
     string(2) "88"
     ["grade2"]=>
     string(1) "0"
     ["grade3"]=>
     string(1) "0"
     ["grade4"]=>
     string(1) "0"
     ["average"]=>
     string(2) "22"
   }
 }
}

[/color]

Help....

I try this Please check the below code:

Code:

$test =Array( "0" => Array
(
    "0" => (Object)
        [
            "PRO_PRICE_CODE" => "AAA001001",
            "PRO_MST_NM" => "Promo Air Asia",
            "PRO_AMT_CUST" => "65000.00"
       ],

     "1" => (Object)
        [
            "PRO_PRICE_CODE" => "AAAnghngng001001",
            "PRO_MST_NM" => "Asia Airlines",
            "PRO_AMT_CUST" => "55000.00"
        ]
)

);

foreach ($test as $key => $value) {
    echo $value["0"]->PRO_PRICE_CODE;   
}

it somehow works...nice but do i need to always code..

Code:

<?php foreach ($data as $key => $value): ?>
  <?php echo "Sakto ".$value[0]->loadid_id; ?>
  <?php echo "Sakto ".$value[0]->subject; ?>
  <?php echo "Sakto ".$value[1]->loadid_id; ?>
  <?php echo "Sakto ".$value[1]->subject; ?>
 <?php endforeach; ?>

do you have a code to loop these data?