Home > Resources > Articles > Programming > PHP > How to Upload and Import Data File into a MySQL Table Programmatically

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>

Suggested Reading

PHP Cookbook: Solutions & Examples

Amazon Reviewer:
Great help for someone thrown into the fray
(Amazon Affiliate Link)