Insert Batch php mysql with check duplicate and sum values of field duplicate

Question

i have 1 million data using foreach. ex table: table

the data data i want to inserting that's data using batch/multipleinsert, but i have problem when i got duplicate data. if data duplicate i want the field amount will sum and update amount field with sum amount duplicated data.

this is my code before

<?php
foreach ($data_transaksi as $key)
      {
          if($key['STATUS_COA'] != $key['CHART_OF_ACCOUNT_STATUS'])
          {
              if($key['ACCOUNT_CATEGORY_CODE'] == '9')
              {
                  $amount = round($key['AMOUNT'],2);
              }
              else
              {
                  $amount = round($key['AMOUNT'],2) *-1;
              }
          }
          else
          {
              if($key['ACCOUNT_CATEGORY_CODE'] == '9')
              {
                  $amount = round($key['AMOUNT'],2)*-1;
              }
              else
              {
                  $amount = round($key['AMOUNT'],2);
              }
          }

          
          $dt_exsis = $this->ledger_model->cek_data_coa_exsis($key['COA_CODE'],$modul,$ID_USER);
          if(empty($dt_exsis['id']))
          {
              //TRYINSERTINGBATCH
              // $datainsert[] = '('.$key['COA_CODE'].','.$amount.','.$ID_USER.',"'.$modul.'")';
              // $test = $key['COA_CODE'];

              $datainput = array(
                      'COA_CODE' => $key['COA_CODE'],
                      'AMOUNT' => $amount,
                      'MODUL' => $modul,
                      'USER_ID' => $ID_USER
                      );
                      
              $this->ledger_model->save_rows_to_table($datainput,'finance_lapkue_temp');
          }
          else
          {
              $amount_fix = $amount + $dt_exsis['AMOUNT'];
              $data=array(
                'AMOUNT' => $amount_fix
              );
              $this->ledger_model->edit_rows_to_table_where($data,'finance_lapkue_temp','id',$dt_exsis['id']);
              // $q = "UPDATE finance_lapkue_temp set AMOUNT = '$amount_fix' where id = '".$dt_exsis['id']."'";
              // $this->db->query($q);
          }
          // $data_amount[$key['COA_CODE']] += $amount;
      }
     ?>

if i using this code, the proccess so slow

What is `SHOW CREATE TABLE {tablename}`? What constitutes 'duplicate data'? There is an [INSERT ON DUPLICATE KEY UPDATE](https://dev.mysql.com/doc/refman/5.7/en/insert-on-duplicate.html). This is going to be written in a way that [doesn't have SQL injection](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) right? — danblack, Feb 28 '19 at 03:33
The fastest way is to use `LOAD DATA LOCAL INFILE`. All options with `INSERT` keyword are slow. — user1597430, Feb 28 '19 at 03:37
i'm trying using isertingbatch using native but have problem with duplicate data in foreach to inserting. i want sum field amount duplicate and update field amount to the amount field in the first insert duplicate data — ryan apriansyah, Feb 28 '19 at 03:47

score 0 · Accepted Answer · answered Feb 28 '19 at 04:13

Good option will to pass only data to DB that you want to insert. All the data cleaning task can be done in controller.

// Create a data array and add all info
$data = [];
//if user does not exist add it in array
if (empty($dt_exist($id))) {
  $data[$ID_USER] = array(
                  'COA_CODE' => $key['COA_CODE'],
                  'AMOUNT' => $amount,
                  'MODUL' => $modul,
                  'USER_ID' => $ID_USER
                  );
}
else {
      //if user exist in $data just modify the amount
      if (!empty($data[$ID_USER])) {
        $data[$ID_USER]['AMOUNT'] += $dt_exsis['AMOUNT']; 
     }
      else {
        // if user does not exist in data create add all info
        $data[$dt_exsis['ID_USER']] = array(
                  'COA_CODE' => $dt_exsis['COA_CODE'],
                  'AMOUNT' => $dt_exsis['amount'],
                  'MODUL' => $dt_exsis['modul'],
                  'USER_ID' => $dt_exsis['ID_USER']
                  );
    }
}

This will save multiple calls to DB and at the end you can pass $data and do multiple insert.

Insert Batch php mysql with check duplicate and sum values of field duplicate

1 Answers1