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.

what is the status of the question? If one solved it, it should be marked as solved by accepting an answer. If you don't know how, please ask. It's just a matter of ticking the gray checkmark next to an answer till it turns green. — Funk Forty Niner, Sep 27 '17 at 21:17

score 0 · Answer 1 · answered Sep 26 '17 at 17:28

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.

Hi there, thanks for your response. Could you please give me complete code/script, a bit hard for me to understand, as a beginner. — Mehidy Hassan Rudro, Sep 26 '17 at 17:48
Complete working script just for copy/paste? I like charity, but rarely code for it :) i gave you the approach, start thinking. StackOverflow is not a free coding service. — Marcodor, Sep 26 '17 at 19:20

score 0 · Answer 2 · answered Sep 28 '17 at 07:26

Here below the code,its fine now.

$fft1=888;
$fft2=777;
$fft3="";
$fft4=999;

$fft5=100;
$ctnqty=10;
$friendslist = "$fft1,$fft2,$fft3,$fft4";
$id = $fft5;
$friendarray = explode(",", $friendslist);
  $frienduserarray = array();

for ($n = 0; $n < count($friendarray); $n++) {
  $friendidpush = "('".$id."','".$friendarray[$n]."','".$ctnqty."'),";
  array_push($frienduserarray, $friendidpush);
 }
 $query = "INSERT INTO freddyhipment (style, orderno,ctnqty) VALUES ";
 $friendarray = explode(",", $friendslist);

 foreach ($friendarray as $order) {
$query .= "('" . $id . "','" . $order . "','" . $ctnqty . "'),";
}

$query = substr($query, 0, -1); // remove trailing comma

Getting Excel row data to mysql column in php

2 Answers2

Linked