Import CSV into MySQL Database in PHP
Learn in this post how to read CSV file in PHP and import CSV into MySQL database. We will use a sample database table to demonstrate how to import data from a CSV file into database and add employees records from uploaded file.
CSV import in web applications is a useful feature to bulk data import like employees, products and other data records. We will upload a sample CSV file of employees record and upload it via an HTML form. The uploaded file will be processed with a PHP code on server side with file error check and file format check.
What is a CSV File?
CSV is abbreviation of Comma Separated Values. As file extension tells its 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 comes handy when you want to add large number for records in database. Instead of adding each record manually for such large number of records can be time consuming and frustrating. With CSV upload and import in PHP the same result can be achieved in much faster and easier way. We can also Export MySQL Data to CSV in PHP which we will cover in future post. This post explains with working example how to import CSV data into MySQL database table in PHP.
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 database table.
- style.css: Contains CSS styles for our form and table that shows the employees records from table.
Add Constants for Database Connection
The 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 Table
We are now going to import CSV to MySQL database and show the imported records as HTML table. This is what this file is basically doing:
- Establish a database connection and assign it to a variable.
- Prepare a form with file type input field which will be used to upload a csv file.
- When the form is submitted, check if there was a file uploaded without any errors.
- Proceed to our next step and use
pathinfo()to get extension of uploaded file to confirm it was CSV file. - Then open uploaded file with file function
fopen()with read permission. - Then read CSV file until it hits the end of file and use
fgetcsv()to fetch the row in current iteration. We prepare$dataarray variable with values to insert data into our employees table.
After the data has been imported we run another query to get records from 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'] = htmlentities($row[0]);
$data['email'] = htmlentities($row[1]);
$data['designation'] = htmlentities($row[2]);
$data['gender'] = htmlentities($row[3]);
$stmt = mysqli_prepare($db_connection, "INSERT INTO employees (".implode(",", array_keys($data)).") VALUES (".implode(",", array_fill(0, count($data), '?')).")");
// 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['designation'];?></td>
<td><?=$record['gender'];?></td>
</tr>
<?php
}
?>
</tbody>
</table>
<?php }?>
</div>
</div>
</section>
</body>
</html>
Add CSS Styles
Add CSS styles for HTML page and 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;
}