In this tutorial, you will learn how to Import CSV File Data Into MySQL Database using PHP. The tutorial aims to provide the IT/CS Students and new programmers with a reference for reading CSV Files in PHP and Import Data into the Database. Here, the step-by-step tutorial with snippets is provided and a sample program source code zip that demonstrates the tutorial objectives is free to download.
How to Import Data From CSV to MySQL Database using PHP?
PHP has a built-in method that allows the server to read files such as CSV. In order to import the data from CSV to MySQL Database, you can simply read the data from the CSV File and create a MySQL Statement for inserting the data into the database.
Here are the following PHP methods that are useful for implementing an import data from CSV to Database:
- fopen()
- fgetcsv()
- fclose()
Steps of implementing an import data from CSV to MySQL DB
- Create a form that contains a file input for choosing the CSV file.
- Upon submission, read the selected/chosen CSV file and list the row data.
- Create a MySQL Insert Statement w/ the values listed in the CSV file.
- Execute the MySQL Query for the Insertion of data into the database.
Example
Here are the snippets that result in a simple program that demonstrate the steps that I provided above.
Database Schema
Let us use the following MySQL Database Schema for this example web application. The schema creates a new database named dummy_db. It also creates one table named members that have id, name, phone, email, and created_at columns.
--
-- Database: `dummy_db`
--
-- --------------------------------------------------------
--
-- Table structure for table `members`
--
Database Connection
Next, create a PHP file and save it as db-connect.php. This file contains the following script which handles the connection of the application and database.
<?php
// DB Host
$host = "localhost";
// DB Username
$uname = "root";
// DB Password
$password = "";
// DB Name
$dbname = "dummy_db";
$conn = new mysqli($host, $uname, $password, $dbname);
if(!$conn){
die("Database Connection Failed."); }
?>
Interface
Next, let's create another PHP file and save it as index.php. The file contains the following script which is a combined PHP and HTML code. As result, the page has a form that contains a file input that only accepts a CSV file. It also contains a table that displays the inserted data from the database.
<?php session_start(); ?>
<!DOCTYPE html>
<meta http-equiv="X-UA-Compatible" content="IE=edge"> <meta name="viewport" content="width=device-width, initial-scale=1.0"> <link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/css/all.min.css" integrity="sha512-xh6O/CkQoPOWDdYTDqeRdPCVd1SpvCA9XXcUnZS2FmJNp1coAFzvtCN9BmamE+4aHK8yyUHUSCcJHgXloTyT2A==" crossorigin="anonymous" referrerpolicy="no-referrer" /> <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" integrity="sha384-Zenh87qX5JnK2Jl0vWa8Ck2rdkQ2Bzep5IDxbcnCeuOxjzrPF/et3URy9Bv1WTRi" crossorigin="anonymous">
<script src="https://cdnjs.cloudflare.com/ajax/libs/font-awesome/6.2.0/js/all.min.js" integrity="sha512-naukR7I+Nk6gp7p5TMA4ycgfxaZBJ7MO5iC3Fp6ySQyKFHOGfpkSZkYVWV5R7u7cfAicxanwYQ5D1e17EfJcMA==" crossorigin="anonymous" referrerpolicy="no-referrer"></script> <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js" integrity="sha384-OERcA2EqjJCMA+/3y+gxIOqMEjwtxJY7qPCqsdltbNJuaOe923+mo//f6V8Qbsw3" crossorigin="anonymous"></script> <body style="background:#9CB4CC"> <nav class="navbar navbar-expand-lg navbar-dark" style="background:#06283D">
<a class="navbar-brand" href="./">Import CSV Data to MySQL in PHP
</a> <a href="https://sourcecodester.com" class="text-light fw-bolder h6 text-decoration-none" target="_blank">SourceCodester
</a> </nav>
<div class="container-fluid px-5 pb-2 pt-5"> <div class="col-lg-6 col-md-8 col-sm-12 mx-auto"> <h3 class="text-center text-light">Importing CSV Data to MySQL Database in PHP
</h3> <?php if(isset($_SESSION['status']) && $_SESSION['status']== "success"): ?>
<div class="alert alert-success rounded-0 mb-3"> <?= $_SESSION['message'] ?>
<?php unset($_SESSION['status']);unset($_SESSION['message']) ?>
<?php endif; ?>
<?php if(isset($_SESSION['status']) && $_SESSION['status'] == "error"): ?>
<div class="alert alert-danger rounded-0 mb-3"> <?= $_SESSION['message'] ?>
<?php unset($_SESSION['status']);unset($_SESSION['message']) ?>
<?php endif; ?>
<div class="card rounded-0 mb-3"> <div class="card-header rounded-0"> <div class="card-title"><b>Import Data From CSV
</b></div> <div class="card-body rounded-0"> <div class="container-fluid"> <form action="import_csv.php" id="import-form" method="POST" enctype="multipart/form-data"> <label for="fileData" class="form-label">Browse CSV Data
</label> <input class="form-control" type="file" accept=".csv" name="fileData" id="fileData" required> <div class="card-footer py-1"> <div class="text-center"> <button class="btn btn-primary rounded-pill col-lg-5 col-md-6 col-sm-12 col-xs-12" form="import-form">Import
</button> <div class="card my-2 rounded-0"> <div class="card-header rounded-0"> <div class="card-title"><b>Member List
</b></div> <div class="card-body rounded-0"> <div class="container-fluid"> <div class="table-responsive"> <table class="table table-hovered table-striped table-bordered"> <tr class="bg-gradient bg-primary text-white"> <th class="text-center">#
</th> <th class="text-center">Name
</th> <th class="text-center">Phone/Fax
</th> <th class="text-center">Email
</th> <?php
include_once('db-connect.php');
$members_sql = "SELECT * FROM `members` order by id ASC";
$members_qry = $conn->query($members_sql);
if($members_qry->num_rows > 0):
while($row = $members_qry->fetch_assoc()):
?>
<th class="text-center"><?= $row['id'] ?></th> <td><?= $row['name'] ?></td> <td><?= $row['phone'] ?></td> <td><?= $row['email'] ?></td> <?php endwhile; ?>
<?php else: ?>
<th class="text-center" colspan="4">No data on the database yet.
</th> <?php endif; ?>
<?php $conn->close() ?>
PHP API
Next, we will create the PHP API that contains the scripts for reading the selected CSV File, listing the data to insert, and inserting the data into the database. Save the following snippet as import_csv.php. This file will be executed upon form submission.
<?php
include_once('db-connect.php');
/**
* Check if CSV File has been sent successfully otherwise return error
*/
if(isset($_FILES['fileData']) && !empty($_FILES['fileData']['tmp_name'])){
// Read CSV File
$csv_file = fopen($_FILES['fileData']['tmp_name'], "r");
// Row Iteration
$rowCount = 0;
//Data to insert for batch insertion
$data = [];
// Read CSV Data by row
while(($row = fgetcsv($csv_file, 1000, ",")) !== FALSE){ if($rowCount > 0){
//Sanitizing Data
$name = addslashes($conn->real_escape_string($row[0])); $phone = addslashes($conn->real_escape_string($row[1])); $email = addslashes($conn->real_escape_string($row[2]));
// Add Row data to insert value
$data[] = "('{$name}', '{$phone}', '{$email}')";
}
$rowCount++;
}
// Close the CSV File
/**
* Check if there's a data to insert otherwise return error
*/
// Convert Data values from array to string w/ comma seperator
$insert_values = implode(", ", $data);
//MySQL INSERT Statement
$insert_sql = "INSERT INTO `members` (`name`, `phone`, `email`) VALUES {$insert_values}";
// Execute Insertion
$insert = $conn->query($insert_sql);
if($insert){
// Data Insertion is successful
$_SESSION['status'] = 'success';
$_SESSION['message'] = 'Data has been imported succesfully.';
}else{
// Data Insertion has failed
$_SESSION['status'] = 'error';
$_SESSION['message'] = 'Import Failed! Error: '. $conn->error;
}
}else{
$_SESSION['status'] = 'error';
$_SESSION['message'] = 'CSV File Data is empty.';
}
}else{
$_SESSION['status'] = 'error';
$_SESSION['message'] = 'CSV File Data is missing.';
}
$conn->close();
?>
CSV File
Lastly, let's create a CSV file that we will use for importing. You can create your own example CSV file but make sure to follow the following format of data with the same column order. Take note that the script for importing the source code skips the first row in the reading. The CSV file only requires the name, phone, and email columns.
name,phone,email
Silas Leonard,(515) 433-7885,[email protected]
Adele Moody,1-451-357-4702,[email protected]
Colton Turner,1-172-865-3080,[email protected]
Carolyn Kramer,1-237-334-8685,[email protected]
Lionel Vazquez,1-226-236-8055,[email protected]
Naomi Workman,(320) 551-7707,[email protected]
Rhona Vega,1-683-547-2283,[email protected]
Martena Hardin,1-426-208-8881,[email protected]
Galena Collier,1-711-578-8491,[email protected]
Demetrius Nichols,1-302-469-9766,[email protected]
There you go! You can now test the application on your end and see if it works as we planned. I also provided the source code zip file that I created for this tutorial with a sample CSV File. You can download it by clicking the Download Button located below this article's content.
Snapshot
Here is a snapshot of the application page interface.
That's it! That's the end of this tutorial. I hope this Importing CSV File Data into MySQL Database using PHP Tutorial helps you with what you are looking for and that you'll find this useful for your current and future PHP Projects.
Explore more on this website for more Tutorials and Free Source Codes.
Happy Coding:)
