Import CSV File Data into MySQL Database using PHP Ajax

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>  

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.

Recommended PHP Tutorials

If you have any questions or thoughts to share, use the comment form below to reach us.

AuthorAdmin

My name is Devendra Dode. I am a full-stack developer, entrepreneur, and owner of Tutsmake.com. I like writing tutorials and tips that can help other developers. I share tutorials of PHP, Python, Javascript, JQuery, Laravel, Livewire, Codeigniter, Node JS, Express JS, Vue JS, Angular JS, React Js, MySQL, MongoDB, REST APIs, Windows, Xampp, Linux, Ubuntu, Amazon AWS, Composer, SEO, WordPress, SSL and Bootstrap from a starting stage. As well as demo example.

Leave a Reply

Your email address will not be published. Required fields are marked *