MySQL在（PHP）循环内部执行INSERT。 INSERT仅在第一个循环上运行

Question

I have a code to upload xlsx to Mysql tables and the load is done with chunks because the file is too large, so I have to INSERT every chunk. The for loop works good it has been tested with echo statements but the INSERT is only done in the first chunk. Any idea of how I can solve this? This is part of the code:

for ($startRow = 2; $startRow <= $totalRows; $startRow += $chunkSize) {
  $chunkFilter->setRows($startRow,$chunkSize);
  //Load of the data
  $objPHPExcel = $objReader->load($newFilePath);
  $objWorksheet4 = $objPHPExcel -> getSheetByName("MIFReportExport");
  //Getting data of every cell
  $highestRow4 = $objWorksheet4->getHighestRow();
  $highestColumn4 = $objWorksheet4->getHighestColumn();
  $highestColumnIndex4 = PHPExcel_Cell::columnIndexFromString($highestColumn4);
  $rows = array();
  for ($row = 2; $row <= $highestRow4; ++$row) {
     for ($col = 0; $col <= $highestColumnIndex4; ++$col) {
          $rows[$col] = $objWorksheet4->getCellByColumnAndRow($col, $row)->getCalculatedValue();
}
 $result = mysql_query("INSERT INTO fy16 (SerialNumber, BrandedModelName, GenericProductCode, Familia, ProductLineModelFile)VALUES ('".$rows[0]."', '".$rows[1]."', '".$rows[2]."', '".$rows[3]."', '".$rows[4]."', '".$rows[5]."')",$con) or die(mysql_error());

}

}

Thanks in advance.

Answer 1

First of all, you should take a look at PDO, and prepared statements. Here is how to create a MASS insert query

$valuesToInsert = [
    ["firstColumn" => "item1", "secondColumn" => "item1"],
    ["firstColumn" => "item2", "secondColumn" => "item2"],
    ["firstColumn" => "item3", "secondColumn" => "item3"],
];

$valuesTab = array_map(function ($tab) { return "({$tab['firstColumn']}, {$tab['secondColumn']})"; },$valuesToInsert);


$query = "INSERT INTO `tableName` (firstColumn, secondColumn) VALUES ". implode(',', $valuesTab);

Output :

INSERT INTO
  `tableName` (firstColumn, secondColumn)
VALUES
  (item1, item1),(item2, item2),(item3, item3)