Tutorial: How to upload a CSV file into a MySQL database with PHP and Ajax Creating a *NEW* Table

Maybe my Google searching skills aren’t as good as I thought; I had trouble finding this exact solution!

As indicated by the headline, these were my objectives:

• Allow the user to upload a CSV file to a MySQL database, with PHP. Bonus points for Ajax.
• The number of columns are unknown, so I needed to dynamically create a new table. I could not instruct the user “Make sure the first column is a name and the second column is an address, etc”. I therefore could not rely on knowing which columns (and the order) would go into an existing database table with predefined rows.
• I found some solutions that read in a file and then looped through every row individually, thereby running several INSERT statements into my table. This seems like a waste of database resources, so I wanted to avoid it.

The Easy Part: HTML

<form action="#" method="POST" enctype="multipart/form-data"> 
 <input type="file" id="file" name="file">
 <input type="submit" name="submit" value="Upload">
</form>

• Of course you’ll have to update the ACTION attribute
• You’ll probably still want a METHOD of “POST”
• The ENCTYPE allows you to upload files. Using JavaScript (below) this probably isn’t needed

Another Easy Part: JavaScript

I wanted to have the user upload the page via Ajax. I’m going to use jQuery.

First, change the submit button around and call a function:

<form action="#" method="POST" enctype="multipart/form-data">
 <input type="file" id="file" name="file"> 
 <button type="button" id="submit" name="submit" onClick="uploadCSV();">Upload</button>
</form>

And here’s the JavaScript function:

function uploadCSV() {

 var formData = new FormData();
  formData.append('file', $('#file')[0].files[0]);
  formData.append('uploadData', '1');

 $.ajax({

  url : '/controllers/index.php',
  type : 'POST',
  data : formData,
  processData: false,  // tell jQuery not to process the data
  contentType: false,  // tell jQuery not to set contentType
  success : function(data) {

   var results = $.trim(data);
   console.log(results);

  },
  error: function(request, status, error){
   alert('Something went wrong; please try again');
  }
 });
}

The additional parameter uploadData is something I use to tell which code block in my controller to run (next section).

UX upgrades: indicate when the file is uploading and allow for drag-and-drop

PHP: Part 1, the Controller

Adding in the parameter uploadData tells which part of my PHP Controller to run. Here it is:

<?php
if (array_key_exists('uploadData', $_POST)) {

 if (empty($_FILES)) {
  print "No file found";
 } else {
  
  if (!is_uploaded_file($_FILES['file']['tmp_name'])) {
   print "File not uploaded";
  } else {

   $fileName = str_replace("\\", "/", $_FILES['file']['tmp_name']);

   $removeTemp = userUploadsRemoveTable($db);

   if (!$removeTemp) {
    return false;
   } else {
    $uploaded = userUploadsCreateTable($db, $fileName);

    if (!$uploaded) {
     return false;
    } else {
     return true;
    }
   }

  } // is_uploaded_file

 } // if(!empty($_FILES))
}
?>

Explanation:

• empty($_FILES) looks to see if the user included a file when they submitted the form
• is_uploaded_file() checks to see if the file was actually uploaded and is accessible
• Get the file name of what was just uploaded
• Then we have some more PHP functions…

PHP: Part 2, Put the File into a MySQL Database

The previous section references the following functions which interact with the database.

Assumption: you have your own way to connect to the database. I’m passing $db into my functions.

function userUploadsRemoveTable($db) {

 $sql = "DROP TABLE IF EXISTS myTable"; 
 
 return $db->runSQL($sql);
}

function userUploadsCreateTable($db, $fileName) {

 // Can't seem to load in a table dynamically.
 //  Create the table:
 //  first step is to read in ONLY the first row of the CSV file
 //   THEN will load in the data
 
 $handle = fopen($fileName, "r");

 $row = 1; 
 $columns = [];

 while (($data = fgetcsv($handle, 1000, ",")) !== FALSE AND $row==1) {
    $columns = $data;
    $row++; 
 }

 $sqlCreateTable = "CREATE TABLE IF NOT EXISTS myTable 
               (".implode(" VARCHAR(255) NOT NULL, ", $columns). " 
                VARCHAR(255) NOT NULL);";

 $sqlLoadTable = "
    LOAD DATA LOCAL INFILE '$fileName'
    INTO TABLE myTable
    FIELDS TERMINATED BY ','
    ENCLOSED BY '\"' LINES
    TERMINATED BY '\n'
    IGNORE 1 LINES";

 $newTableCreated = $db->runSQL($sqlCreateTable);

 if (!$newTableCreated) {
  print "Error Uploading Data (a)";
  return false;
 } else {
  
  $appendData = $db->runSQL($sqlLoadTable);

  if ($appendData) {
   print "File uploaded";
   return true;
  } else {
   print "Error Uploading Data (b)";
   return false;
  }

 }
}

Explanation

• First drop the existing table. This assumes there’s one table in your database that is affected. In my app, I dynamically modified the name of the table to have a unique identifier for each user who uploads data
• CREATE a new database table, dynamically updating the structure (columns) based on the input CSV file
• Import the data, using LOAD DATA LOCAL INFILE

Security Considerations

Typically you want to limit database permissions. In most cases, my web apps only need SELECT / INSERT / UPDATE / DELETE permissions. I do not enable permissions for CREATE nor DROP tables.

With this solution, I obviously need these additional privileges. What I did though, was I created a separate database for the user file uploads. This separate database user has elevated privileges.

Then I have a screen where the user selects which fields they want, and that runs the INSERT statement into my “main” database.

Final thoughts

• I had trouble getting this to work on my local computer (MAMP). I ended up working on my remote server.
• I had trouble with a “temporary” table (as I’ve used on MS SQL Server). I’m pretty sure this concept does exist in MySQL though.