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.

You could have an array of single queries and execute them in a loop — GordonM
– GordonM, Commented Jan 25, 2012 at 10:23
No, as you can see in this case executing as separate queries doesn't work because it cannot recall @myRight and @Level vars from past queries' results — Mohammad Naji
– Mohammad Naji, Commented Jan 25, 2012 at 10:30
What I know is just that the vars are not accessible after the query that declares them, is left. — Mohammad Naji
– Mohammad Naji, Commented Jan 25, 2012 at 11:46
Wow, that's odd behaviour, because I thought the semicolon would denote the end of the statement anyway. — GordonM
– GordonM, Commented Jan 25, 2012 at 15:27

Stack Programmer · Accepted Answer · 2018-11-13 05:35:57Z

19

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

edited Nov 13, 2018 at 5:35

Stack Programmer

6776 silver badges18 bronze badges

answered Jun 28, 2013 at 6:36

webmaster_sean

9825 gold badges13 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Satish Over a year ago

Don't forgot to check all query executed or not by checking transaction status $this->db->trans_status() stackoverflow.com/a/34695559/5803974

Skittles · Accepted Answer · 2012-01-27 01:12:01Z

4

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)";

answered Jan 27, 2012 at 1:12

Skittles

2,9189 gold badges33 silver badges42 bronze badges

1 Comment

semira Over a year ago

Codeigniter not allows to run a query with multiple statement in a single $this->db->query($sql); function call. we cannot use the point you mentioned.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related