03-Apr-2023
.
Admin
Hi Dev,
This tutorial is focused on how to import CSV file in PHP MySQL. you'll learn how to import CSV file into MySQL PHP. We will use how to import CSV file into MySQL using PHP. if you have a question about how to import a CSV file into MySQL using PHP then I will give a simple example with a solution.
Now, let's see the article on how to import CSV file into MySQL PHP. it's a simple example of how to import CSV file in PHP MySQL. you can understand the concept of how to import CSV file into MySQL using PHP. if you have a question about how to import a CSV file into MySQL using PHP then I will give a simple example with a solution.
Step 1: Create Table in Database
Create table into your database.
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;
Step 2: Create a Database Connection File
Create a file name db.php and execute the below code into your file.
<?php
$servername='localhost';
$username='root';
$password='';
$dbname = "mydb";
$conn=mysqli_connect($servername,$username,$password,"$dbname");
if(!$conn){
die('Could not Connect MySql Server:' .mysql_error());
}
?>
Step 3: Create HTML Form To Upload CSV File
Create a simple HTML upload csv file form and add the following code into 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 in PHP MySQL</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>
Step 4: 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
// 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";
}
}
?>
I hope it could help you...
#PHP
