Store Procedures with CakePHP

I have always wondered how to store procedures with CakePHP, as I knew it will reduce the DBhits and will definitely make the application faster and usable.

I have searched on google if any class was available. Everyone was talking about $this->query(“call yourpeocedure()”); and they are getting results as desired, hence, it is fine and we can get the desired result.

But in case where stored procedure will have multiple select query, $this->query() will give an error. Here is a very small class for CakePHP by which we can integrate our MySQL stored procedure with CakePHP.

For now class is very abstract and if needed then we can modify the class. This class is handling the stored procedure in two forms:

1) If our MySQL stored procedure have any select query just like Select * from our table then it will simply return result after calling the function like $this->Model->procedure(“YourProcuedure” , $input_parameter).

2) If our stored procedure is dealing with output procedure then it will just return by calling another function name

$this->Model->getOutData().

Example:

$this->Model->Procedure(“YourProcedure” , $input_parameter , $outputparameter);

$data = $this->Model->GetOutData();

Class for the CakePHP call MySQL stored procedure

For more detail please go through the step-by-step implementation given below:

 Paste the following code in AppModel.php

<br /> <strong>&lt;?php</strong>

class AppModel extends Model

{

var $outputParams = array();

function Procedure($name , $inputParameter = array(), $outputParameter = array() )

{

$this->outputParams = $outputParameter;

if (class_exists(‘DATABASE_CONFIG’)) {

$this->config =& new DATABASE_CONFIG();

}

if($this->config->default[‘driver’] == “mysql”)

{

trigger_error(“OOps error occure, Please go in your database.php of your config folder and set driver name = <b>mysqli</b> to execute stored procuedure. Currently it is set to mysql”);

exit;

}

//Create parameter

$parameter = “”;

foreach($inputParameter as $params)

{

$parameter .= $parameter == “” ? ” ‘$params’ ” : ” , ‘$params’ “;

}

if(count($outputParameter)> 0)

{

foreach($outputParameter as $prm)

{

$parameter .= $parameter == “” ? ” @$prm ” : ” , @$prm “;

}

}

$procuedure = ” call `$name`($parameter) “;

$db =& ConnectionManager::getDataSource($this->useDbConfig);

$db->connection;

mysqli_multi_query($db->connection,$procuedure) or die(mysqli_error($db->connection));

$final_data = array();

do {

/* store first result set */

$mid_data = array();

if ($result = mysqli_store_result($db->connection)) {

while ($row = mysqli_fetch_array($result)) {

$mid_data[] =  $row;

}

mysqli_free_result($result);

}

$final_data[] = $mid_data;

/* print divider */

} while (mysqli_next_result($db->connection));

return $final_data;

}

function getOutData()

{

$outputParameter = $this->outputParams;

if(count($outputParameter)> 0)

{

$parameter = “”;

foreach($outputParameter as $prm)

{

$parameter .= $parameter == “” ? ” @$prm  ” : ” , @$prm  “;

}

$SQL = ” SELECT $parameter “;

$data = $this->query($SQL);

return $data;

}

else

{

trigger_error(“OOPS!!! no resource for select query here”);

}

}

function paginateCount($conditions = null, $recursive = 0, $extra = array()) {

$parameters = compact(‘conditions’);

$this->recursive = $recursive;

$count = $this->find(‘count’, array_merge($parameters, $extra));

if (isset($extra[‘group’])) {

$count = $this->getAffectedRows();

}

return $count;

}

}

Now make sure that in every model you have extended(inherited AppModel class, which is very basic if not the inherited then before using these function please do it)

Now in your model or controller you can use use procedure function to execute the execute stored procedure. Following is the way to execute the stored procedure from CakePHP.

If you are using from controller then you can use following way:

<br />

<strong>&lt;?php</strong><br />

<strong>

$input_prameter = array(“username” , “search_keyword” , 5 , 3);

//Define input parameter into array in the same order in which your procedure require input

</strong><br />

<strong>

$output&nbsp; = array(“@search_result” , “@location”);

//output parameter of the stored procedure in the same way as define in your mysql procedure

</strong><br />

<strong>

$sel_data=$this-&gt;Model-&gt;Procedure(“GetUserDetailProcedure”, $input_parameter&nbsp; , $output);

</strong><br />

<strong>

print_r($sel_data);

//it will return data if there will be any select query used in stored procedure

</strong><br />

<strong>

$data_of_output_parameter = $this-&gt;Model-&gt;getOutData();

// to get data of output parameter, if you do not have any output parameter in your mysql stored procedure then no need to call this function.

</strong>

Source: Codewebber