Importing CSV File Data into MySQL Database using PHP Tutorial

Submitted by oretnom23 on Saturday, December 10, 2022 - 16:07.

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 IF NOT EXISTS `dummy_db` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `dummy_db`;
 
-- --------------------------------------------------------
 
--
-- Table structure for table `members`
--
 
CREATE TABLE IF NOT EXISTS `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:)

Comments

csv file data

thanks for sharing only want to add how to skip the first line of the head of csv file $flag = true; // Read CSV Data by row while(($row = fgetcsv($csv_file, 1000, ",")) !== FALSE){ if($rowCount > 0){ //Sanitizing Data if($flag) { $flag = false; continue; } $name = addslashes($conn->real_escape_string($row[6])); $phone = addslashes($conn->real_escape_string($row[7])); $email = addslashes($conn->real_escape_string($row[8]));

Add new comment

Add new comment
1779 views