CODEWITHSUNDEEP
mysqlsqlphpmyadmin
Toffic
Toffic

Reputation: 28

How to convert SQL query with LOCK TABLE into stored procedure with SELECT...FOR UPDATE

I am trying to project post categories on my website (training PHP) and decided to use Mike Hillyer's nested style, that I found on HIS PAGE

The problem I have is that I want to set his methods as "stored procedures" on my phpMyAdmin SQL server, BUT adding nodes and deleting them uses LOCK TABLE which is forbidden to use in stored procedures. Can someone tell me how to change one of his SQL queries into one working in stored procedure and explain why?

First query for adding nodes:

LOCK TABLE nested_category WRITE;

SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';

UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

I found that there is something like SELECT...FOR UPDATE method, but I am a total beginner in SQL and can't figure it out myself ;/

Upvotes: 0

Views: 121

Answers (1)

Kasikn77
Kasikn77

Reputation: 309

I think you should start from this article: https://www.mysqltutorial.org/getting-started-with-mysql-stored-procedures.aspx

I cannot see BEGIN, END and CREATE PROCEDURE in your query so I guess that what you have here can be just run like a raw query not procedure.

Before you will create new procedure you should define what that procedure should do? I read article you send and it looks like it was responsible for adding nodes.

Lets name it addNode. First parameter should be parent (that will be name of parent node - 'TELEVISION' in your example. Lest name second as child ('GAME CONSOLES'). Both should be same typa as name column in nested_category table.

DELIMITER //

CREATE PROCEDURE addNode(
    parent varchar(20),
    child varchar(20)
)
BEGIN
    -- << you will figure this out :) >>
    -- SELECT [...] WHERE name = parent
    -- UPDATE [...]
    -- INSERT [...] VALUES(child, [...]
END //

DELIMITER ;

Now in your PHP you can run:

CALL addNode('TELEVISION', 'GAME CONSOLES');

Because in procedure we have begin and end everything that happens between them is one set of statements that should be executed together, so you don't need to lock your tables. Read more about transactions and isolation level.

PS: I didn't check that query. Hope it is fine.

Upvotes: 2

Related Questions