CodeIgniter multiple results from stored procedure

Question

I have one stored procedure on MySQL, for example:

CREATE PROCEDURE get_multiple_results()
BEGIN
  SELECT 'A' AS A;
  SELECT 'B' AS B;
  SELECT 'C' AS C;
END

So, How I get the data using the query method of CodeIgniter?

$this->db->query('CALL get_multiple_results()')->result_array();

Thanks!

Answer 1

just call the method written below and get the array (list) of your query results e.g. $resultSet = $this->GetMultipleQuery(" CALL my_proc ('$input')");

Query string can also be concatenation of multiple select queries.

     /**
     * To get result(s) of queries that returns multiple result sets...
     *
     * @author Pankaj Garg <[email protected]>
     *
     * @param string $queryString
     *
     * @return bool|array List of result arrays
     */
public function GetMultipleQueryResult($queryString)
{
    if (empty($queryString)) {
                return false;
            }

    $index     = 0;
    $ResultSet = array();

    /* execute multi query */
    if (mysqli_multi_query($this->db->conn_id, $queryString)) {
        do {
            if (false != $result = mysqli_store_result($this->db->conn_id)) {
                $rowID = 0;
                while ($row = $result->fetch_assoc()) {
                    $ResultSet[$index][$rowID] = $row;
                    $rowID++;
                }
            }
            $index++;
        } while (mysqli_next_result($this->db->conn_id));
    }

    return $ResultSet;
}

Answer 2

I hope you are using sqlsrv driver.

In that case include library provided below.

$this->load->library('sqldb');

and run the query

$this->sqldb->query($querystring);

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed'); 


/**
 * CodeIgniter sqldb Class
 *
 * This library will help get multiple resultsets from SQL stored procedure

 * @author       Saamit Raut
 */

class Sqldb {

    function __construct() {
        //$CI->load->database();
        //$this->load->library('database');

        $this->CI =& get_instance();
        $this->CI->load->database();
    }

    public function query($querystring){
        $query=sqlsrv_query($this->CI->db->conn_id,$querystring);//exit;

        $resultsets=array();

        do{
                $array=array();
                while( $row = sqlsrv_fetch_array( $query, SQLSRV_FETCH_ASSOC )) {
                    $array[]=$row;
                }
                $resultsets[]=$array;

        }while(sqlsrv_next_result($query));

        return $resultsets;
    }   
}