In this tutorial, we will show you how to import or insert CSV file data into MySQL database using PHP and Ajax without refreshing a web page.
How to Import/Insert CSV File Data into MySQL Database using PHP + Ajax
Here are steps to import/upload/insert CSV file data into MySQL database using PHP and ajax:
Step 1 – Create PHP Project
First, visit your webserver directory and create a php directory.
Step 2 – Create a Table in the Database
Create a table in your database; so visit your PHPMyAdmin and create a table name users with the following fields: name, email, mobile.
CREATE TABLE `users` (
`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_at` datetime NOT NULL,
`updated_at` 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;
Recommended:-Simple Registration Form in PHP with Validation
Step 3 – Create a Database Connection File
Create a file name db.php and update the below code into your file.
<?php
$servername='localhost';
$username='root';
$password='';
$dbname = "my_db";
$conn=mysqli_connect($servername,$username,$password,"$dbname");
if(!$conn){
die('Could not Connect MySql Server:' .mysql_error());
}
?>
Recommended:-How to Add Captcha in PHP Registration Form
Step 4 – Create HTML Form & Implment jQuery Ajax Function
Create HTML Form to Upload CSV
Create a simple HTML upload csv file form and add the following code into your index.php file:
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<title>Import CSV File into MySQL using PHP Ajax</title>
</head>
<body>
<div class="container">
<h2 class="mb-3 mt-3"> Import CSV File into MySQL using PHP </h2>
<form action="upload.php" method="post" enctype="multipart/form-data" id="upload_csv_form">
<div class="form-group">
<label for="exampleFormControlFile1">Please Select File</label>
<input type="file" name="file" class="form-control-file" id="exampleFormControlFile1">
</div>
<div class="form-group">
<input type="submit" name="submit" value="submit" class="btn btn-primary">
</div>
</form>
</div>
</body>
</html>
Add jQuery Library
Add jQuery library into your index.php file:
<script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
Implement Ajax Code for Upload CSV File
Implement ajax code for upload csv file to server:
<script>
$(document).ready(function(){
$('#upload_csv_form').on("submit", function(e){
e.preventDefault(); //form will not submitted
$.ajax({
url:"upload.php",
method:"POST",
data:new FormData(this),
contentType:false, // The content type used when sending data to the server.
cache:false, // To unable request pages to be cached
processData:false, // To send DOMDocument or non processed data file it is set to false
success: function(data){
if(data=='Error1')
{
alert("Invalid File");
}
else if(data == "Error2")
{
alert("Please Select File");
}
else if(data == "Success")
{
alert("CSV file data has been imported");
$('#upload_csv_form')[0].reset();
}
else
{
// $('#employee_table').html(data);
}
}
})
});
});
</script>
Recommended:-File Upload in PHP MySQL database
Step 5 – Create PHP File To Import Csv File Data Into Database
Create one file name upload.php; which is used to read csv file and insert all csv file data into MySQL database. So add the following code into upload.php file:
<?php
// Load the database configuration file
include_once 'db.php';
if(!empty($_FILES["file"]["name"]))
{
// Allowed mime types
$fileMimes = 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'], $fileMimes))
{
// 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 (($getData = fgetcsv($csvFile, 10000, ",")) !== FALSE)
{
// Get row data
$name = $getData[0];
$email = $getData[1];
$phone = $getData[2];
$status = $getData[3];
// If user already exists in the database with the same email
$query = "SELECT id FROM users WHERE email = '" . $getData[1] . "'";
$check = mysqli_query($conn, $query);
if ($check->num_rows > 0)
{
mysqli_query($conn, "UPDATE users SET name = '" . $name . "', phone = '" . $phone . "', status = '" . $status . "', created_at = NOW() WHERE email = '" . $email . "'");
}
else
{
mysqli_query($conn, "INSERT INTO users (name, email, phone, created_at, updated_at, status) VALUES ('" . $name . "', '" . $email . "', '" . $phone . "', NOW(), NOW(), '" . $status . "')");
}
}
// Close opened CSV file
fclose($csvFile);
echo "Success";
}
else
{
echo "Error1";
}
}else{
echo "Error2";
}
Conclusion
In this tutorial; you have learned how to import CSV file data into MySQL database using PHP + ajax.
Recommended PHP Tutorials
Recommended:-jQuery Ajax Form Submit with FormData Example
Recommended:-PHP 8 Pagination with Bootstrap Example
Recommended:-User Registration with Email Verification in PHP
Recommended:-PHP Contact Form with Validation Example
Recommended:-PHP Cookie Set, Get And Delete Example
Recommended:-Insert Update Delete in PHP on Same Page
Recommended:-How to Fetch Data From Database in PHP using Ajax
Recommended:-PHP | Implode and Explode Function
Recommended:-Send Reset Password Link Email PHP
Recommended:-Crop and Save Image Using jQuery Croppie and PHP
Recommended:-PHP Google Places Autocomplete Example
Recommended:-PHP Dropzone File Upload Tutorial Example
If you have any questions or thoughts to share, use the comment form below to reach us.
