CodeIgniter multiple results from stored procedure

Question

I have one stored procedure in MySQL, for example:

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

So how do I retrieve data using the CodeIgniter request method?

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

Thanks!

Answer 1

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

A query string can also be a concatenation of several select queries.

  /** * To get result(s) of queries that returns multiple result sets... * * @author Pankaj Garg < [e-mail] > * * @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

UPDATE

Solved my problem according to PHP manual http://php.net/manual/en/mysqli.multi-query.php

And, we changed the following files:

• system / database / driver / DB_result.PHP

Add the multi_results function to the end of the file.

 public function multi_results() { return array(); } 

• system / database / driver / DB_driver.php

Add the multi_query () function after the simple_query () function.

 function multi_query($sql, $binds = FALSE) { if ( ! $this->conn_id) { $this->initialize(); } $sql = $this->compile_binds($sql, $binds); return $this->_execute_multi_query($sql); } 

• System / Databse / Drivers / mysqli_driver.php

Add the _execute_multi_query () function after the _execute () function.

 /** * Execute multi query * * @access private called by the base class * @param string an SQL query * @return resource */ function _execute_multi_query($sql) { $sql = $this->_prep_query($sql); $result_sets = array(); $k = 0; mysqli_multi_query($this->conn_id, $sql); do { $result = mysqli_store_result($this->conn_id); if($result) { $l = 0; while($row = mysqli_fetch_assoc($result)) { $result_sets[$k][$l] = $row; $l++; } $k++; mysqli_free_result($result); } } while(mysqli_next_result($this->conn_id)); return $result_sets; } 

If I miss something or something is wrong, please correct me.

Thanks!

Answer 3

Hope you are using sqlsrv driver.

In this case, include the library 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; } }