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;
} 


Expert Website Development - Temecula, CA
Community Auth For CodeIgniter 3