The following code runs incredibly slowly when performing a FOREACH LOOP then do filtering using data from table cabang and insert to a table called part_rak.
The filtering loop through each row on excel file then loop based comma separator
Filtering Loop
if (explode(",", $rowData[0][14]) > 1) {
foreach (explode(",", $rowData[0][14]) as $key => $value2) {
$data = array(
"rak_part_id" => $last,
"rak_cabang_id" => $this->spareparts_m->get_cabang_id_by_code("B"),
"rak_code" => $value2,
"rak_created_at"=> date('Y-m-d H:i:s')
);
$this->db->insert("part_rak",$data);
}
}else{
foreach (explode("|", $cabang) as $key => $value) {
$data = array(
"rak_part_id" => $last,
"rak_cabang_id" => $value,
"rak_code" => $expRak[$key],
"rak_created_at"=> date('Y-m-d H:i:s')
);
$this->db->insert("part_rak",$data);
}
}
here it is my model method get_cabang_id_by_code
public function get_cabang_id_by_code($cabang_code)
{
$q = $this->db->get_where("cabang",array("cabang_code" => $cabang_code));
return $q->result_array()[0]['cabang_id'];
}
I'd be grateful of any suggestions which would improve the performance of my script.
below is my complete import source code
public function import($uploaded)
{
$inputFileName = './public/uploads/excel/'.$uploaded['file_name'];
try {
$inputFileType = IOFactory::identify($inputFileName);
$objReader = IOFactory::createReader($inputFileType);
$objPHPExcel = $objReader->load($inputFileName);
} catch(Exception $e) {
die('Error loading file "'.pathinfo($inputFileName,PATHINFO_BASENAME).'": '.$e->getMessage());
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();
$highestColumn = $sheet->getHighestDataColumn();
$last_id="";
for ($row = 3; $row <= $highestRow; $row++){ // Read a row of data into an array
$rowData = $sheet->rangeToArray('A' . $row . ':' . $highestColumn . $row, NULL, TRUE, FALSE);
$data = array(
"part_no" => $rowData[0][0],
"part_revised_no" => $rowData[0][1],
"part_latest_no" => $rowData[0][2],
"part_desc" => $rowData[0][3],
"part_incoming_date"=> $rowData[0][4],
"part_fob_japan" => $rowData[0][5],
"part_min_order" => $rowData[0][6],
"part_master_key1" => $rowData[0][7],
"part_total_stock" => $rowData[0][8],
"part_stock_j" => $rowData[0][9],
"part_stock_b" => $rowData[0][10],
"part_stock_s" => $rowData[0][11],
"part_stock_m" => $rowData[0][12],
// "part_rak" => $rowData[0][13],
"part_last_opname" => $rowData[0][17],
"part_discontinued" => $this->convDiscontinued($rowData[0][18]),
"part_mesin_code" => $rowData[0][19],
"part_srp" => $rowData[0][20],
"part_srp_cod" => $rowData[0][21],
"part_supv_price" => $rowData[0][22],
"part_mgr_price" => $rowData[0][23],
"part_dtr_price" => $rowData[0][24],
"part_keterangan" => $rowData[0][25],
);
// $data['part_rak_id'] = implode(",", $arrLast);
$insert = $this->db->insert("sparepart", $data);
$last = $this->db->insert_id();
$cabang = $this->spareparts_m->get_cabang_id_by_code("J")."|".
$this->spareparts_m->get_cabang_id_by_code("B")."|".
$this->spareparts_m->get_cabang_id_by_code("S")."|".
$this->spareparts_m->get_cabang_id_by_code("M");
$rak_code = $rowData[0][13]."|".$rowData[0][14]."|".$rowData[0][15]."|".$rowData[0][16];
$expRak = explode("|", $rak_code);
$arrLast = [];
if (explode(",", $rowData[0][13]) > 1) {
foreach (explode(",", $rowData[0][13]) as $key => $value2) {
$data = array(
"rak_part_id" => $last,
"rak_cabang_id" => $this->spareparts_m->get_cabang_id_by_code("J"),
"rak_code" => $value2,
"rak_created_at"=> date('Y-m-d H:i:s')
);
$this->db->insert("part_rak",$data);
}
}else{
foreach (explode("|", $cabang) as $key => $value) {
$data = array(
"rak_part_id" => $last,
"rak_cabang_id" => $value,
"rak_code" => $expRak[$key],
"rak_created_at"=> date('Y-m-d H:i:s')
);
$this->db->insert("part_rak",$data);
// $arrLast[]= $this->db->insert_id();
}
}
if (explode(",", $rowData[0][14]) > 1) {
foreach (explode(",", $rowData[0][14]) as $key => $value2) {
$data = array(
"rak_part_id" => $last,
"rak_cabang_id" => $this->spareparts_m->get_cabang_id_by_code("B"),
"rak_code" => $value2,
"rak_created_at"=> date('Y-m-d H:i:s')
);
$this->db->insert("part_rak",$data);
}
}else{
foreach (explode("|", $cabang) as $key => $value) {
$data = array(
"rak_part_id" => $last,
"rak_cabang_id" => $value,
"rak_code" => $expRak[$key],
"rak_created_at"=> date('Y-m-d H:i:s')
);
$this->db->insert("part_rak",$data);
// $arrLast[]= $this->db->insert_id();
}
}
if (explode(",", $rowData[0][15]) > 1) {
foreach (explode(",", $rowData[0][15]) as $key => $value2) {
$data = array(
"rak_part_id" => $last,
"rak_cabang_id" => $this->spareparts_m->get_cabang_id_by_code("S"),
"rak_code" => $value2,
"rak_created_at"=> date('Y-m-d H:i:s')
);
$this->db->insert("part_rak",$data);
}
}else{
foreach (explode("|", $cabang) as $key => $value) {
$data = array(
"rak_part_id" => $last,
"rak_cabang_id" => $value,
"rak_code" => $expRak[$key],
"rak_created_at"=> date('Y-m-d H:i:s')
);
$this->db->insert("part_rak",$data);
// $arrLast[]= $this->db->insert_id();
}
}
if (explode(",", $rowData[0][16]) > 1) {
foreach (explode(",", $rowData[0][16]) as $key => $value2) {
$data = array(
"rak_part_id" => $last,
"rak_cabang_id" => $this->spareparts_m->get_cabang_id_by_code("M"),
"rak_code" => $value2,
"rak_created_at"=> date('Y-m-d H:i:s')
);
$this->db->insert("part_rak",$data);
}
}else{
foreach (explode("|", $cabang) as $key => $value) {
$data = array(
"rak_part_id" => $last,
"rak_cabang_id" => $value,
"rak_code" => $expRak[$key],
"rak_created_at"=> date('Y-m-d H:i:s')
);
$this->db->insert("part_rak",$data);
// $arrLast[]= $this->db->insert_id();
}
}
}
}
