1

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.. =)

derloopkat
  • 6,232
  • 16
  • 38
  • 45
Marc Arren Q. Salita
  • 13
  • 4
  • https://stackoverflow.com/questions/41599703/what-is-the-codeigniter-equivilent-to-php-multi-query/41600560#41600560 – Vickel Jan 31 '18 at 02:05

2 Answers2

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

AL-zami
  • 8,902
  • 15
  • 71
  • 130
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.

DFriend
  • 8,869
  • 1
  • 13
  • 26
  • 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'); – Marc Arren Q. Salita Jan 31 '18 at 06:44
  • 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.. – DFriend Jan 31 '18 at 08:51