The data management area benefits greatly from the import and export feature. The Import feature allows users to upload and enter various data items into the database. Bulk data can be imported into the database with a single click using the Import tool. The table data list can be downloaded and saved in a file for offline use using the export functionality. Multiple records can be downloaded in a file format using the Export feature.
CSV files are commonly used to import and export data in online applications. The CSV (comma-separated values) file saves data in plain text format and makes it easier to transfer data across programmes. Using PHP and MySQL, the import and export capability may be readily implemented using a CSV file. Importing data from a CSV file into a database and exporting data to a CSV file are both possible with PHP and MySQL. In this article, we’ll show you how to use PHP and MySQL to import and export data from CSV files into a database.
The following functionality will be implemented in the example import and export script.
PHP is used to import data from a CSV file into a MySQL database.
Using PHP and MySQL, export data to CSV.
Make a database table.
A table in the database must be built to store the data of the members. In the MySQL database, the following SQL creates a members table with some basic fields.
CREATE TABLE `members` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`email` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
`phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
`created` datetime NOT NULL,
`modified` datetime NOT NULL,
`status` enum('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CSV (Comma Separated Values)
The CSV file should have the following fields based on the database table structure: Name, Email, Phone, and Status. The CSV file format will be similar to the following screen when importing CSV file data into the database.
The downloaded format will look like this when the data is exported to a CSV file.
Configuring the Database (dbConfig.php)
The database is connected using the dbConfig.php file. According to your MySQL database credentials, specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName).
<?php
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName = "codexworld";
// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
Upload and Download of CSV Files (index.php)
The member’s information is initially shown in an HTML table with import and export options.
The data for existing members is retrieved from the database and shown in a tabular format.
At the top of the list is an Import button.
A CSV file upload form opens when you click the Import button.
The form is submitted to the importData.php file, which imports the data from the CSV file into the database.
formToggle() – When the Import button is clicked, this JavaScript function is used to Show/Hide the CSV upload form.
The status message is collected from the URL and the import status is shown on the web page if the CSV file import request has already been submitted.
At the top of the list is an Export button.
The Export link takes you to the exportData.php file, which allows you to export table data to a CSV file.
<?php
// Load the database configuration file
include_once 'dbConfig.php';
// Get status message
if(!empty($_GET['status'])){
switch($_GET['status']){
case 'succ':
$statusType = 'alert-success';
$statusMsg = 'Members data has been imported successfully.';
break;
case 'err':
$statusType = 'alert-danger';
$statusMsg = 'Some problem occurred, please try again.';
break;
case 'invalid_file':
$statusType = 'alert-danger';
$statusMsg = 'Please upload a valid CSV file.';
break;
default:
$statusType = '';
$statusMsg = '';
}
}
?>
<!-- Display status message -->
<?php if(!empty($statusMsg)){ ?>
<div class="col-xs-12">
<div class="alert <?php echo $statusType; ?>"><?php echo $statusMsg; ?></div>
</div>
<?php } ?>
<div class="row">
<!-- Import & Export link -->
<div class="col-md-12 head">
<div class="float-right">
<a href="javascript:void(0);" class="btn btn-success" onclick="formToggle('importFrm');"><i class="plus"></i> Import</a>
<a href="exportData.php" class="btn btn-primary"><i class="exp"></i> Export</a>
</div>
</div>
<!-- CSV file upload form -->
<div class="col-md-12" id="importFrm" style="display: none;">
<form action="importData.php" method="post" enctype="multipart/form-data">
<input type="file" name="file" />
<input type="submit" class="btn btn-primary" name="importSubmit" value="IMPORT">
</form>
</div>
<!-- Data list table -->
<table class="table table-striped table-bordered">
<thead class="thead-dark">
<tr>
<th>#ID</th>
<th>Name</th>
<th>Email</th>
<th>Phone</th>
<th>Status</th>
</tr>
</thead>
<tbody>
<?php
// Get member rows
$result = $db->query("SELECT * FROM members ORDER BY id DESC");
if($result->num_rows > 0){
while($row = $result->fetch_assoc()){
?>
<tr>
<td><?php echo $row['id']; ?></td>
<td><?php echo $row['name']; ?></td>
<td><?php echo $row['email']; ?></td>
<td><?php echo $row['phone']; ?></td>
<td><?php echo $row['status']; ?></td>
</tr>
<?php } }else{ ?>
<tr><td colspan="5">No member(s) found...</td></tr>
<?php } ?>
</tbody>
</table>
</div>
<!-- Show/hide CSV upload form -->
<script>
function formToggle(ID){
var element = document.getElementById(ID);
if(element.style.display === "none"){
element.style.display = "block";
}else{
element.style.display = "none";
}
}
</script>The HTML Table, Form, and Links in this example code are styled with the Bootstrap 4 package. If you don’t want to use the Bootstrap structure, you may leave it out. Include the Bootstrap library file and the custom CSS file if not otherwise specified (if any).
<!-- Bootstrap library -->
<link rel="stylesheet" href="assets/bootstrap/bootstrap.min.css">
<!-- Stylesheet file -->
<link rel="stylesheet" href="assets/css/style.css">
CSV Data to Database Import (importData.php)
With PHP and MySQL, the importData.php file manages the CSV file upload and data import procedure.
Check to see if the file you submitted is a valid CSV file.
The PHP is uploaded file() method can be used to check the status of a CSV file upload.
Using the PHP fopen() function, open the CSV file.
Using the PHP fgetcsv() function, parse data from a CSV file.
Based on the member’s email, insert or update data into the database.
<?php
// Load the database configuration file
include_once 'dbConfig.php';
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');
// Skip the first line
fgetcsv($csvFile);
// Parse data from CSV file line by line
while(($line = fgetcsv($csvFile)) !== FALSE){
// Get row data
$name = $line[0];
$email = $line[1];
$phone = $line[2];
$status = $line[3];
// Check whether member already exists in the database with the same email
$prevQuery = "SELECT id FROM members WHERE email = '".$line[1]."'";
$prevResult = $db->query($prevQuery);
if($prevResult->num_rows > 0){
// Update member data in the database
$db->query("UPDATE members SET name = '".$name."', phone = '".$phone."', status = '".$status."', modified = NOW() WHERE email = '".$email."'");
}else{
// Insert member data in the database
$db->query("INSERT INTO members (name, email, phone, created, modified, status) VALUES ('".$name."', '".$email."', '".$phone."', NOW(), NOW(), '".$status."')");
}
}
// Close opened CSV file
fclose($csvFile);
$qstring = '?status=succ';
}else{
$qstring = '?status=err';
}
}else{
$qstring = '?status=invalid_file';
}
}
// Redirect to the listing page
header("Location: index.php".$qstring);Data in CSV format (exportData.php)
The exportData.php file manages the PHP and MySQL data export procedure.
Retrieve the data from the database.
Using the PHP fopen() function, create and open a file in writing-only mode.
Set header columns, format as CSV, and use PHP’s fputcsv() method to write it to the opened file.
Data from the database should be output, formatted as CSV, and saved to a file.
Toggle the browser to save data in CSV format in a file.
<?php
// Load the database configuration file
include_once 'dbConfig.php';
$filename = "members_" . date('Y-m-d') . ".csv";
$delimiter = ",";
// Create a file pointer
$f = fopen('php://memory', 'w');
// Set column headers
$fields = array('ID', 'Name', 'Email', 'Phone', 'Created', 'Status');
fputcsv($f, $fields, $delimiter);
// Get records from the database
$result = $db->query("SELECT * FROM members ORDER BY id DESC");
if($result->num_rows > 0){
// Output each row of the data, format line as csv and write to file pointer
while($row = $result->fetch_assoc()){
$lineData = array($row['id'], $row['name'], $row['email'], $row['phone'], $row['created'], $row['status']);
fputcsv($f, $lineData, $delimiter);
}
}
// Move back to beginning of file
fseek($f, 0);
// Set headers to download file rather than displayed
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '";');
// Output all remaining data on a file pointer
fpassthru($f);
// Exit from file
exit();
Note :
Using PHP and MySQL, our example script demonstrates how to simply add import and export functionality to a data list. The export and import functionality is a wonderful way to make the data management area more user-friendly. You may also use PHP and MySQL to enhance our import CSV file and export data to CSV script to meet your specific needs.