SQL使用PHPExcel插入从excel读取的数据

Question

I am currently working on reading a excel using php and storing those records in mysql.I came across PHPExcel,a bunch of very nice plugin classes which can very easily help to achive it.I tried to search through but did not something similar to my use case.Also,not very good at object oriented PHP and I am short of time in doing this.

    First Name  Last Name   Nationality Gender  Date of Birth   Time of Birth   Date/Time   PHP Coder   Sanity %Age

Above are my sample database columns and First row of my excel sheet.I want to match the column names of my rows before inserting them to database. My code till now gives me a 2 dimensional array in which I get the column names and values.The reason I want to check the column name before inserting is that,my excels can be in any order of the column names. I used exampleReader01 in the package and some SO reference to achieve this.

  $headingsArray = $objWorksheet->rangeToArray('A1:'.$highestColumn.'1',null, true, true, true);
  $headingsArray = $headingsArray[1];
  $r = -1;
  $namedDataArray = array();
  for ($row = 2; $row <= $highestRow; ++$row) {
  $dataRow = $objWorksheet->rangeToArray('A'.$row.':'.$highestColumn.$row,null, true, true, true);
  if ((isset($dataRow[$row]['A'])) && ($dataRow[$row]['A'] > '')) {
       ++$r;
  foreach($headingsArray as $columnKey => $columnHeading){
      $namedDataArray[$r][$columnHeading] = $dataRow[$row][$columnKey];
    }
  }
}

Now I want some help how can I insert this in the right column. My array is like this

Array
(
 [0] => Array
    (
        [First Name] => Mark
        [Last Name] => Baker
        [Nationality] => British
        [Gender] => M
        [Date of Birth] => 19-Dec-60
        [Time of Birth] => 1:30
        [Date/Time] => 22269.0625
        [PHP Coder] => 1
        [Sanity %Age] => 32%
    )

[1] => Array
    (
        [First Name] => Toni
        [Last Name] => Baker
        [Nationality] => British
        [Gender] => F
        [Date of Birth] => 24-Nov-50
        [Time of Birth] => 20:00
        [Date/Time] => 18591.83333
        [PHP Coder] => 
        [Sanity %Age] => 95%
    )

[2] => Array
    (
        [First Name] => Rachel
        [Last Name] => Baker
        [Nationality] => British
        [Gender] => F
        [Date of Birth] => 7-Dec-82
        [Time of Birth] => 0:15
        [Date/Time] => 30292.01042
        [PHP Coder] => 
        [Sanity %Age] => 100%
    )

 )

Hope I am clear.
Thanks for your time.

Answer 1

If I can assume that your Excel column header names never change, why not simply use a mapping array?

$dbMapping = array(
    'col1' => header1,
    'col2' => header2,
     ..
    'colN' => headerN
);

So when you're ready to insert to the database, you iterate through each row with the column header names you already have in your 2D array and pass it into your mapping array i.e. $dbMapping['col1'] and that will get you your header name and you can grab the correct row value.

psuedo

 foreach ($rows as $row) {
     insert into col1, col2, ... colN
       values ($rows[$dbMapping['col1']], $rows[$dbMapping['col2']], ...
 }

Of course it would be in your best interest to use parameterized values.