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, Jan 25 '12 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, Jan 25 '12 at 10:30
What I know is just that the vars are not accessible after the query that declares them, is left. — Mohammad Naji, Jan 25 '12 at 11:46
Wow, that's odd behaviour, because I thought the semicolon would denote the end of the statement anyway. — GordonM, Jan 25 '12 at 15:27
Sorry, I don't understand what you mean by "odd behavior". At end of each mysql command there IS a semicolon. I think that don't understand your speech :-/ As I've updated my question, recently I got that even using `mysqli_multi_query()` doesn't work! — Mohammad Naji, Jan 25 '12 at 15:39
What I mean is that your SQL string is split up into several SQL statements with semicolons. For example LOCK TABLE event WRITE; is a single SQL statement, the semicolon (;) symbol at the end marks the end of the statement. While there is only a single string of SQL, that string contains multiple statements. — GordonM, Jan 25 '12 at 16:01
@zrvan Added the error message to Problem#1. Thank you for your notice :) — Mohammad Naji, Jan 25 '12 at 16:17
@GordonM Sorry for misunderstanding your previous comment. Yes, I am using multiple statements at the same query because I couldn't execute them one by one. Because the second command hold a variable that should be used later. — Mohammad Naji, Jan 25 '12 at 16:21

score 19 · Accepted Answer · edited Nov 13 '18 at 05:35

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 '18 at 05:35

Stack Programmer

679
6
18

answered Jun 28 '13 at 06:36

webmaster_sean

942
4
13
23

Don't forgot to check all query executed or not by checking transaction status $this->db->trans_status() https://stackoverflow.com/a/34695559/5803974 – Satish Apr 19 '18 at 13:00

score 4 · Answer 2 · answered Jan 27 '12 at 01:12

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 '12 at 01:12

Skittles

2,866
9
31
37

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. – semira May 26 '21 at 06:45

score -2 · Answer 3 · answered Jul 03 '20 at 11:20

You Can Simply Use the following:

 print_r($this->db->query("YOUR query NO 1") ? 'Success Q1' : $this->db->error());
 print_r($this->db->query("YOUR query NO 2") ? 'Success Q2' : $this->db->error());
 print_r($this->db->query("YOUR query NO 3") ? 'Success Q3' : $this->db->error());

score -3 · Answer 4 · edited Feb 06 '18 at 10:32

$query1 = $this->db->query("SELECT * FROM `Wo_Products` WHERE `boost_plan`=1 AND `tamatar`=1 AND`active`=1 ORDER BY rand()  LIMIT 3");

$query2 = $this->db->query("SELECT * FROM `Wo_Products` WHERE `boost_plan`=2 AND `tamatar`=1 AND`active`=1 ORDER BY rand()  LIMIT 3");

$query3 = $this->db->query("SELECT * FROM `Wo_Products` WHERE `boost_plan` NOT IN (0,1) AND `tamatar`=1 AND`active`=1 ORDER BY rand()  LIMIT 6");

    $result1 = $query1->result();
    $result2 = $query2->result();
    $result3 = $query3->result();
    return array_merge($result1, $result2, $result3);

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

4 Answers4

Linked

Related