0

i read many import excel for insert data in Mysql Database, but i don't find reference how to update data by uploading excel file. So, How to update data in mysql DB when i import data excel file ?

Example : In DB Table A :

Id_Follow_Up Tgl_Fol_Up
1 2022-10-30
2 2022-10-29

In Excel

Id_Follow_Up Tgl_Fol_Up
1 2022-11-30
2 2022-11-30

So, i want update my Table with new data in Excel. Based example IN DB ID 1 is 2022-10-30 but i want update data based excel, so, i want to change ID 1 from 2022-10-30 to 2022-11-30. Likewise with ID 2 from 2022-10-29 to 2022-11-30.

Note : I use PHPExcel

This is my Controller code :

public function import_excel(){
    if(isset($_FILES["fileExcel"]["name"])){
      $path = $_FILES["fileExcel"]["tmp_name"];
      $object = PHPExcel_IOFactory::load($path);
      foreach($object->getWorksheetIterator() as $worksheet){
        $highestRow = $worksheet->getHighestRow();
                $highestColumn = $worksheet->getHighestColumn();
        for($row=2; $row<=$highestRow; $row++)
        {
          $tgl_fol_up = $worksheet->getCellByColumnAndRow(1,$row)->getValue();
          $tgl_fol_up = \PHPExcel_Style_NumberFormat::toFormattedString($tgl_fol_up, 'YYYY-MM-DD');
          $id_follow_up = $worksheet->getCellByColumnAndRow(0,$row)->getValue();
          $temp_data[]=array(
            'tgl_fol_up' => $tgl_fol_up
          );
           $where[]=array(
            'id_follow_up' => $id_follow_up
          );
        }
      }
    
      $update = $this->Customer_list_model->update_import($where,$temp_data);
      if($update)
      {
        $_SESSION['pesan']  = "Data Berhasil di Import ke Database";
        $_SESSION['tipe']   = "success";
                redirect($_SERVER['HTTP_REFERER']);
      }else{
        $_SESSION['pesan']  = "Terjadi Kesalahan";
        $_SESSION['tipe']   = "danger";
        redirect($_SERVER['HTTP_REFERER']);
      }
    }else{
      echo "Tidak Ada File yang masuk";
    }
  }

This is my Model code :

public function update_import($where,$temp_data)
{
        $this->db->where($where);
         $insert = $this->db->insert_batch('data_detail', $temp_data);
        if($insert){
            return true;
        }
}

Please help me.

Nings
  • 1
  • 5

1 Answers1

0

I did it. I solved that by Model just remove and the controller i add and change code like this :

public function import_excel(){
    if(isset($_FILES["fileExcel"]["name"])){
      $path = $_FILES["fileExcel"]["tmp_name"];
      $object = PHPExcel_IOFactory::load($path);
      foreach($object->getWorksheetIterator() as $worksheet){
        $highestRow = $worksheet->getHighestRow();
                $highestColumn = $worksheet->getHighestColumn();
        for($row=2; $row<=$highestRow; $row++)
        {
          $tgl_fol_up = $worksheet->getCellByColumnAndRow(1,$row)->getValue();
          $tgl_fol_up = \PHPExcel_Style_NumberFormat::toFormattedString($tgl_fol_up, 'YYYY-MM-DD');
          $id_follow_up = $worksheet->getCellByColumnAndRow(0,$row)->getValue();
          $temp_data=array(
            'tgl_fol_up' => $tgl_fol_up,
            'id_follow_up '  => $id_follow_up 
          );
          $this->db->where('id_follow_up', $id_follow_up);
          $update = $this->db->update('data_detail',$temp_data);
        }
      }
    
      $update = $this->Customer_list_model->update_import($where,$temp_data);
      if($update)
      {
        $_SESSION['pesan']  = "Data Berhasil di Import ke Database";
        $_SESSION['tipe']   = "success";
                redirect($_SERVER['HTTP_REFERER']);
      }else{
        $_SESSION['pesan']  = "Terjadi Kesalahan";
        $_SESSION['tipe']   = "danger";
        redirect($_SERVER['HTTP_REFERER']);
      }
    }else{
      echo "Tidak Ada File yang masuk";
    }
  }
Nings
  • 1
  • 5