Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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;

}
share|improve this question

1 Answer

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','','') 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.