Cakephp Store procedure + multiple resultset fetch

Hi All,


I have been working on a project which involves huge data. So the idea is to use stored procedures to make the query execution fast. Most tricky part in this, is to fetch multiple-result set from a single store procedure. 


Step 1: Write the procedure which have the multiple select queries.
Step 2 : Make a component within your cake's controller folder(app/controllers/components) and paste the code given below:

function callstoreproc($procname,$paramarr=null)
{
$connstr = ConnectionManager::getInstance();

$conhost = $connstr->config->default["host"];
$conlogin = $connstr->config->default["login"];
$conpassword = $connstr->config->default["password"];
$condatabase = $connstr->config->default["database"];

$mysqli = new mysqli($conhost, $conlogin, $conpassword, $condatabase);

if (mysqli_connect_errno())
{
echo "Connect failed";exit();
}
 $query = $procname;
 if (mysqli_multi_query($mysqli, $query))
{
$i=0;
while (mysqli_more_results($mysqli))
{
if ($result = mysqli_store_result($mysqli))
{


$j=0;
if(mysqli_num_rows($result)>0)
{
while ($row = mysqli_fetch_assoc($result))
{
$arr[$i][$j] = $row;
$j++;

}
}
else
{

$arr[$i] = array();
}

mysqli_free_result($result);
}
mysqli_next_result($mysqli);
$i++;
}
}
return $arr;
}

Step 3: Now include the component in your controller and call the component function using:


$data=$this->[component name]->callstoreproc("CALL [procedure ]([parameter])");


Note: make the database driver from mysql to mysqli in the database.php in config folder.


Best,