Import a CSV into MySQL Database in PHP
Programmatically importing a CSV file to the database is a feature that makes the data entry job faster and easier. Learn how to import data from a CSV file into a database in simple steps.
CSV import in web applications is a useful feature for bulk data import, like employees, products, and other data records. We will use a sample database table, upload a sample CSV file with employee records, and upload it via an HTML form. The uploaded file will be processed using PHP code to demonstrate how to read a CSV file in PHP with file error check and file format check on the server side.
What is a CSV File?
CSV is an abbreviation of Comma Separated Values. As the file extension indicates, it is a text file that contains table data values separated by commas. Each row in this file is a record containing field values separated by commas.
Why Import CSV to Database?
CSV import becomes a useful feature when you want to add a large number of records to the database. Adding a large number of records manually is not only frustrating and time consuming but it also opens doors for mistakes and errors. With an automated CSV upload and import in PHP feature, all entries can be added to the database in a much faster, safer and easier way. We can also Export MySQL Data to CSV in PHP, which we will cover in a future post. This post explains how to import CSV data into a MySQL database table in PHP with a working example.
Files we are going to need are:
- constants.php: Holds constant values for our database connection.
- index.php: Contains the form to upload a CSV file and code to import it into the database table.
- style.css: Contains CSS styles for our form and table that show the employee records from the table.
Add Constants for Database Connection
Our database constants configuration file constants.php, contains database connection constants that we will be using in our index.php file.
constants.php
<?php
define('DB_HOST', 'DATABASE_HOST'); // Your database host
define('DB_NAME', 'DATABASE_NAME'); // Your database name
define('DB_USER', 'DATABASE_USERNAME'); // Username for database
define('DB_PASSWORD', 'DATABASE_PASSWORD'); // Password for database
Import CSV to Database in PHP and Display Records in a Table
We are now going to import CSV to a MySQL database and show the imported records as an HTML table. This is what this file is basically doing:
- Establish a database connection and assign it to a variable.
- Prepare a form with a file type input field that will be used to upload a CSV file.
- When the form is submitted, check that a file was uploaded without any errors.
- Proceed to our next step and use
pathinfo()to get the extension of the uploaded file to confirm it was a CSV file. - Then open the uploaded file with the file function
fopen()with read permission. - Then read the CSV file until it hits the end of the file and use
fgetcsv()to fetch the row in the current iteration. We prepare a$dataarray variable with values to insert into our employees' table.
After the data has been imported, we run another query to get records from the database table and show them in a table.
index.php
<?php
include 'constants.php';
$db_connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) or die(mysqli_connect_error());
?>
<!DOCTYPE html>
<html>
<head>
<title>Import CSV into MySQL Database in PHP - Demo</title>
<meta content="text/html; charset=UTF-8" http-equiv="Content-Type"/>
<meta content="width=device-width, initial-scale=1, maximum-scale=1" name="viewport"/>
<link rel="stylesheet" href="css/style.css"/>
</head>
<body>
<section class="section py-4">
<div class="container">
<div class="form-wrapper">
<form name="csv-import" method="POST" enctype="multipart/form-data">
<div class="row">
<div class="col-4">
<input type="file" name="csv_file" class="form-control"/>
</div>
<div class="col-4">
<input type="checkbox" name="first_row_header" id="first_row_header"/>
<label for="first_row_header">First Row is Header</label>
</div>
<div class="col-4 text-right">
<button type="submit" class="btn btn-green">Import</button>
</div>
</div>
</form>
</div>
<div class="table-wrapper">
<?php
// Check if file was uploaded & there were no errors
if ($_FILES && $_FILES['csv_file']['error'] === 0) {
$extension = pathinfo($_FILES['csv_file']['name'], PATHINFO_EXTENSION);
// Check if extension is CSV then proceed to import
if ($extension === 'csv') {
// Open file for reading
$file = fopen($_FILES['csv_file']['tmp_name'], 'r');
$i = 1;
// Loop through all rows of file and insert them to database table
while (!feof($file)) {
// Get current row as recordset
$row = fgetcsv($file);
if (isset($_POST['first_row_header']) && $i === 1) {
$i++;
continue;
}
if (!empty($row)) {
$data = [];
$data['full_name'] = trim($row[0]);
$data['email'] = trim($row[1]);
$data['gender'] = trim($row[2]);
$data['designation'] = trim($row[3]);
$sql = sprintf('INSERT INTO employees (%s) VALUES (%s)',
implode(",", array_keys($data)),
implode(",", array_fill(0, count($data), '?')));
$stmt = mysqli_prepare($db_connection, $sql);
// Bind parameters and execute
mysqli_stmt_bind_param($stmt, str_repeat('s', count($data)), ...array_values($data));
mysqli_stmt_execute($stmt);
mysqli_stmt_close($stmt);
}
$i++;
}
} else {
?>
<div class="alert alert-red">Uploaded file was not a csv file. Please upload a csv file</div>
<?php
}
}
if ($query = mysqli_query($db_connection, 'SELECT * FROM employees')) {
?>
<table class="table">
<thead>
<tr>
<th>Name</th>
<th>Email</th>
<th>Gender</th>
<th>Designation</th>
</tr>
</thead>
<tbody>
<?php
// Loop through all records and display data
while ($record = mysqli_fetch_assoc($query)) { ?>
<tr>
<td><?= $record['full_name']; ?></td>
<td><?= $record['email']; ?></td>
<td><?= $record['gender']; ?></td>
<td><?= $record['designation']; ?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php } ?>
</div>
</div>
</section>
</body>
</html>
Add CSS Styles for The Import Interface
Add CSS styles for the HTML page and the employees table.
style.css
* {
box-sizing: border-box;
}
html,body {
margin: 0;
}
body {
background-color: #f6f6f6;
font-family: "Segoe UI", "Roboto", "Helvetica", sans-serif;
font-size: 15px;
font-weight: normal;
font-style: normal;
}
.container {
max-width: 1140px;
width: 100%;
margin-right: auto;
margin-left: auto;
padding-right: 15px;
padding-left: 15px;
}
.py-4 {
padding-top: 1rem;
padding-bottom: 1rem;
}
.form-wrapper:first-child {
padding: 15px;
background: #fff;
}
.row {
display: -webkit-box;
display: -ms-flexbox;
display: flex;
-ms-flex-wrap: wrap;
flex-wrap: wrap;
margin-right: -15px;
margin-left: -15px;
}
.col-4 {
-webkit-box-flex: 0;
-ms-flex: 0 0 33.333333%;
flex: 0 0 33.333333%;
max-width: 33.333333%;
min-height: 1px;
padding-right: 15px;
padding-left: 15px;
}
.text-right {
text-align: right;
}
.alert-red {
background: #e65442;
border: 1px solid #cc3a28;
width: 100%;
padding: 10px;
margin-bottom: 10px;
color: #fff;
transition: opacity 0.5s;
}
.btn-green {
display: inline-block;
padding: 5px 10px;
cursor: pointer;
font: inherit;
border-radius: .25rem;
background: #00a65a;
border: 1px solid #009549;
color: #ffffff;
}
.table {
border: 1px solid #dddddd;
width: 100%;
border-collapse: collapse;
margin: 5px 0;
background: #fff;
}
.table, .table > thead > tr > th,
.table > thead > tr > td,
.table > tbody > tr > th,
.table > tbody > tr > td {
border: 1px solid #dddddd;
}
.table td {
padding: 5px 10px;
}
.table th {
text-align: left;
padding: 10px;
}
We just created a CSV import in PHP without using an external PHP library. With this approach, we can quickly import a large set of data to the database. The code can be further modified to add additional features like column mapping and properly sanitizing and validating input data.