Calling MySQL stored procedure and Displaying its data in CAKEPHP

Question

I want to call my stored procedure which accept only one integer input parameter and return multiple rows in cakephp framework.

Stored Procedure:

PROCEDURE `Salary`(IN sysid int )
BEGIN
SELECT name, lastName, Salary, Month From SalaryData 
where Userid = sysid;
END

Model.PHP:

<?php
 class SalaryModel extends AppModel
 {
   public function sProcedure($testId) 
{
    $result=$this->query("CALL Salary($testId);");
    return $result;
}
 }?>

My Controller code is:

public function Procdata($testId) {
    $result=$this->Systemstate->sProcedure($testId);
 }

Now, Can anyone suggests me how to display the content of query in "view.ctp" ??

I hope my model is correct.

Thanks in advance

Why are you trying to use a stored procedure? Why not just generate a query using the framework? — Colonel Mustard, Aug 02 '16 at 14:51
because its a complex query, including max functions etc and after everything it results is returned as a select query from stored procedure — Noman uddin, Aug 02 '16 at 14:56
@Nomanuddin please only tag questions with the relevant version of CakePHP you are using. Are you using CakePHP 1 or 2? — drmonkeyninja, Aug 02 '16 at 15:51

score 1 · Answer 1 · edited May 23 '17 at 10:33

1

MySQL stored procedure does not usually return some result. You need to develop FUNCTION instead to return some value. Read more here.

edited May 23 '17 at 10:33

Community

1
1

answered Aug 02 '16 at 14:38

Bogdan Burym

5,482
2
27
46

okay If I use a function, any suggestion how to call and display content in cakephp? – Noman uddin Aug 02 '16 at 14:43

score 0 · Accepted Answer · answered Aug 02 '16 at 16:33

There are various methods to call stored procedures in cakephp. but the best way is to execute it as a query instead of baking it as a model, because up to my search there is no way to build or bake complex stored procedures.

So, this way I solved my issue:

My Controller code is:

    public function Procdata($testId) {
    $result=$this->Systemstate->sProcedure($testId);
    $this->set('data',$result);
 }

My Display Section View.CTP

   print_r($data);

Calling MySQL stored procedure and Displaying its data in CAKEPHP

2 Answers2