How to call stored procedure in Cakephp3

Stored procedure is a set of SQL statements with an assigned name, which are stored in a relational database management system as a group. It reduces multiple calls to database as these are procedural logic and allows faster execution.

Why we should use stored procedure ?

Stored procedures can be tested independently in the web application and can reduce network traffic as SQL statements can be executed in batches directly in database rather than sending multiple requests. Stored procedures are cached on the server. Unlike standard SQL statements, stored procedures are compiled and optimized by the database server.

A) Steps of using stored procedure in cakePHP 3 :

Cakephp has predefined execute() function. By using this function we can call stored procedure in cakephp.

Step-1:
Include connection namespace in controller above class name, it helps us to connect with database.

Example:
namespace App\Controller;
use Cake\Datasource\ConnectionManager;

Step-2:
Write connection manager inside function where you want to execute that stored procedure.

Example:
$conn = ConnectionManager::get('default');

Step-3:
For executing the stored procedure you’ve to write a execute() function and write a stored procedure name with call word and pass a parameter inside procedure_name(parameter).

3.1) For Integer type parameter:

Example:
// with single parameter using fetchAll('assoc')
$id = 2;
$result = $conn->execute("call procedure_name($id)")->fetchAll('assoc');

// with multi parameter using fetchAll('assoc')
$id = 2;
$roll = 4;
$result = $conn->execute("call procedure_name($id,$roll)")->fetchAll('assoc');

3.2) For String type parameter:
If you have string type of parameter than you will have to write it in quotes(”).

Example:

// with single parameter
$name = 'admin';
$result = $conn->execute("call procedure_name('$name')")->fetchAll('assoc');

// with multi parameter
$id = 2;
$name = 'admin';
$result = $conn->execute("call procedure_name($id,'$name')")->fetchAll('assoc');

3.2.1) $result variable return a data according to the query which is written in stored procedure in MySql.

B) Where and When we use fetchAll(‘assoc’) function ?

1) FetchAll(‘assoc’) means to get/fetch all data according to the parameter(s) which we have passed inside procedure_name().

2) We use fetchAll(‘assoc’) function at that time when the query return some data.

3) If we write SELECT query in stored procedure than we will use fetchAll(‘assoc’) function to get data but If we write CREATE, INSERT, UPDATE, DELETE query in stored procedure than there is no need to write fetchAll(‘assoc’) function.

Example: 

// only passing one parameter without using fetchAll('assoc')
1) $result = $conn->execute("call procedure_name($id)");

// passing two parameter without using fetchAll('assoc')
2) $result = $conn->execute("call procedure_name($id,'$name')");

C) How to write Stored Procedure in MySql ?

Example:

CREATE PROCEDURE procedure_name
(
IN UID int,
IN UNAME varchar(255)
)
BEGIN
SELECT * FROM abc WHERE id = UID AND name = UNAME;
END

Hope, now you have learned how stored procedures can be implemented in Cakephp 3.0 and how it helps to execute and make application faster.
If you still have any query about this or need help in implementing any database related integrations, feel free to contact us.
Check our another blog where you will learn about how to implement MYSQL transactions in PHP .