如何在Codeigniter中通过一个查询插入和更新现有数组数据?

问题描述

我在arrays中拥有表中的所有数据.其中我们updated existing data和一些added new data.此数据是从 CSV 导入的，并存储在array中.

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.

问题是:

如何在Codeigniter中使用"ON DUPLICATE KEY UPDATE"通过单个查询插入和更新现有数据?

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

像这样的桌子.

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

控制器

  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);

          }

型号

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

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

**非常感谢，谁能解决这个问题:) **

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

推荐答案

这很疯狂.

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);

          }

处于模式...

 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);
         }
   }

这篇关于如何在Codeigniter中通过一个查询插入和更新现有数组数据?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！