How to use batch update/insert sql using codeigniter?

Question

in this example.. how can i simplified and fasten up the update and insert of data in the database?

//if count($arr_list['sample_element1'] is 500+++ or more records

$i =0;
while ($i < count($arr_list['sample_element1'])) {

$update_db= array('column1' => $arr_list['sample_element1'][$i],'column2' => $arr_list['sample_element2'][$i]);

$this->db->update('sample_table',$update_db);

$i++;
}

if the record to be save is over 1000 data...it will take about sometime to finish the process.. thank in advance.. =)

stackoverflow.com/questions/41599703/…

Vickel
– Vickel

2018-01-31 02:05:16 +00:00
Commented Jan 31, 2018 at 2:05 — Vickel
– Vickel, Commented Jan 31, 2018 at 2:05

AL-zami · Accepted Answer · 2018-01-31 01:59:54Z

0

use batch_update

$this->db->update(table_name,array,where_key_word);

here table_name = your table name

and array = an array containing multiple associative array

and where_key_word = the column which should be used in where condition

also read this question thread for more information

batch updating thousands of element together may create performance issue.Better split them up and update by smaller batch.Also use transactions so that if any of the update fails the whole update procedure can be rolled back

edited Jan 31, 2018 at 1:59

answered Jan 31, 2018 at 1:54

AL-zami

9,20417 gold badges79 silver badges138 bronze badges

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

Comments

DFriend · Accepted Answer · 2018-01-31 02:59:42Z

0

Taking @AL-zami's answer you're looking for this

$data = [];
$this->db->trans_start();
foreach($arr_list as $item)
{
    $data[] = [
        'column1' => $item['element1'],
        'column2' => $item['element2'],
        'column3' => $item['element3']
    ];
}
$this->db->update_batch('mytable', $data, 'column1');
$this->db->trans_complete();

Where 'column1' is the column used for the "WHERE" condition.

answered Jan 31, 2018 at 2:59

DFriend

8,9741 gold badge16 silver badges26 bronze badges

2 Comments

Marc Arren Q. Salita Over a year ago

yes yes.. i have the same idea... but what if you have two or more where clause? do i have to add another parameter like this? $this->db->update_batch('mytable', $data, 'column1','column2','column3');

DFriend Over a year ago

No, that won't work. update_batch() won't accept that. Try using another $this->db->where('field', $value) call. But I don't think that will work..

Collectives™ on Stack Overflow

How to use batch update/insert sql using codeigniter?

2 Answers 2

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related