How to run multi queries at once in Codeigniter?

Question

$sql = "
        DELETE FROM `products` WHERE id = 123456; 
        INSERT INTO `products` SET `name` = 'abc', `price` = 100;
        DELETE FROM `survey` WHERE id = 2522; 
        INSERT INTO `survey` SET `survey_name` = 'bla bla', `date` = 10-07-2018;
       ";

When I run it from PHPMyAdmin Then it's working. But when I run it from Codeigniter as $this->db->query($sql); then it's not working.

How can I solve it??

Note: In $sql variable there have chunk of queries. I want to hit server one time with chunk of queries. Then server executes those queries one by one. I never want to hit multiple time hit from PHP.

In **$sql** variable there have chunk of queries. I want to hit server one time with chunk of queries. Then server execute those queries one by one. — Md. Kamruzzaman Khondakar, Jul 10 '18 at 05:27
it works in php my admin because it treats `;` as a new query. contrary to what you might believe each is getting executed one-by-one. transactions are your best bet here if it matters to you that if one query fails the rest shouldn't be committed and you already have a good answer for that. — Alex, Jul 10 '18 at 07:04

score 2 · Answer 1 · answered Jul 10 '18 at 05:09

2

Maybe use transactions?

$this->db->trans_start();
$this->db->query('DELETE FROM `products` WHERE id = 123456');
$this->db->query('INSERT INTO `products` SET `name` = 'abc', `price` = 100');
$this->db->query('DELETE FROM `survey` WHERE id = 2522');
$this->db->query('INSERT INTO `survey` SET `survey_name` = 'bla bla', `date` = 10-07-2018');
$this->db->trans_complete();

Related issue: https://stackoverflow.com/a/17358652/5566169

answered Jul 10 '18 at 05:09

Nadimul De Cj

484
4
16

1

exactly what I thought to write. – Abdulla Nilam Jul 10 '18 at 05:10
Your query will hit server one by one. I want to hit server one time. Then server will execute one bye one. / I build a variable which have chunk of queries. Then hit to server *$this->db->query($sql);* – Md. Kamruzzaman Khondakar Jul 10 '18 at 05:18
In **$sql** variable there have chunk of queries. I want to hit server one time with chunk of queries. Then server execute those queries one by one. – Md. Kamruzzaman Khondakar Jul 10 '18 at 05:27
@Md.KamruzzamanKhondakar this is not possible because $sql execute one one single query and return a value . You can try explode your string like good example by https://stackoverflow.com/a/51257727/5566169 – Nadimul De Cj Jul 10 '18 at 05:44

Burak Demirbilek · Answer 2 · 2018-07-10T05:36:57.747

1

According to the CodeIgniter Docs, you should make a transactions if you want to run multiple queries. But this solution is already answered, I'm not writing it again.

But I want to show, you can still write your queries in one variable and use for loop for splitting and executing one by one:

$sqls = explode(';', $sql);
array_pop($sqls);

foreach($sqls as $statement){
    $statment = $statement . ";";
    $this->db->query($statement);   
}

This code piece belongs to the github user Relequestual, I used his example code on his gist to give you an alternative way and approach.

Edit: You should use transactions if you want to pass the multiple queries to the db in one time, the transaction handles after that and executes for you. So the code should be like this:

$sqls = explode(';', $sql);
array_pop($sqls);

$this->db->trans_start();
foreach($sqls as $statement){
    $statment = $statement . ";";
    $this->db->query($statement);   
}
$this->db->trans_complete();

edited Jul 10 '18 at 05:36

answered Jul 10 '18 at 05:21

Burak Demirbilek

89
1
5

In **$sql** variable there have chunk of queries. I want to hit server one time with chunk of queries. Then server execute those queries one by one. – Md. Kamruzzaman Khondakar Jul 10 '18 at 05:26
I've updated my answer, is this satisfies your needs, can you check it out? – Burak Demirbilek Jul 10 '18 at 05:40
It will hit one bye one to server. I want to hit once to server then server execute those queries one by one. – Md. Kamruzzaman Khondakar Jul 10 '18 at 05:43

score 0 · Answer 3 · answered Jul 10 '18 at 05:12

0

Use transactions as bellow-

$this->db->trans_start();
$this->db->query("DELETE FROM products WHERE id = 123456");
$this->db->query("INSERT INTO products SET name = 'abc', price = 100;");
$this->db->query("DELETE FROM survey WHERE id = 2522");
$this->db->query("INSERT INTO survey SET survey_name = 'bla bla', date = '10-07-2018'");
$this->db->trans_complete();

answered Jul 10 '18 at 05:12

Rubel Talukder

11
1

sorry i didn't see – Rubel Talukder Jul 10 '18 at 05:20
In **$sql** variable there have chunk of queries. I want to hit server one time with chunk of queries. Then server execute those queries one by one. – Md. Kamruzzaman Khondakar Jul 10 '18 at 05:27
each query hits server once though all queries are in one line or a variable. So use transaction is a better solution. – Rubel Talukder Jul 10 '18 at 12:07

score 0 · Answer 4 · edited Aug 28 '20 at 15:59

0

You can use Union to join the queries:

This works for me in Codeinginter:

$result = $this->db->query($query1 .' UNION '. $query2)->result_array();

edited Aug 28 '20 at 15:59

marc_s

732,580
175
1,330
1,459

answered Aug 28 '20 at 11:44

Zaryab Akhtar

1
1

How to run multi queries at once in Codeigniter?

4 Answers4