MySQL do an INSERT inside for(PHP) loop. INSERT only runs on first loop

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.

check what values have on start and on end: $totalRows & $chunkSize Just add echo $totalRows.' '.$chunkSize; before for (&start...) and after end } — Grzegorz
– Grzegorz, Commented Feb 3, 2017 at 12:27

newQuery · Accepted Answer · 2019-08-01 09:14:48Z

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)

edited Aug 1, 2019 at 9:14

answered Feb 3, 2017 at 13:11

newQuery

781 silver badge9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL do an INSERT inside for(PHP) loop. INSERT only runs on first loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related