preorder traversal nodes are not deleting in codeigniter

Question

I've following sql query to delete the nodes.

$sql="LOCK TABLE xp_subunit WRITE; ";
            $sql .= "SELECT @myLeft := ".$_GET['lft'].", @myRight :=  ".$_GET['rgt'].", @myWidth :=  ".$_GET['lft']." - lft + 1
            FROM xp_subunit
            WHERE id =".$_GET['id']."; ";

            $sql .= "DELETE FROM xp_subunit WHERE lft BETWEEN @myLeft AND @myRight; ";

            $sql .= "UPDATE xp_subunit SET rgt = rgt - @myWidth WHERE rgt > @myRight; ";
            $sql .= "UPDATE xp_subunit SET lft = lft - @myWidth WHERE lft > @myRight; ";
            $sql.="UNLOCK TABLES;";

            //echo $sql;
            echo $query = $this->db->query($sql);

I tried this query in phpmyadmin it executes successfully and delete the node as desired. but when I'm converting it to codeigniter code it gives mysql syntax error like:

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 'DELETE FROM xp_subunit WHERE lft BETWEEN @myLeft AND @myRight;UPDATE xp_subunit ' at line 3

SELECT @myLeft := 1, @myRight := 29, @myWidth := 1 - lft + 1 FROM xp_subunit WHERE id =1; DELETE FROM xp_subunit WHERE lft BETWEEN @myLeft AND @myRight;UPDATE xp_subunit SET rgt = rgt - @myWidth WHERE rgt > @myRight;UPDATE xp_subunit SET lft = lft - @myWidth WHERE lft > @myRight;

Does I'm doing something wrong... thanks in adv.

score 1 · Accepted Answer · answered Jul 28 '12 at 08:42

1

$this->db->query($sql) is meant to run a single query.

Multiple queries in a single call are not supported.

answered Jul 28 '12 at 08:42

Yan Berk

14,328
9
55
52

so is there any alternative for this? – J.K.A. Jul 28 '12 at 08:48
Read here: http://stackoverflow.com/questions/8156828/codeigniter-how-to-run-multiple-batch-queries – Yan Berk Jul 28 '12 at 08:57
I used `mysqli_multi_query()` it is executing and deleting records. how to get it is successfully executed .. not returning any value. – J.K.A. Jul 28 '12 at 09:00
Whats wrong with it returning a boolean value? If you don't need it, don't check for it. – Yan Berk Jul 28 '12 at 09:05
It not returning any value...blank – J.K.A. Jul 28 '12 at 09:13
Thats a different problem and its not related to this question. Please post a new question regarding this matter. – Yan Berk Jul 28 '12 at 09:15

preorder traversal nodes are not deleting in codeigniter

1 Answers1