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

 Back to Top