Build a PHP Mysql Importer from CSV file with headers

Question

Im looking to build a php script to import data from a csv file that references the headers.

Here is my script. FYI. i already have a mysql connection, just after the query builder with the headers and values.

Q: Can anyone help me with building a working query, as this one does not work.

if(isset($_POST['importSubmit'])){

    // Allowed mime types
    $csvMimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');

    // Validate whether selected file is a CSV file
    if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)){

        // If the file is uploaded
        if(is_uploaded_file($_FILES['file']['tmp_name'])){

            // Open uploaded CSV file with read-only mode
            $csvFile = fopen($_FILES['file']['tmp_name'], 'r');

            $dbTable = '_test_members';

            // Skip the first line
            //fgetcsv($csvFile);

            // Get first Row as Column Names
            $frow = fgetcsv($csvFile);

            $sqlHeaders = '(';
            $sqlHeaders .= $frow;
            $sqlHeaders .= ')';

            // Parse data from CSV file line by line
            while(($line = fgetcsv($csvFile)) !== FALSE){

                $sqlValues = ' (';
                $sqlValues .= $line;
                $sqlValues .= ') ';

                $db->query("INSERT INTO ".$dbTable." ".$sqlHeaders." VALUES ".$sqlValues."");
            }


            // Close opened CSV file
            fclose($csvFile);

            $qstring = '?status=success';
        }else{
            $qstring = '?status=error';
        }
    }else{
        $qstring = '?status=invalid_file';
    }
}

Any help would be greatly appreciated.

Script has been based on the following tutorial: https://www.codexworld.com/import-csv-file-data-into-mysql-database-php/

Rob

Not sure what your question is, are you getting errors or do you just want some feedback on what you have done? — Nigel Ren, Dec 10 '19 at 16:48
Just realised that i am referencing an array and not a CSV line. Oops. — user3510101, Dec 10 '19 at 16:50
I am getting errors and i feel it is from that fact that i have built the query string with the array and not a csv line with the comma separator. — user3510101, Dec 10 '19 at 16:51
when in this situation I would have used explode() twice on the full file. Mainly because I didn't realize fgetcsv was a function, but secondly you still need to see the count of how many columns you're trying to insert so unexpected errors are not thrown. PHP's count() on an array. The first explode would delimit off new lines 'PHP_EOL' and the second is the line delimiter, typically a comma or | in csv (Idk why I've seen | as the delimiter here but I have). You should look into validation with the PDO driver in PHP for the future. — Richard Tyler Miles, Dec 10 '19 at 17:50

score 0 · Answer 1 · answered Dec 10 '19 at 17:43

A couple of notes:

Please note you can get hacked with the code above. Refer to the owasp guide to avoid this.

Also there's a time and place to use double quotes in php.

Finally, it is cleaner to compose large if blocks in the reverse fashion so you're not scrolling right to get to the 'meat' of the code. ie.

// Validate whether selected file is a CSV file
if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)){
                  .............
} else {
    $qstring = '?status=error';
}

Should be refactored to be:

if(empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $csvMimes)){
    return '?status=error';
}

I do understand that this isn't in context of a function, but it may be worth the refactor.

Best of luck, Tyler

Build a PHP Mysql Importer from CSV file with headers

1 Answers1