CodeIgniter: Call multiple queries from a MySQL stored procedure

If you are using CodeIgniter and wondering how are you going to execute such queries multiplied in greater than 1 times.

So let say you have this MySQL stored procedure,

DELIMITER //
DROP PROCEDURE IF EXISTS multiples;//
CREATE PROCEDURE multiples( param1 INT, param2 INT )
BEGIN
SELECT * FROM table_a where key=param1;
SELECT * FROM table_b where key=param2;
END;
//


Now, your main goal is to grab the result set that the selection from table_a and table_b will be fetched. MySQL has the ability to fetch multiple queries in a stored procedure (or see http://dev.mysql.com/doc/refman/5.7/en/c-api-multiple-queries.html).

Now in CodeIgniter, I'll just have a simple snippet here,

1. public function getMultipleQuery() {
2.             $k = 0;
3.             $arr_results_sets = array();
4.             /* execute multi query */
5.             if (mysqli_multi_query($this->db->conn_id, 'CALL multiples(1, 1)')) {
6.                 do {
7.                     $result = mysqli_store_result($this->db->conn_id);
8.                     // print_r($result);
9.                     if ($result) {
10.                         $l = 0;
11.                         while ($row = $result->fetch_assoc()) {
12.                             $arr_results_sets[$k][$l] = $row;
13.                             $l++;
14.                         }
15.                     }
16.                     $k++;
17.                 } while (mysqli_next_result($this->db->conn_id));
18.             }
19.  
20.             print_r($arr_results_sets);
21.            
22.             exit($this->db->conn_id);
23.  
24. }

Where just pass the db->conn_id from CodeIgniter's DB class then calling multiples with params 1 and 1.

This will print the results taht are stored in $arr_results_sets. In this example, you can just use an if/ese statement to check whether such column exist in arr_results_sets if you want to handle the result like for example check if such login name exist, and if not, return to login url; or you can proceed to another query again if login name exist and check the next query in sequence and get the result set for rendering to the view.