mysqli - Getting Excel row data to mysql column in php

Question

I know how I can get data from excel to mysql using php. Please have look at the excel chart below:

I want to input data in below mysql table. From the excel file column D,E,F,G data will insert as row in mysql table & column A,B,C & H will input as column but will follow the no of row as A,B,C & D

I feel what I'm asking is a bit complicated. But please try to give some idea or advise on how to do that. I can't change the excel file, because there are many files to proceed this way.

Answer 1

Basically one row from Excel will insert/update 4 rows in MySQL. May use PhpSpreadsheet for an easy iteration of Excel file.

The logic in in pseudo-php-code will be something like:

For inserts only:

// Statement prepare
$DB = new PDO('mysql:host='.$host.';dbname='.$base., $user, $pass);
$ST = $DB->prepare('insert into table(field1, fiel2, ..) values (:field1, :field2, ..)');

// Excel iteration
while ($Row = $Excel->NextRow()) {
  $ST->execute(['field1' => $Row['A'], .. 'field2' => $Row['D']]);
  $ST->execute(['field1' => $Row['A'], .. 'field2' => $Row['E']]);
  $ST->execute(['field1' => $Row['A'], .. 'field2' => $Row['F']]);
  $ST->execute(['field1' => $Row['A'], .. 'field2' => $Row['G']]);
}

For synchronization:

// Statement prepare
$DB = new PDO('mysql:host='.$host.';dbname='.$base., $user, $pass);
// Update statement
$ST = $DB->prepare('update table set field2 = :field2 where field1 = :field2 and ..)');

// Excel iteration
while ($Row = $Excel->NextRow()) {
  $ST->execute(['field1' => $Row['A'], .. 'field2' => $Row['D']]);
  $ST->execute(['field1' => $Row['A'], .. 'field2' => $Row['E']]);
  $ST->execute(['field1' => $Row['A'], .. 'field2' => $Row['F']]);
  $ST->execute(['field1' => $Row['A'], .. 'field2' => $Row['G']]);
}

Hope you got the idea.