In this tutorial we are going to learn how to import csv file using php and save data in mysql table. CSV(Comma Separated Values) stores the data as comma separated  format. To demonstrate import data from csv to mysql database we will take one example.

Create Table in MySql(Phpmyadmin)

Create the users table in mysql with basic fields. Below snippet  shows the users table structure.

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
 `phone` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
 `created_at` datetime NOT NULL,
 `updated_at` datetime NOT NULL,
 `status` enum('1','0') COLLATE utf8_unicode_ci NOT NULL DEFAULT '1',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


Connect to the MySql(Phpmyadmin) config.php

Firstly, we need to connect to the mysql database to import the data from csv. Below are the code Snippet. In config.php add your database host name, Database user name, Database password and database name.

<?php
//Database details
$db_host     = 'localhost';
$db_username = 'username';
$db_password = 'password';
$db_name     = 'databasename';

//Create connection and select DB
$db = new mysqli($db_host, $db_username, $db_password, $db_name);

if($db->connect_error){
    die("Unable to connect database: " . $db->connect_error);
}


CSV File Upload (index.php)

In index.php we have basic html table with users listed. On clicking import users button you have to choose the .csv file to import. After selecting .csv file click on import button it will post the data to import.php for importing the user's data to database.

<?php
//load the database configuration file
include 'config.php';

if(!empty($_GET['status'])){
    switch($_GET['status']){
        case 'succ':
            $statusMsg = 'Users data has been inserted successfully.';
            break;
        case 'err':
            $statusMsg = 'Some problem occurred, please try again.';
            break;
        case 'invalid_file':
            $statusMsg = 'Please upload a valid CSV file.';
            break;
        default:
            $statusMsg = '';
    }
}
?>
<div class="container">
    <?php if(!empty($statusMsg)){
        echo '<div>'.$statusMsg.'</div>';
    } ?>
    <div class="panel panel-default">
        <div class="panel-heading">
            Users list
            <a href="javascript:void(0);" onclick="$('#import_form').slideToggle();">Import Users</a>
        </div>
        <div class="panel-body">
            <form action="import.php" method="post" enctype="multipart/form-data" id="import_form">
                <input type="file" name="file" />
                <input type="submit" class="btn btn-primary" name="importBtn" value="IMPORT">
            </form>
            <table class="table table-bordered">
                <thead>
                    <tr>
                      <th>Name</th>
                      <th>Email</th>
                      <th>Phone</th>
                      <th>Created</th>
                      <th>Status</th>
                    </tr>
                </thead>
                <tbody>
                <?php
                    //get records from database
                    $query = $db->query("SELECT * FROM users ORDER BY id DESC");
                    if($query->num_rows > 0){ 
                        while($row = $query->fetch_assoc()){ ?>
                    <tr>
                      <td><?php echo $row['name']; ?></td>
                      <td><?php echo $row['email']; ?></td>
                      <td><?php echo $row['phone']; ?></td>
                      <td><?php echo $row['created_at']; ?></td>
                      <td><?php echo ($row['status'] == '1')?'Active':'Inactive'; ?></td>
                    </tr>
                    <?php } }else{ ?>
                    <tr><td colspan="5">No user(s) found.....</td></tr>
                    <?php } ?>
                </tbody>
            </table>
        </div>
    </div>
</div>

Import CSV File Data to Mysql Database (import.php)

<?php
//load the database configuration file
include 'config.php';

if(isset($_POST['importBtn'])){
    
    //validate whether uploaded file is a csv file
    $csvMimes = array('text/x-comma-separated-values', 'text/comma-separated-values', 'application/octet-stream', 'application/vnd.ms-excel', 'application/x-csv', 'text/x-csv', 'text/csv', 'application/csv', 'application/excel', 'application/vnd.msexcel', 'text/plain');
    if(!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'],$csvMimes)){
        if(is_uploaded_file($_FILES['file']['tmp_name'])){
            
            //open uploaded csv file with read only mode
            $csvFile = fopen($_FILES['file']['tmp_name'], 'r');
            
            //skip first line
            fgetcsv($csvFile);
            
            //parse data from csv file line by line
            while(($line = fgetcsv($csvFile)) !== FALSE){
                //check whether user already exists in database with same email
                $prevQuery = "SELECT id FROM users WHERE email = '".$line[1]."'";
                $prevResult = $db->query($prevQuery);
                if($prevResult->num_rows > 0){
                    //update user data
                    $db->query("UPDATE users SET name = '".$line[0]."', phone = '".$line[2]."', created = '".$line[3]."', updated_at = '".$line[3]."', status = '".$line[4]."' WHERE email = '".$line[1]."'");
                }else{
                    //insert user data into database
                    $db->query("INSERT INTO users (name, email, phone, created_at, updated_at, status) VALUES ('".$line[0]."','".$line[1]."','".$line[2]."','".$line[3]."','".$line[3]."','".$line[4]."')");
                }
            }
            
            //close opened csv file
            fclose($csvFile);

            $qstring = '?status=succ';
        }else{
            $qstring = '?status=err';
        }
    }else{
        $qstring = '?status=invalid_file';
    }
}
//redirect to the listing page
header("Location: index.php".$qstring);

In import.php file are processed as below.

- Once user hit the import button form submitted to the import.php
- In import.php checked if form is submitted.
- Opened the .csv file in readonly mode.
- In import.php validated the .csv file. If file is not csv, control goes to the else and show the proper message.
- In import.php checked the email is already inserted in users table. If email is already there updated the existing record else inserted the  new record.
- Closed the file and reset the file pointer.
Written by
Check out this Author's contributed articles.
If you like Tutorialsmint and would like to contribute, you can also write an article using Write an Article. See your article appearing on the Tutorialsmint main page and help other programmers to enhance their knowledge.

Comments(0)

RECENT TUTORIALS
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Wed Dec 26 • 08:00 AM GMT
Edit Post
Add Reply