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 database table to demonstrate how to import data from a CSV file into database and add employees records from uploaded file.
CSV is abbreviation of Comma Separated Values. As file extension tells its a text file that contains table data values separated with commas. Each row in this file is a record containing field values separated with commas. 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 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. This post explains with working example how to import CSV data into MySQL database table in PHP. You may also want to check Export MySQL Data to CSV 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.
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. $data array variable will be prepared 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"/>
<link rel="stylesheet" href="css/style.css" />
</head>
<body>
<div class="container">
<div class="section">
<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="section">
<?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' );
// Loop through all rows of file and insert them to database table
while( !feof($file) ){
// Get current row as recordset
$row = fgetcsv( $file );
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]);
mysqli_query($db_connection, "INSERT INTO employees (".implode(",", array_keys($data)).") VALUES ('".implode("','", array_values($data))."')");
}
}
}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>
</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;
}
.section: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;
}