Web developers frequently import CSV(Comma-Separated Values) data. Data migration, bulk data inserts, information presentation from external sources, and other tasks are occasionally necessary. CSV is required in every situation to handle anything. This post will teach us, step-by-step and with an example database, how to import CSV data into MySQL using PHP.
What is CSV?
CSV (comma-separated values) is the most popular file format to store data in plain text for offline uses. Generally, a CSV file is used to import and export data for moving data between programs.
Each line of the CSV file is a data record that consists of one or more fields. When there is needed to add 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 with a single click.
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 student’s data from a CSV file and insert it into the database using PHP and MySQL. According to this script functionality, the user would be able to upload a CSV file of student details, and students data will be inserted into the MySQL database using PHP.
We will do these all things withing three steps:
- Make a student table, where we store all records of students.
- Make a database connection file, which connect MySQL database to PHP.
- Make a file, where we import CSV file and upload those data from CSV file to MySQL database.
Step-1 Create Table
Create a student table in the database. The following SQL creates a student table with some basic fields in the MySQL database.
CREATE TABLE `studentinfo` ( `id` int(11) NOT NULL, `name` varchar(100) NOT NULL, `email` varchar(100) NOT NULL, `contact` varchar(50) NOT NULL, `city` varchar(100) NOT NULL, `status` tinyint(1) NOT NULL, `created_at` datetime NOT NULL, `updated_at` datetime NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
After make student table, We will make database connection file using PHP.
Step-2 Make Database Connection
Make a dbconnect.php file, to connection MySQL database with PHP.
<?php
$host = "localhost"; // Host name.
$username = "root"; //mysql user
$password = ""; //mysql pass
$database = 'phpeasysolutiondemo'; // Database name.
$dbconnect = mysqli_connect($host, $username, $password, $database);
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>
Also read about File Upload In PHP
Step-3 Make PHP file To Import
In this step, we make a PHP file import_csv.php and write code to check email exists in student table or not. If it is exists, then we update that record otherwise, we insert those records in student table.
<?php
include_once 'dbconnect.php';
//$dbconnect
if(isset($_FILES['upload_csv']['name']))
{
$get_file_name = $_FILES['upload_csv']['name'];
$get_file_tmpname = $_FILES['upload_csv']['tmp_name'];
if(is_uploaded_file($_FILES['upload_csv']['tmp_name']))
{
// Open uploaded CSV file with read-only mode
$csvFile = fopen($_FILES['upload_csv']['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];
$contact = $line[2];
$city = $line[3];
$status = $line[4];
// Check whether member already exists in the database with the same email
$prevQuery = "SELECT id FROM studentinfo WHERE email = '".$line[1]."'";
$prevResult = $dbconnect->query($prevQuery);
if($prevResult->num_rows > 0)
{
// Update member data in the database
$result = $dbconnect->query("UPDATE studentinfo SET name = '".$name."', contact = '".$contact."', city = '".$city."', status = ".$status.", updated_at = NOW() WHERE email = '".$email."'");
}
else
{
// Insert member data in the database
$result = $dbconnect->query("INSERT INTO studentinfo (name, email, contact, city, status, created_at, updated_at) VALUES ('".$name."', '".$email."', '".$contact."', '".$city."', ".$status.", NOW(), NOW())");
}
}
// Close opened CSV file
fclose($csvFile);
}
}
?>
<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>Import CSV File</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/css/bootstrap.min.css" rel="stylesheet">
</head>
<body>
<section style="margin-top:100px">
<div class="container-fluid">
<div class="row">
<div class="col-lg-12 col-xlg-12 col-md-6">
<div class="card">
<div class="card-body">
<h4 class="card-title">Upload CSV</h4>
<form class="form-horizontal form-material" action="import_csv.php" method="post" name="frmUploadCsv" id="frmUploadCsv" enctype="multipart/form-data">
<div class="input-group mb-3">
<div class="custom-file">
<input type="file" class="custom-file-input" id="upload_csv" name="upload_csv">
</div>
</div>
<div class="form-group">
<div class="col-sm-12">
<input type="submit" name="upload" class="btn btn-success" value="Upload">
</div>
</div>
</form>
</div>
</div>
</div>
</div>
</div>
</section>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.1.3/dist/js/bootstrap.bundle.min.js"></script>
</body>
</html>
It is simple to use PHP to import CSV data into a MySQL database. You may effectively transfer data from a CSV file to your MySQL database by following the instructions provided in this article. This technique works well for bulk inserts, data migration, and adding data to databases from a variety of sources.