Laravel 10 Call MySQL Stored Procedure Example Tutorial

Stored procedures are strong database components that allow you to encapsulate and execute sophisticated database operations. Laravel, a powerful PHP framework, provides handy methods for executing MySQL stored procedures from within your application.

In this article, we will walk you through the process of calling MySQL stored procedures in Laravel 10, allowing you to fully utilise the power of database-driven processes.

By the end of this section, you’ll have the skills and knowledge to improve your Laravel 10 projects by effectively calling MySQL stored procedures.

Read More: Laravel 10 FullCalendar Ajax CRUD Tutorial Example

Let’s get started.

Laravel Installation

Open terminal and run this command to create a laravel project.

composer create-project laravel/laravel myblog

It will create a project folder with name myblog inside your local system.

To start the development server of laravel –

php artisan serve

URL: http://127.0.0.1:8000

Assuming laravel already installed inside your system.

Create Database & Connect

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

CREATE DATABASE laravel_app;

To connect database with application, Open .env file from application root. Search for DB_ and update your details.

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=laravel_app
DB_USERNAME=root
DB_PASSWORD=root

How To Create MySQL Stored Procedure in MySQL?

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 single 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 More: How To Work with Javascript Cookie For GET SET and REMOVE

Read a Single Blog from Table

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

How To Call Stored Procedure in Laravel?

Open project into terminal and run this artisan command,

php artisan make:controller DataController

It will create DataController.php file inside /app/Http/Controllers folder.

Open file and write this following code,

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class DataController extends Controller
{
    public function listBlogs()
    {
        $blogs = DB::select("CALL getBlogs()");

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

    public function singleBlog($id)
    {
        $single_blog = DB::select("CALL getSingleBlogDetail(" . $id . ")");

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

• Using CALL getBlogs() to call, list all blogs

• Using CALL getSingleBlogDetail(” . $id . “) to get specific blog detail.

Create Routes

Open web.php from /routes folder. Add these routes into file,

//...

use App\Http\Controllers\DataController;

Route::get("list-blog", [DataController::class, "listBlogs"]);
Route::get("single-blog/{id}", [DataController::class, "singleBlog"]);

//...

Application Testing

Run this command into project terminal to start development server,

php artisan serve

URL (List All Blogs): http://127.0.0.1:8000/list-blog

Read More: PHP Exception How To Create Your Own Exception Class

URL (Single Blog): http://127.0.0.1:8000/single-blog/3

That’s it.

We hope this article helped you to learn about Laravel 10 Call MySQL Stored Procedure Example 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.