PHP - Displaying one array based on another array

Question

Ultimately I'm just trying to build a page that imports a csv file into a mysql db.

I've imported the csv successfully. And I've set up the page so the user selects from a dropdown what field from the database corresponds to what section in the csv.

E.g.

student_firstname: [First_Name] 
student_surname: [Last_Name] 
student_address1: [Street_1] 
etc.

So afterwards I've built 2 arrays, the first one tells me the mysql field and the csv heading, the second is the raw data from the csv file:

    Array
(
    [firstname] => First_Name
    [surname] => Last_Name
    [address1] => Street_1
    [address2] => Street_2
    [address3] => City
    [address4] => County
    [postcode] => Postcode
    [telephone] => Mobile_Phone
    [notes] => None
)
Array
(
    [0] => Array
        (
            [Username] => 
            [Last_Name] => Jacobs
            [First_Name] => Adam
            [Email] => 
            [Password] => password
            [Student_Id] => 
            [Middle_Name] => 
            [Job_Title] => 
            [Department] => 
            [Company] => 
            [Street_1] => 177 The Road
            [Street_2] => 
            [City] => Exeter
            [County] => 
            [Postcode] => EX1 1XX
            [Country] => 
            [Work_Phone] => 
            [Home_Phone] => 
            [Work_Fax] => 
            [Mobile_Phone] => 01111 1112222
            [Website] => 
            [Role] => Exeter City
        )

    [1] => Array
        (
            [Username] => 
            [Last_Name] => Miller
            [First_Name] => Adam
            [Email] => 
            [Password] => password
            [Student_Id] => 
            [Middle_Name] => 
            [Job_Title] => 
            [Department] => 
            [Company] => 
            [Street_1] => The White
            [Street_2] => 21A Some House
            [City] => Creditery
            [County] => 
            [Postcode] => EX1 1AA
            [Country] => 
            [Work_Phone] => 
            [Home_Phone] => 
                [Work_Fax] => 
                [Mobile_Phone] => 07111 112222
                [Website] => 
                [Role] => Exeter City
            )
    )

All I'm trying to do now is to create a string that inserts only the values from the second array into the sql fields defined in the first array, so I can add into a mySQL INSERT statement.

E.g.

    INSERT INTO students (student_firstname, student_surname, student_address1, student_address2, student_address3, student_address4, student_postcode, student_telephone, student_notes, student_added) VALUES 
    ('Adam','Jacobs','177 The Road','','Exeter','','EX1 1XX','01111 1112222','',''),
('Adam','Millar','The White','21A Some House','Creditory','','EX1 1AA','07111 11222','','')

My current code is below to illustrate that I am definitely got some working out, but it's turning into a mess now

if (isset($_POST['file_upload'])){
    $uploaded = 1;
    $lines = explode("\n", file_get_contents($_FILES["csvfile"]["tmp_name"]));
$head = str_replace(" ","_",str_getcsv(array_shift($lines)));

$array = array();
foreach ($lines as $line) {
        $row = array_pad(str_getcsv($line), count($head), '');
    $array[] = array_combine(str_replace(" ","_",$head), $row);
}


    //echo '<pre>';
    //print_r($array);
    //echo '</pre>';
    session_start();
  $_SESSION['csvimport'] = $array;
}
if (isset($_POST['submitrecords'])){
    $fields = array(
    'firstname' => $_POST['student_firstname'],
    'surname' => $_POST['student_surname'],
    'address1' => $_POST['student_address1'],
    'address2' => $_POST['student_address2'],
    'address3' => $_POST['student_address3'],
    'address4' => $_POST['student_address4'],
    'postcode' => $_POST['student_postcode'],
    'telephone' => $_POST['student_telephone'],
    'notes' => $_POST['student_notes']);

    $array = $_SESSION['csvimport'];
    echo '<pre>';
    print_r($fields);
    print_r($array);
    echo '</pre>';

    foreach ($fields as $dbfield){
        foreach(array_keys($array[0]) as $value){
        if ($dbfield == $value){
            foreach($array as $key => $value2){
                $snippet .= ",('" . $value2[$dbfield] . "','" . $studentid[0] . "','" . $studentid[1] . "')";
            }

        }



    }
}
    $snippet = substr($snippet,1);

    $insertSQL = "INSERT INTO students (student_firstname, student_surname, student_address1, student_address2, student_address3, student_address4, student_postcode, student_telephone, student_notes, student_added) VALUES $snippet";


    echo $insertSQL;

}

Answer 1

I dropped "notes" from your original array, because it didn't contain a field from the CSV file, but it gets added again later:

$fnames = array (
    'firstname' => 'First_Name',
    'surname' => 'Last_Name',
    'address1' => 'Street_1',
    'address2' => 'Street_2',
    'address3' => 'City',
    'address4' => 'County',
    'postcode' => 'Postcode',
    'telephone' => 'Mobile_Phone');

After naming your second array $csv, the following code generates your database query:

$db = array();
$db_cols = array_keys($fnames);
foreach ($csv as $key => $value){
    $insx = array_intersect_key($value, array_flip($fnames));
    $db[$key] = array_combine($db_cols, $insx);
    $db[$key]['notes'] = '';
    $db[$key]['added'] = '';
}
if (count($db) > 0){
    $query = "INSERT INTO students (student_";
    $query .= implode(', student_', array_keys($db[0])).") VALUES \n";
    foreach ($db as $key => $value){
        $query .= "('".implode("','", $value)."'),\n";
    }
    $query = rtrim($query, ",\n");
    echo $query;
}

Output:

INSERT INTO students (student_firstname, student_surname, student_address1, student_address2, student_address3, student_address4, student_postcode, student_telephone, student_notes, student_added) VALUES 
('Jacobs','Adam','177 The Road','','Exeter','','EX1 1XX','01111 1112222','',''),
('Miller','Adam','The White','21A Some House','Creditery','','EX1 1AA','07111 112222','','')