How To Import CSV File In MySQL Using PHP
In this article, we will see how to import CSV files in MySQL using PHP. Here, we will learn about importing CSV files into MySQL database in PHP. For this example, we will use fgetcsv() function. The fgetcsv() function parses a line from an open file, checking for CSV fields.
So, we will create a database after that configure a database connection then we will create an HTML form for importing CSV files to the database.
So, let's see import CSV file in PHP, how to import CSV file into MySQL, import CSV file data into MySQL database using PHP, and how to import a large CSV file in MySQL using PHP.
In this step, we will create a user table. So, add the following code and run the below code.
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,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Now, we will create a config.php file and add the database configuration.
<?php
$host = "localhost";
$user = "root";
$password = "password";
$dbname = "import_csv_example";
$con = mysqli_connect($host, $user, $password, $dbname);
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
In this step, we will create an HTML form for uploading a CSV file. So, add the following code to the index.php file.
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<title>How To Import CSV File In MySQL Using PHP - Techsolutionstuff</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" 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>
In this step, we will create an upload.php file. It is used to read CSV files and insert all CSV file data into MySQL database and also we will include a config.php file. So, add the following code.
<?php
include_once 'config.php';
if (isset($_POST['submit']))
{
$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 selected file is a CSV file or not
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];
// If user already exists in the database with the same email
$query = "SELECT id FROM users WHERE email = '" . $getData[1] . "'";
$check = mysqli_query($con, $query);
if ($check->num_rows > 0)
{
mysqli_query($conn, "UPDATE users SET name = '" . $name . "', created_at = NOW() WHERE email = '" . $email . "'");
}
else
{
mysqli_query($con, "INSERT INTO users (name, email, created_at, updated_at) VALUES ('" . $name . "', '" . $email . "', NOW(), NOW())");
}
}
// Close opened CSV file
fclose($csvFile);
header("Location: index.php");
}
else
{
echo "Please select valid file";
}
}
You might also like:
- Read Also: Laravel 9 Import Large CSV File In Database
- Read Also: How To Export Data From MySQL To CSV In PHP
- Read Also: How To Import CSV File In MySQL Using Node.js
- Read Also: How To Import Excel File Into Database Using Python
