SQL query for inserting a new node in a table using the nested set model fails to execute

I'm currently working with the nested set model and i've read the article from the MySQL site (Managing Hierarchical Data in MySQL).

I got the following query directly from the article:

LOCK TABLE categories WRITE;

SELECT @myRight := right FROM categories WHERE name = ?;

UPDATE categories SET right = right + 2 WHERE right > @myRight;
UPDATE categories SET left = lft + 2 WHERE left > @myRight;

INSERT INTO categories (name, left, right) VALUES(?, @myRight + 1, @myRight + 2);

UNLOCK TABLES;

I'm using PHP and MySQLi functions to execute this statement like this:

if (false !== $stmt = $mysqli->prepare($query)) {
    $stmt->bind_param('ss', 'services', 'hosting');开发者_开发知识库
    $stmt->execute();
    $stmt->close();
} else {
    echo $mysqli->error;
}

The following code produces a syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT @myRight := right FROM categories WHERE name = ?; UPDATE categories SET righ' at line 3

I hope someone can help me with this.

MySQLi::prepare only works on single-statement queries.

You should break your query into a series of single statements, prepare those preparable and issue them independently.

Alternatively, wrap your batch into a stored procedure and call it.