Executing multiple queries in codeigniter that cannot be executed one by one

Question

I have an "event" table. For simplicity, you can imagine that it should be like a hierarchical category. It uses the nested set model (Thanks to Mark Hillyer for his post at http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/)

My code:

$query = 
"LOCK TABLE event WRITE;
SELECT @ParentRight := parent.rgt, @Level := parent.level FROM event AS parent WHERE parent.id = '{$data['parent_id']}';

UPDATE event SET rgt = rgt + 2 WHERE rgt > @ParentRight;
UPDATE event SET lft = lft + 2 WHERE lft > @ParentRight;

INSERT INTO event(lft, rgt, level) VALUES(@ParentRight, @ParentRight + 1, @Level);
UNLOCK TABLES;";

mysqli_multi_query($this->db->conn_id, $query);

$data['id'] = $this->db->insert_id();
return $this->db->update('event', $data);

And after that I'm going to update the last inserted object with $this->db->update('event', $data)

$data is an array that user has filled.

---

Problem 1:

I couldn't execute $query with $this->db->query($query);;

Solution 1 that didn't work:

I. Use mysqli db engine.

II. mysqli_multi_query($this->db->conn_id, $query); While I thought it works, it is giving the following error:

Commands out of sync; you can’t run this command now.

---

Problem 2:

$this->db->insert_id() doesn't work (returns 0)

Problem 3:

$this->db->update('event', $data); errors: Commands out of sync; you can't run this command now

---

How can I correct this code to work? I'd be even happy to find a solution for the first problem.

Answer 1

Maybe use transactions?

$this->db->trans_start();
$this->db->query('AN SQL QUERY...');
$this->db->query('ANOTHER QUERY...');
$this->db->query('AND YET ANOTHER QUERY...');
$this->db->trans_complete(); 

https://www.codeigniter.com/user_guide/database/transactions.html

Answer 2

Why not just write a stored procedure that does all the SQL you listed above taking the variables in your queries as parameters. Then just call the stored procedure as a single SQL statement;

$sql = "CALL some_sp($param1, $param2...etc)";