How to Create and Use Stored Procedure in Laravel?

About Us:

We are from free coder. We are a group of web developers who are passionate about web development. You can learn from us php, laravel, node js, vue js, react js and many other topic on web developement.
we are very happy to help you.
free coder

Today what you are going to learn:

Hi Brother,

Hello Brother, i will let you know lesson 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 lesson 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 lesson 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 and laravel 8 version.

in this lesson we will create stored procedure call "get_posts_by_userid" that will return posts of given user id. so let's see bellow simple lesson:

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);

});

Result:-

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..

Recent Posts

Related Posts