CODEWITHSUNDEEP
phpmysqllaravel
Eddie Dane
Eddie Dane

Reputation: 1589

How to execute a raw sql query with multiple statement with laravel

Is there anyway I can execute a query with multiple statement like the one below, using laravel framework. I have tried using DB::statement but returned a sql syntax error, but when I execute the same query on phpmyadmin I works, its so frustrating. Please help me.

EG

LOCK TABLE topics WRITE;

SELECT @pRgt := rgt FROM topics WHERE id = ?;

UPDATE topics SET lft = lft + 2 WHERE rgt > @pRgt;
UPDATE topics SET rgt = rgt + 2 WHERE rgt >= @pRgt;

INSERT INTO topics (title, overview, article, image, lft, rgt)
VALUES (?, ?, ?, ?, @pRgt, @pRgt + 1);

UNLOCK TABLES;

Upvotes: 21

Views: 16007

Answers (2)

Ishmael Mavor Raines
Ishmael Mavor Raines

Reputation: 330

Thanks @TheSalamov

I thought to illustrate with general PHP example in Laravel

$result = DB::unprepared("LOCK TABLE table1 READ, table1 WRITE, table2 READ, table3 READ;"  
    .  $query_multi_stmt 
    .  " UNLOCK TABLES;");

if ($result > 0 ){  //...if rows affected
           //...do something
}

Upvotes: 2

TheSalamov
TheSalamov

Reputation: 917

DB::unprepared() should do the trick , laravel prepare and do some stuff for sql queries .
But instead you can use absolutely raw ones using DB::unprepared .

EG

DB::unprepared('LOCK TABLE topics WRITE;

SELECT @pRgt := rgt FROM topics WHERE id = ?;

UPDATE topics SET lft = lft + 2 WHERE rgt > @pRgt;
UPDATE topics SET rgt = rgt + 2 WHERE rgt >= @pRgt;

INSERT INTO topics (title, overview, article, image, lft, rgt)
VALUES (?, ?, ?, ?, @pRgt, @pRgt + 1);

UNLOCK TABLES;');

It will execute your whole SQL query whether it is single/multiple statements .

Upvotes: 46

Related Questions