Import CSV File Data into MySQL Database using PHP Ajax

Import CSV file data into MySQL database using PHP ajax; In this tutorial; you will learn how to import/insert CSV file data into MySQL database using PHP + ajax.

How to Import/Insert CSV File Data into MySQL Database using PHP + Ajax

Follow the below steps and import/upload/insert CSV file data into MySQL database using php ajax

Step 1 – Create PHP Project
Step 2 – Create Table in Database
Step 3 – Create a Database Connection File
Step 4 – Create HTML Form & Implment jQuery Ajax Function
- Create HTML Form to Upload CSV
- Add jQuery Library
- Implement Ajax Code for Upload CSV File
Step 5 – Create PHP File To Import Csv File Data Into Database

Step 1 – Create PHP Project

First of all; visit your webserver directory and create a php directory; which name demo.

Step 2 – Create Table in Database

Create table into your database; so visit your PHPMyAdmin and create a table name users with the following fields: name, email, mobile.

CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
 `email` varchar(50) 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('Active','Inactive') COLLATE utf8_unicode_ci NOT NULL DEFAULT 'Active',
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Step 3 – Create a Database Connection File

Create a file name db.php and update the below code into your file.

<?php
	$servername='localhost';
	$username='root';
	$password='';
	$dbname = "my_db";
	$conn=mysqli_connect($servername,$username,$password,"$dbname");
	  if(!$conn){
		  die('Could not Connect MySql Server:' .mysql_error());
		}
?>

Step 4 – Create HTML Form & Implment jQuery Ajax Function

Create HTML Form to Upload CSV

Create a simple HTML upload csv file form and add the following code into your index.php file:

<!doctype html>
<html lang="en">

<head>
  <!-- Required meta tags -->
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">

  <!-- Bootstrap CSS -->
  <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">


  <title>Import CSV File into MySQL using PHP Ajax</title>

</head>

<body>

  <div class="container">
    <h2 class="mb-3 mt-3"> Import CSV File into MySQL using PHP </h2>
    <form action="upload.php" method="post" enctype="multipart/form-data" id="upload_csv_form">
        <div class="form-group">
          <label for="exampleFormControlFile1">Please Select File</label>
          <input type="file" name="file" class="form-control-file" id="exampleFormControlFile1">
        </div>
        <div class="form-group">
         <input type="submit" name="submit" value="submit" class="btn btn-primary">
       </div>
  </form>
  </div>

</body>

</html>

Add jQuery Library

Add jQuery library into your index.php file:

  <script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>

Implement Ajax Code for Upload CSV File

Implement ajax code for upload csv file to server:

 <script>  
      $(document).ready(function(){  
           $('#upload_csv_form').on("submit", function(e){  
                e.preventDefault(); //form will not submitted  
                $.ajax({  
                     url:"upload.php",  
                     method:"POST",  
                     data:new FormData(this),  
                     contentType:false,          // The content type used when sending data to the server.  
                     cache:false,                // To unable request pages to be cached  
                     processData:false,          // To send DOMDocument or non processed data file it is set to false  
                     success: function(data){  
                          if(data=='Error1')  
                          {  
                               alert("Invalid File");  
                          }  
                          else if(data == "Error2")  
                          {  
                               alert("Please Select File");  
                          }                           
                          else if(data == "Success")  
                          {  
                               alert("CSV file data has been imported");  
                               $('#upload_csv_form')[0].reset();
                          }  
                          else  
                          {  
                              // $('#employee_table').html(data);  
                          }  
                     }  
                })  
           });  
      });  
 </script>

Recommended:-File Upload in PHP MySQL database

Step 5 – Create PHP File To Import Csv File Data Into Database

Create one file name upload.php; which is used to read csv file and insert all csv file data into MySQL database. So add the following code into upload.php file:

<?php
// Load the database configuration file
include_once 'db.php';

if(!empty($_FILES["file"]["name"]))
{

    // Allowed mime types
    $fileMimes = 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'
    );

    // Validate whether selected file is a CSV file
    if (!empty($_FILES['file']['name']) && in_array($_FILES['file']['type'], $fileMimes))
    {

            // Open uploaded CSV file with read-only mode
            $csvFile = fopen($_FILES['file']['tmp_name'], 'r');

            // Skip the first line
            fgetcsv($csvFile);

            // Parse data from CSV file line by line
            while (($getData = fgetcsv($csvFile, 10000, ",")) !== FALSE)
            {
                // Get row data
                $name = $getData[0];
                $email = $getData[1];
                $phone = $getData[2];
                $status = $getData[3];

                // If user already exists in the database with the same email
                $query = "SELECT id FROM users WHERE email = '" . $getData[1] . "'";

                $check = mysqli_query($conn, $query);

                if ($check->num_rows > 0)
                {
                    mysqli_query($conn, "UPDATE users SET name = '" . $name . "', phone = '" . $phone . "', status = '" . $status . "', created_at = NOW() WHERE email = '" . $email . "'");
                }
                else
                {
                     mysqli_query($conn, "INSERT INTO users (name, email, phone, created_at, updated_at, status) VALUES ('" . $name . "', '" . $email . "', '" . $phone . "', NOW(), NOW(), '" . $status . "')");

                }
            }

            // Close opened CSV file
            fclose($csvFile);

            echo "Success";
        
    }
    else
    {
        echo "Error1";
    }
}else{
  echo "Error2";  
}

Conclusion

Import CSV file data into MySQL database using PHP ajax; In this tutorial; you have learned how to how to import csv file data into MySQL database using PHP + ajax.