@vincej Here You Go

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

Note $result returns 0. Also I had change table def allow nulls for updated_at and created_at. See you are gonna need to get error info from this. I highly recommend you get PHPStorms debug going on Linux. Its makes it easier to explore.

I found unprepared here...

http://stackoverflow.com/questions/14687993/laravel-4-how-to-run-a-raw-sql

DB:statement and such, use prepared PDO statements and in this case we have statements with data already supplied. In your case the select variables. You can inject PHP values with regular variables. You made to escape single quotes as I did.

What version are you running? I did this under 5.3 latest.

@jimmck

You Sir, are a Rockstar !! The word 'Thank you' does not go far enough in my appreciation. Yes, I changed the DB defaults to a timestamp. That was just an oversight on my part. Escaping the record did not come to my mind. I can not recall ever escaping a value other than perhaps some html.

Debugger I would love to get Storm debugger working, did spend about 6 hours back in June trying it. Watched video's Googled, Got Xdebug installed, but in the In the end I gave up as nothing seemed to make it work. In my mind this should be easy, and yet JetBrains makes it hard.

Version Still using L5.1. I don't use any of the features in 5.3, and so have not yet done it. I would very much prefer to stay current, however, again, time is always against me. Please tell me. Rather than manually updating 5.1 could I not just make a fresh install of 5.3 and just drop my existing code in? Ok, perhaps a pain, as I would then also have to re-import all my vendor packages.

General Thoughts I'm perhaps old school, and if I recall your bio, you too are an old .Net / Java developer. In many respects I would prefer the old school way of working with the raw PHP and SQL. I find that I can spend a huge amount of time trying to do things in Laravel which in PHP are more verbose, yes, but at the very least you know what is going on. Yes, I could spend days examining the API, but again time is against me. The API is great, but for me, not very user friendly - so we end up relying on StackOverflow.

Shame there is no private messaging feature on Laracasts. Thanks Again !

@vincej Glad its going better. Yes, I have built a few things. Eloquent may be good for simple things, but for developers who know how to write SQL I think it can be counter intuitive. You wanted to use 'Laravel-esque' functions at this point hence the answers. You can just create a new Laravel project and put your code in that project, I mentioned the version as a point of reference for you. I suspect you like Laravel for the Routing model. Which I do too. Some of the other parts are just not sustainable from a complexity point of view. Gary Hockin from JetBrains has a video on the debugging.

https://blog.jetbrains.com/phpstorm/2016/10/webinar-recording-step-into-debugging-with-phpstorm/

And as a longtime customer I have complained to them ad nausea as to why they have 1000 ways to do something. It can be easier to read legal brief than the docs!!!

@jimmck Thanks for the link - I will give it another go. At least this guy is British and understandable. I lived in UK for 26 years and am a dual national ! The other guy with the heavy Russian like accent was incomprehensible !

Glad you agree, that Eloquent can be a pain. Personally I prefer raw SQL. However, when I can , I try to push it into QB / Eloquent as I am hopeful that Laravel is taking care of security.

@jimmck

Heah Jim, I'm back :o)

After a little thought I changed my strategy. To remind you I am building a tree of categories using nested hierarchies. Rather than have a new query every single time you select a different node, I decided to pull the whole categories table in as a collection and then do the queries in Javascript. I have 4 levels and It all works great. BUT I am tripping up on something notionally trivial.

To add a new node I have to go back to Laravel + Sql. You helped out with using a sql query using unprepared(). 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. When using the variables I get nothing. No error message, nothing. Even Storm gives no errors, but it is not correct.

What am I doing wrong ?? As always , I thank you in advance !!

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

@vincej Hey. Did not see your message. This new interface does not have them standout like they used to.

Looking at it. Can I set $category to something in the sample table I built? I assume yes. Whats the .\ ??? Oh wait. Storm IDE likes this code? I gotta put together...

@jimmck

Thank you for coming back. You informed me 3 weeks ago that I had to escape those quotes hence the backslashes. That threw me. So I check on StackOverflow and everyone is talking about concatenating the values being passed. So ... I spent ages playing with back slashes and dots, and got no where. :o)

This morning, with a fresh head, I just entered '$category';. No escaping no concatenation and who would have guessed - it worked !

Wholly different question: I have huge quantities of JS & JQuery in my application. Do you use a JS framework? What do you get out of it? Why bother? Should I mid way change over from JS to say Vue? Or should I just carry on with raw JS & Jquery ?

Sorry to bug you. :o) Cheers !!

@vincej Hey. Yes it looked like a character issue last night. Did not see your post until late. You need to put single quotes around strings for SQL as you know. But inside double quotes the single quote as you posted is fine. I use my own JS framework for Ajax switching to Axio and I use JQuery and Vue. I don't know your timelines or deliverables but I would keep it the way you got it. Vue is great but you should develop in Vue 2 and there are lots of idiosyncrasies to learn. You can always port it over. I used straight JS and JQuery for a longtime and non single page apps it works fine. I totally agree with doing the data manipulation in JS as you mentioned, saves on the server round trips. I always keep an eye out for your posts. You can gmail my screenname... Glad to Help!