In this tutorial; you will learn how to import/upload CSV file data to MySQL database using PHP script.
How to Import/Upload CSV File Data to MySQL Database using PHP?
Follow the below steps to import/upload CSV file data into MySQL:
Step 1 – Create PHP Project
First of all; visit web server directory and create a php directory; which name demo.
Step 2 – Create Table in Database
Create table into your database; so visit 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
Create a simple HTML named index.php and form it to allow users to choose a CSV file for upload:
<!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</title>
<style>
.custom-file-input.selected:lang(en)::after {
content: "" !important;
}
.custom-file {
overflow: hidden;
}
.custom-file-input {
white-space: nowrap;
}
</style>
</head>
<body>
<div class="container">
<form action="upload.php" method="post" enctype="multipart/form-data">
<div class="input-group">
<div class="custom-file">
<input type="file" class="custom-file-input" id="customFileInput" aria-describedby="customFileInput" name="file">
<label class="custom-file-label" for="customFileInput">Select file</label>
</div>
<div class="input-group-append">
<input type="submit" name="submit" value="Upload" class="btn btn-primary">
</div>
</div>
</form>
</div>
</body>
</html>
Recommended:-File Upload in PHP MySQL database
Step 5 – Create Import Csv File Functionality
Create one file name upload.php and implement code in to to insert all csv file data into MySQL database:
<?php
// include mysql database configuration file
include_once 'db.php';
if (isset($_POST['submit']))
{
// 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
// 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);
header("Location: index.php");
}
else
{
echo "Please select valid file";
}
}
Conclusion
Through this tutorial, you have learned how to upload CSV file and import/insert data into MySQL database using PHP.
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:-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.
You are a life saviour man!!! thanks a lot man/women for these tutorials!!