How to pass PHP variable into Laravel DB::unprepared query

Question

working with Laravel 5.1. I have this query below. It works fine in phpMyAdmin. It will also work if I hard code the $category and the $value. But no number of concatenations and / or escaping of the variables gets the query to run in my controller. I am using use Illuminate\Support\Facades\DB; I have tried encapsulating the query with single quotes and double quotes, to no effect. I am using PHPStorm and it even says the query is correct, but it is not. What am I doing wrong ??

public function addNode(Request $request)
{

$category = $request->input('parent_category');
$value = $request->input('new_category');


$result = DB::unprepared("
LOCK TABLE nested_categories WRITE;
SELECT @myLeft := lft FROM nested_categories
WHERE name =  '.\'$category\'.';
UPDATE nested_categories SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_categories SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO nested_categories(name, lft, rgt) VALUES( '.\'$value\'.', @myLeft + 1, @myLeft + 2);
UNLOCK TABLES;");

return redirect('nested_categories');
}

TKS !!

Vince · Accepted Answer · 2016-11-22 18:37:46Z

1

Easy when you know how. Forget concatenation or back slashes. Just use this:

$category = $request->input('parent_category');
$value = $request->input('new_category');

$result = DB::unprepared("
LOCK TABLE nested_categories WRITE;
SELECT @myLeft := lft FROM nested_categories
WHERE name =  '$category';
UPDATE nested_categories SET rgt = rgt + 2 WHERE rgt > @myLeft;
UPDATE nested_categories SET lft = lft + 2 WHERE lft > @myLeft;
INSERT INTO nested_categories(name, lft, rgt) VALUES( '$value', @myLeft + 1, @myLeft + 2);
UNLOCK TABLES;");

answered Nov 22, 2016 at 18:37

Vince

1,5552 gold badges23 silver badges36 bronze badges

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

Collectives™ on Stack Overflow

How to pass PHP variable into Laravel DB::unprepared query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related