How to execute the query of procedure with parameter calling in laravel

Question

I am trying to execute the following code in laravel...

$sql = "SET @p0='$p_id'; CALL `rating_avg`(@p0);";
$pp = mysqli_query($link,$sql);
if($pp == false)die("QUERY DIDN'T EXECUTE");

But always showing QUERY DIDN'T EXECUTE

I am doing a web project using Laravel. My project name is Research paper management system. Our course teacher forbid us to use laravel model and told us to execute any query in the following way. $sql = "SOME QUERY"; mysqli($database_connect , $sql);

That's why I am trying to do the following as our teacher's terms and conditions...

$sql = "SET @p0='$p_id'; CALL `rating_avg`(@p0);";
$pp = mysqli_query($link,$sql);
if($pp == false)die("QUERY DIDN'T EXECUTE");

Here 'rating_avg' is a procedure. I have created it manually in the phpMyadmin database. While creating I found the following query...

CREATE PROCEDURE `rating_avg`(IN `pid` INT) DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER select avg(reviewer_given_rating) AS v from paper_has_reviews where paper_id=pid;

Everything is okay. I just don't know how to run the query $sql = "SET @p0='$p_id'; CALL rating_avg(@p0);";

mysqli_query() isn't working here.

I love how your teacher forces you to write code that is wide open to SQL injection. Oh wait, no I don't. In the meantime, you might want to follow some tutorials as your query probably executes but doesn't return the result in the way you expect it. Start here for example: tutorialspoint.com/mysqli/mysqli_select_query.htm — Loek
– Loek, Commented Jun 18, 2019 at 11:50

d3jn · Accepted Answer · 2019-06-18 13:12:55Z

0

Try using DB::statement() for this:

// Splitting it into separate statements makes for better code structure.
DB::statement("SET @p0='$p_id'");
DB::statement("CALL rating_avg(@p0)");

But it requires that you have configured your database connection properly inside your Laravel app. I suggest you visit official documentation on the matter.

answered Jun 18, 2019 at 13:12

d3jn

1,4103 gold badges15 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Kazi Ziaul Hassan Over a year ago

I already said that my course teacher forbid to use model. I have just tried what you have suggested me to do. But here is saying DB is not found

Kazi Ziaul Hassan Over a year ago

Thanks :D . It works now . Thanks again for your collaboration.

Kazi Ziaul Hassan Over a year ago

How can I collect the value generated from the query "select avg(reviewer_given_rating) AS v from paper_has_reviews where paper_id=pid;" inside the procedure to web page. I want to show the generated value comes from avg(reviewer_given_rating) in web page.

Kazi Ziaul Hassan Over a year ago

I did "$data = DB::select("CALL rating_avg(@pid)");" as you suggest and it successfully executed also. But how can I get that avg(rivewer_given_rating) value from the array of object $data. Would you please elaborate @d3jn ?

Kazi Ziaul Hassan Over a year ago

stackoverflow.com/questions/56678779/… @d3jn

|

Collectives™ on Stack Overflow

How to execute the query of procedure with parameter calling in laravel

1 Answer 1

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related