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

Question

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.

score 0 · Accepted Answer · answered Apr 22 '11 at 10:32

0

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.

answered Apr 22 '11 at 10:32

Quassnoi

413,100
91
616
614

So do I need mysqli::multi-query() then?, also stored procedures is not an option. – Melvin Apr 22 '11 at 10:33
@Melvin: either that (note that it cannot be prepared), or a stored proc. – Quassnoi Apr 22 '11 at 10:34

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

1 Answers1