How to Create and Use Stored Procedure in Laravel?
Hi Dev,
Today, i will let you know example of how to create and use stored procedures in laravel. if you have question about laravel migration create stored procedure then i will give simple example with solution. you will learn how to call mysql stored procedure in laravel. you can understand a concept of how to write stored procedure in laravel.
In this example i will create mysql stored procedure using laravel migration and we will call stored procedure using laravel eloquent. you can easily use it with laravel 6, laravel 7, laravel 8, laravel 9 and laravel 10 version.
in this example we will create stored procedure call "get_posts_by_userid" that will return posts of given user id. so let's see bellow simple example:
Create Stored Procedure using Migration:
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
class CreatePostProcedure extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
$procedure = "DROP PROCEDURE IF EXISTS `get_posts_by_userid`;
CREATE PROCEDURE `get_posts_by_userid` (IN idx int)
BEGIN
SELECT * FROM posts WHERE user_id = idx;
END;";
\DB::unprepared($procedure);
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
}
}
Call Stored Procedure:
Route::get('call-procedure', function () {
$postId = 1;
$getPost = DB::select(
'CALL get_posts_by_userid('.$postId.')'
);
dd($getPost);
});
Output:
Array
(
[0] => stdClass Object
(
[id] => 5
[title] => asasas
[body] => asasasa
[created_at] => 2021-05-01 06:00:03
[updated_at] => 2021-05-01 06:00:03
[user_id] => 1
)
[1] => stdClass Object
(
[id] => 6
[title] => sdfsdf
[body] => sdfsdfsf
[created_at] => 2021-05-01 06:14:05
[updated_at] => 2021-05-01 06:14:05
[user_id] => 1
)
)
i hope it can help you..
Hardik Savani
I'm a full-stack developer, entrepreneur and owner of ItSolutionstuff.com. I live in India and I love to write tutorials and tips that can help to other artisan. I am a big fan of PHP, Laravel, Angular, Vue, Node, Javascript, JQuery, Codeigniter and Bootstrap from the early stage. I believe in Hardworking and Consistency.
We are Recommending you
- Laravel Migration Enum Default Value Example
- Laravel Migration Add Enum Column Example
- How to Rollback Migration in Laravel?
- Laravel Migration Add Comment to Column Example
- How to add Default Value of Column in Laravel Migration?
- Laravel Migration Custom Index Name Example
- Laravel Migration Custom Foreign Key Name Example
- How to Add Index in Laravel Migration?
- How to Add Foreign Key in Laravel Migration?
- Laravel Migration - How to Add New Column in Existing Table ?
- Mysql procedure with pagination in laravel?