How to insert and update existing array data with

2019-06-10 00:39发布

问题:

i have all data from table in arrays . In which we updated existing data and some added new data. This data had import from CSV and stored into array.

The Question is:

How to insert and update existing data with one single query in Codeigniter with "ON DUPLICATE KEY UPDATE"?

Table before like this..

id(auto incre)   invoice_code    item_code   item_rate

1                INPO311018-1    pip640up    62
2                INPO311019-43   plxliupp    43
3                INPO311012-05   al6408f     24

after insert&update at same time table would look like this.

id(auto incre)   invoice_code    item_code   item_rate

1                INPO311018-1    pip640up    59.99
2                INPO311019-43   plxliupp    40
3                INPO311012-05   al6408f     25.99
4                INPO011019-3   Ndry_milk    1.4
5                INPO021012-05   al894_ad     99

Controller

  function import_csv()
          {
            $this->load->library('csvimport');  //Load Library

            $file_data=$this->csvimport->get_array($_FILES["csv_file"]["tmp_name"]);
            foreach ($file_data as $row) {

              $data[]=array(
                'id'=>$row['id'],
                'invoice_code'=>$row['invoice Code'],
                'item_code'=>$row['item Code'],
                'item_rate'=>$row['item Code'],

              );

            }

            $this->load->model('invoice_model');
            $this->invoice_model->insert_data($data);

          }

Model

  function insert_data($data)          //Add & Update table with "CSV"

        {
            $this->db->insert_batch('po_invoice',$data);
        }

**Advanced thanks, who gonna solves this problem :) **

回答1:

This is crazy ..maybe.. i dont get it properly ... but try this...

function import_csv()
          {
            $this->load->model('invoice_model'); /// load it at beginning 
            $this->load->library('csvimport');  //Load Library

            $file_data=$this->csvimport->get_array($_FILES["csv_file"]["tmp_name"]);
            foreach ($file_data as $row) {

              $data = array(
                'id'=>$row['id'],
                'invoice_code'=>$row['invoice Code'],
                'item_code'=>$row['item Code'],
                'item_rate'=>$row['item Code'],
              );
           // in order to insert or update ... you cannot use insert batch ....  
           // pass it one by one 
             $this->invoice_model->insert_update_data($data);

            }


           //  $this->invoice_model->insert_data($data);

          }

at modal ...

 function insert_update_data($data)          //Add & Update table with "CSV"
    {

        $this->db->where('invoice_code',$data['invoice_code']);
        $this->db->where('item_code',$data['item_code']);
        $q = $this->db->get('po_invoice');
         if($q->num_rows() > 0){
          $this->db->where('invoice_code',$data['invoice_code']);
          $this->db->where('item_code',$data['item_code']);
          $this->db->update('po_invoice',$data);
         } else {
          $this->db->insert_batch('po_invoice',$data);
         }
   }