Importing CSV File Data into MySQL Database using PHP Tutorial

shylock95 · Thursday at 3:40 AM

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`
--
CREATE

DATABASE

[url=http://dev.mysql.com/doc/refman/%35%2E%31/en/control-flow-functions.html]IF

NOT

EXISTS

[/url] `dummy_

db`

DEFAULT

CHARACTER SET

utf8mb4 COLLATE

utf8mb4_general_ci;
USE

`dummy_

db`

;
-- --------------------------------------------------------
--
-- Table structure for table `members`
--
CREATE

TABLE

[url=http://dev.mysql.com/doc/refman/%35%2E%31/en/control-flow-functions.html]IF

NOT

EXISTS

[/url] `members`

(
`id`

int

(

11

)

NOT

NULL

AUTO_INCREMENT

,
`name`

varchar

(

250

)

NOT

NULL

,
`phone`

varchar

(

50

)

NOT

NULL

,
`email`

varchar

(

100

)

NOT

NULL

,
`created_

at`

datetime

NOT

NULL

DEFAULT

current_timestamp

(

)

,
PRIMARY KEY

(

`id`

)
)

ENGINE

=

InnoDB

DEFAULT

CHARSET

=

utf8mb4;

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>
<html

lang

=

"en"

>
<head

>
<meta

charset

=

"UTF-8"

>
<meta

http-equiv

=

"X-UA-Compatible"

content

=

"IE=edge"

>
<meta

name

=

"viewport"

content

=

"width=device-width, initial-scale=1.0"

>
<title

>

Import CSV Data to MySQL in PHP</

title

>
<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

>
</

head

>
<body

style

=

"background:#9CB4CC"

>
<nav class

=

"navbar navbar-expand-lg navbar-dark"

style

=

"background:#06283D"

>
<div

class

=

"container"

>
<a

class

=

"navbar-brand"

href

=

"./"

>

Import CSV Data to MySQL in PHP</

a

>
<div

>
<a

href

=

"https://sourcecodester.com"

class

=

"text-light fw-bolder h6 text-decoration-none"

target

=

"_blank"

>

SourceCodester</

a

>
</

div

>
</

div

>
</

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

>
<hr

>
<?php if(

isset(

$_SESSION[

'status'

]

)

&& $_SESSION[

'status'

]

==

"success"

)

: ?>
<div

class

=

"alert alert-success rounded-0 mb-3"

>
<?=

$_SESSION[

'message'

]

?>
</

div

>
<?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'

]

?>
</

div

>
<?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

>
<div

class

=

"card-body rounded-0"

>
<div

class

=

"container-fluid"

>
<form

action

=

"import_csv.php"

id

=

"import-form"

method

=

"POST"

enctype

=

"multipart/form-data"

>
<div

class

=

"mb-3"

>
<label

for

=

"fileData"

class

=

"form-label"

>

Browse CSV Data</

label

>
<input

class

=

"form-control"

type

=

"file"

accept

=

".csv"

name

=

"fileData"

id

=

"fileData"

required>
</

div

>
</

form

>
</

div

>
</

div

>
<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

>
</

div

>
</

div

>
<div

class

=

"card my-2 rounded-0"

>
<div

class

=

"card-header rounded-0"

>
<div

class

=

"card-title"

><b

>

Member List</

b

></

div

>
</

div

>
<div

class

=

"card-body rounded-0"

>
<div

class

=

"container-fluid"

>
<div

class

=

"table-responsive"

>
<table

class

=

"table table-hovered table-striped table-bordered"

>
<thead

>
<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

>
</

tr

>
</

thead

>
<tbody

>
<?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()):
?>
<tr

>
<th

class

=

"text-center"

><?=

$row[

'id'

]

?></

th

>
<td

><?=

$row[

'name'

]

?></

td

>
<td

><?=

$row[

'phone'

]

?></

td

>
<td

><?=

$row[

'email'

]

?></

td

>
</

tr

>
<?php endwhile; ?>
<?php else: ?>
<tr

>
<th

class

=

"text-center"

colspan

=

"4"

>

No data on the database yet.</

th

>
</

tr

>
<?php endif; ?>
<?php $conn->

close() ?>
</

tbody

>
</

table

>
</

div

>
</

div

>
</

div

>
</

div

>
</

div

>
</

div

>
</

body

>
</

html

>

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
session_start

(

)

;
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
fclose

(

$csv_file

)

;
/**
* Check if there's a data to insert otherwise return error
*/
if

(

count

(

$data

)

>

0

)

{
// 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

(

)

;
header

(

'location: ./'

)

;
exit

;
?>

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:)

Download

You must upgrade your account or reply in the thread to view the hidden content.

Importing CSV File Data into MySQL Database using PHP Tutorial

More options

shylock95

452,496

331,765

331,773