Importing csv file into database using column info

Question

Below is a sample table I've been working on,

table1

╔════╦══════════════╦═══════════╗════════════════════╗ 
║ id ║  file_id     ║fieldname  ║ from_column_number ║       
╠════╬══════════════╬═══════════╣════════════════════╬ 
║  1 ║ 5566533      ║  mid      ║          4         ║
║  2 ║ 5566533      ║  old      ║          2         ║
║  3 ║ 5566533      ║  fossil   ║          1         ║
║  4 ║ 3232534      ║  old      ║          4         ║
║  5 ║ 3232534      ║  mid      ║          3         ║
║  6 ║ 3232534      ║  new      ║          2         ║    
║  7 ║ 3232534      ║  fossil   ║          1         ║
╚════╩══════════════╩═══════════╝════════════════════╝

I have CSV file's with their Id's in the column file_id ,their header names and their order as they appear in the files in column's fieldname and from_column_number respectively. ie,in the above case I've shown two file's with file_id 5566533 and 3232534.

They have their header order as

5566533:

 fossil old mid 

3232534:

 fossil new mid old

I need to load the files into the below table,rearranging the way it loads for each file using the above information

For example,The file having the id 5566533 has the field mid in its fourth position in the above case.

table2

╔════╦══════════════╦═══════════╗════════════════════╗ 
║ id ║  mid         ║old        ║ fossil             ║       
╠════╬══════════════╬═══════════╣════════════════════╬ 
║ 1  ║ 422342343    ║  12222    ║        342342      ║
║ 2  ║ 234234       ║  43234    ║        3243        ║
║ 3  ║ 345435355445 ║  234234   ║        32432       ║
║ 4  ║ 3455544534   ║  2343245  ║        4234        ║
║ 5  ║ 345345544454 ║  432234   ║        324545      ║
║ 6  ║ 554345345    ║  34243    ║        453242      ║    
║ 7  ║ 77w345544533 ║  34234455 ║        5245345     ║
╚════╩══════════════╩═══════════╝════════════════════╝ 

I can use only plain SQL and a bit of PHP for this purpose.g

Answer 1

I think I figured out what you are asking. First, you could convert your field mapping table into an array. I've done the following manually but it should demonstrate what you are trying to do:

// Load some sample data regarding input file field ordering
// ---------------------------------------------------------
$csvinfo = array();

$csvinfo[5566533]['mid']    = 4;
$csvinfo[5566533]['old']    = 2;
$csvinfo[5566533]['fossil'] = 1;

$csvinfo[3232534]['old']    = 4;
$csvinfo[3232534]['mid']    = 3;
$csvinfo[3232534]['new']    = 2;
$csvinfo[3232534]['fossil'] = 1;

With the field mapping information in place you can walk through the list of files, load them, and then extract the data you care about using the field mapping information:

// Process each of the file names loaded into csvinfo
// --------------------------------------------------
echo "Processing input files<br>\n";
foreach ($csvinfo as $fileid => $fldmap)
{
  $raw = @file_get_contents("$fileid.csv");
  if ( !strlen($raw) )
    die("ERROR: Unable to load $fileid.csv contents!\n");
  $data = explode("\n",$raw);

  $i=0;
  echo "<br>\n";
  echo "Importing from $fileid.csv<br>\n";

  foreach ($data as $line)
  {
    // Skip header line
    // ----------------
    if ( !$i++ || !strlen(trim($line)) )
      continue;

    $flds = explode(',',$line);
    $midval = trim($flds[$fldmap['mid']-1]);
    $oldval = trim($flds[$fldmap['old']-1]);
    $fosval = trim($flds[$fldmap['fossil']-1]);

    // Write fields to table here
    // --------------------------
    echo "... line " . ($i-1) . " $line with fossil $fosval  mid $midval old $oldval<br>\n";
    ;
  }

  echo ($i-1) . " lines imported from $fileid.csv<br>\n";
}

The code above will generates the following debug information:

Processing input files

Importing from 5566533.csv
... line 1 f812, o998, b234, m1892 with fossil f812 mid m1892 old o998
... line 2 f915, o123, b454, m9817 with fossil f915 mid m9817 old o123
2 lines imported from 5566533.csv

Importing from 3232534.csv
... line 1 f179, n723, m886, o912 with fossil f179 mid m886 old o912
... line 2 f791, n237, m868, o129 with fossil f791 mid m868 old o129
... line 3 f917, n372, m688, o291 with fossil f917 mid m688 old o291
3 lines imported from 3232534.csv

Here's the 3232534.csv file (manually created with junk data):

fossil, new, mid, old
f179, n723, m886, o912
f791, n237, m868, o129
f917, n372, m688, o291

I'm not going to suggest the code is particularly robust or anything like that. However, it's a starting point if I've interpreted your question correctly.