Import CSV File Data into MySQL Database using PHP Ajax

Import CSV File Into MySQL Database using PHP - Lara Tutorials

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

Import CSV File Data into MySQL Database using PHP + Ajax

Follow the below steps and import/upload 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.

Leave a Reply

Your email address will not be published.