How to Upload and Import Data File into a MySQL Table Programmatically in PHP

This example PHP code illustrates how to programmatically upload a file through an HTML web form and import the data contained within into a MySQL database table. This is useful in PHP web applications where you have third-party data you need to update in your database - such as package tracking numbers, research data, and similar. For this example to work, please note the following conditions:

  • Data file needs to be plain text-based file and in .CSV file format
  • Data columns are delimited by commas
  • Double-quote used as the text qualifier
  • CSV file should have a header row with column headers named exactly as those in your target MySQL table and in the same order as they appear on your database table.
  • In this example, the expected data column names are: ColumnNameA, ColumnNameB, and ColumnNameC

To use this code, simply copy and paste it into your PHP development environment and configure the variables indicated in the comment lines to your specific environment.


<?php
// This example PHP code helps illustrate how to upload comman-delimited data is a CSV file
// and import them into a MySQL database table.
// The upload file should be text-based, CSV format file, comma-delimited, with double-quote for the text qualifier.
// The CSV file should have a header row with column headers named exactly as those in your target MySQL table 
// and in the same order as they appear on your database table.
// In this example, the expected data column names are: ColumnNameA, ColumnNameB, and ColumnNameC

// BEGIN: Establish a connection to the database
// INSTRUCTION: Fill in the following four variables with your specific connection.

// Server hostname or IP address
$server_hostname = "your.domain.com"; 

// The name of your MySQL database instance
$database_name = "your-dbase-name-here"; 

// The username of your database login credential 
$username = "your-dbase-userid-here";

// The password of your database login credential
$password = "your-dbase-password-here"; 

$link_sqli = mysqli_connect($server_hostname, $username, $password, $database_name);

// If an error occurred while connecting to the database, display the error code and exit.
if (!$link_sqli) {
   echo "Error: Unable to connect to MySQL." . PHP_EOL;
   echo "Debugging error #: " . mysqli_connect_errno() . PHP_EOL;
   echo "Error description: " . mysqli_connect_error() . PHP_EOL;
   exit;
}
// END: Establish a connection to the database

// BEGIN: Define some variable(s)
// INSTRUCTION: Specify your MySQL table name where data is to be imported into in the variable below.
$TargetTableName = 'Sample_Table_Name';
// END: Define some variable(s)

// *** No more configurable options below this point for this code to function on most servers ***
if ((isset($_POST["MM_upload"])) && ($_POST["MM_upload"] == "form_fileupload")) {
  if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
    echo '<ul><li>Target table name: "' . $TargetTableName . '"</li>';									
    echo '<li>File "'. $_FILES['filename']['name'] . '" uploaded successfully.</li>';
    $SourceFilePath = $_FILES['filename']['tmp_name'];
				
    // BEGIN: Delete any existing data in the table before performing the import.
    // Check if the option to empty the target table before importing the data is selected.
    if ($_POST["chkEmptyTable"]==1) {
      echo '<li>Emptying existing data in table . . . ';
	  $DeleteRecordsSQL = "TRUNCATE TABLE `" . $TargetTableName . "`"; 
	  // Empty the table of its current records
	  $SQLResult = mysqli_query($link_sqli, $DeleteRecordsSQL);
	  if (!$SQLResult) {
	    echo 'Error encountered: ' . mysqli_error() . '</li>';
	  } else {
	    echo 'Completed</li>';
	  }
    }
    // END: Delete any existing data in the table before performing the import.
		
    // BEGIN: Process of importing the data from the uploaded file.
    echo '<li>Starting processs to load data to table.</li>';
	
    // The PHP command below (mysqli_options) may be needed if you encounter a PHP Warning of: mysqli_query(): LOAD DATA LOCAL INFILE forbidden 
    // If you encounter this error, use this command by removing the two forward slashes in front of it to uncomment it.
		 
    //mysqli_options($link_sqli, MYSQLI_OPT_LOCAL_INFILE, true);
	
    $SQLLoadQuery = "LOAD DATA LOCAL INFILE '" . $SourceFilePath . "' INTO TABLE `" . $TargetTableName . "`
        FIELDS TERMINATED BY ',' 
	    OPTIONALLY ENCLOSED BY '\"'
		LINES TERMINATED BY '\n' 
		IGNORE 1 LINES 
		(`ColumnNameA`,`ColumnNameB`,`ColumnNameC`)";
		
    $Recordset_Assets = mysqli_query($link_sqli, $SQLLoadQuery) or die(mysqli_error($link_sqli));
    echo '<li>Load data completed.</li>';
    echo '</ul>';
  // END: Process of importing the data from the uploaded file.
  } else {
    echo '<ul><li>Your data file missing. Be sure to click below to select your file to upload.</li></ul>';
  }
}
?>
<!-- Display HTML web form for uploading file -->
<hr/>
<form enctype="multipart/form-data" action="" method="post">
    <label for="input-file-now">Choose the file to upload below:</label>
    <p><input type="file" id="input-file-now" name="filename" /></p>
    <p>
    <label>        
    Empty Table Before Importing Data?
    <input name="chkEmptyTable" type="checkbox" id="chkEmptyTable" value="1" />
    </label>
    <input type="hidden" name="MM_upload" value="form_fileupload" />
    </p>
    <button type="submit" name="submit" id="Submit" value="Upload">Upload and Insert Data</button>
</form>

Recommended Reading


Improve confidence and job performance

Improve productivity and efficiency

Learn more,
earn more

Life-long
investment
To learn more about this topic, we are providing you with recommendations to help you further your knowledge. These are our affiliate links to Amazon where you can purchase them and also explore a variety of other relevant books.