A CSV (comma-separated values) file stores the tabular data in plain text format. Basically, the CSV file format is used to import to or export from the table data. Each line of the CSV file is a data record that consists of one or more fields. When there is needed to add the huge data into the MySQL database, it will very time-consuming to add data one by one. In that situation, the import feature helps to insert a bunch of data in the database on a single click.
Bulk Import is a very useful feature to add multiple records in the database without insert manually. Using the CSV file you can store the data and import the CSV file data into the database at once using PHP and MySQL. Import CSV into MySQL helps to save the user time and avoid repetitive work. In this tutorial, we will show you how to upload CSV file and import data from CSV file to MySQL database using PHP.
In the example script, we will import the member’s data from CSV file and insert into the database using PHP and MySQL. According to this script functionality, the user would be able to upload a CSV file of members details and members data will be inserted into the MySQL database using PHP.
Create Database Table
To store the member’s data, a table needs to be created in the database. The following SQL creates a members table with some basic fields in the MySQL database.
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 File Format
Based on the database table structure, the CSV file should have these fields – Name, Email, Phone, and Status. To import the data from CSV file, the format will similar the following screen.
Database Configuration (dbConfig.php)
The dbConfig.php is used to connect the database. Specify the database host ($dbHost), username ($dbUsername), password ($dbPassword), and name ($dbName) as per your MySQL database credentials.
<?php
// Database configuration
$dbHost = "localhost";
$dbUsername = "root";
$dbPassword = "root";
$dbName = "codehunger";
// Create database connection
$db = new mysqli($dbHost, $dbUsername, $dbPassword, $dbName);
// Check connection
if ($db->connect_error) {
die("Connection failed: " . $db->connect_error);
}
CSV File Upload (index.php)
Initially, the members data is listed with CSV file import option.
- Existing members data are fetched from the database and listed on the webpage.
- An Import button is placed at the top of the list.
- By clicking the Import button, an HTML form is appeared to select and upload a CSV file.
- On submission, the form is submitted to the
importData.phpfile for importing the CSV data to the database. - formToggle() – It is a JavaScript function that helps to Show/Hide the CSV upload form. This function is triggered on click event of the Import button.
If the form is already submitted,
- The status message is retrieved from the URL and the import status is displayed on the web page.
<?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 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>
</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 Bootstrap library is used to styling the Table, Form, and Buttons. So, include the Bootstrap 4 library file and custom stylesheet file (if any). If you don’t want to use Bootstrap structure, omit to include this library file.
<!-- Bootstrap library --> <link rel="stylesheet" href="assets/bootstrap/bootstrap.min.css"> <!-- Stylesheet file --> <link rel="stylesheet" href="assets/css/style.css">
Import CSV Data to Database (importData.php)
The importData.php file handles the file upload and CSV data import process using PHP and MySQL.
- Validate the posted file whether it is a valid .csv file.
- Check whether the CSV file is uploaded using is_uploaded_file() function.
- Open the CSV file in read-only mode using fopen() function.
- Read and Parse data from the opened CSV file using fgetcsv() function.
- Retrieve the CSV data line by line.
- Insert/Update member data in the database based on the email address.
- Redirect to the listing page with import status code.
<?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);
