Reading Time: 5 minutes

2,273 Views

A procedure (often called a stored procedure) is a collection of pre-compiled SQL statements stored inside the database. It is a subroutine or a subprogram in the regular computing language.

Inside this article we will see How to run/call MySQL stored procedure in cakephp 4. This article will be very interesting to know and learn. We will see the complete guide of mysql stored procedure creation and calling from a cakephp 4 application.

How to use a mysql stored procedure in cakephp 4, all things will be cleared in this article.

Learn More –

Let’s get started.

CakePHP 4 Installation

To create a CakePHP project, run this command into your shell or terminal. Make sure composer should be installed in your system.

$ composer create-project --prefer-dist cakephp/app:~4.0 mycakephp

Above command will creates a project with the name called mycakephp.

Create Database

To create a database, either we can create via Manual tool of PhpMyadmin or by means of a mysql command.

CREATE DATABASE mydatabase;

Successfully, we have created a database.

Database Connection

Open app_local.php file from /config folder. Search for Datasources. Go to default array of it.

You can add your connection details here to connect with your database. It will be like this –

//...

'Datasources' => [
        'default' => [
            'host' => 'localhost',
            /*
             * CakePHP will use the default DB port based on the driver selected
             * MySQL on MAMP uses port 8889, MAMP users will want to uncomment
             * the following line and set the port accordingly
             */
            //'port' => 'non_standard_port_number',

            'username' => 'root',
            'password' => 'sample@123',

            'database' => 'mydatabase',
            /*
             * If not using the default 'public' schema with the PostgreSQL driver
             * set it here.
             */
            //'schema' => 'myapp',

            /*
             * You can use a DSN string to set the entire configuration
             */
            'url' => env('DATABASE_URL', null),
        ],
  
     //...

//...

You can pass host, username, password and database.

Successfully, you are now connected with the database.

Create MySQL Stored Procedure

Open MySQL database.

Let’s say we have a table in database called as blogs. In this table we have 100 rows. By the help of procedure, we will read all blogs as well as any specific blog.

We will create stored procedure by using phpmyadmin manual interface option to add routines.

We will create two MySQL stored procedure here for this article.

Read all blogs from table
Read a Single Blog from Table

Read all blogs from table

Read a Single Blog from Table

Successfully, we have created two mysql stored procedures in database.

Create Routes

Open routes.php from /config folder. Inside this file we will add few routes which calls methods of controller.

Add this code into your routes.php file,

//...

$routes->connect(
    '/blogs',
    ['controller' => 'Site', 'action' => 'readAllBlogs']
);

$routes->connect(
    '/blog/{id}',
    ['controller' => 'Site', 'action' => 'singleBlog']
)->setPass(["id"]);

//...

Create Controller

Open project into terminal and run this command to create controller.

$ bin/cake bake controller Site --no-actions

It will create a file SiteController.php inside /src/Controller folder.

Open SiteController.php and write this complete code into it.

<?php

declare(strict_types=1);

namespace App\Controller;

use Cake\Datasource\ConnectionManager;

class SiteController extends AppController
{
    private $db;
    public function initialize(): void
    {
        parent::initialize();

        $this->db = ConnectionManager::get("default");
        $this->autoRender = false;
    }

    // Read all blogs
    public function readAllBlogs()
    {
        $results = $this->db
            ->execute("CALL getBlogs()")
            ->fetchAll('assoc');

        echo "<pre>";
        print_r($results);
    }

    // Get single blog
    public function singleBlog()
    {
        $blog_id = $this->request->getParam("id");

        $results = $this->db
            ->execute("CALL getSingleBlogDetail(" . $blog_id . ")")
            ->fetch('assoc');

        echo "<pre>";
        print_r($results);
    }
}

Using CALL getBlogs() to call, list all blogs
Using CALL getSingleBlogDetail(“. $blog_id .”) to get specific blog detail.

Application Testing

Back to terminal and run this command to start development server.

$ bin/cake server

URL To List All Blogs – http://localhost:8765/blogs

URL To Single Blog – http://localhost:8765/blog/2

We hope this article helped you to learn about Call MySQL Stored Procedure in CakePHP 4 Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.