Import CSV file data into Mysql database using PHP ajax; In this tutorial; i am going to show you how to import CSV file data into MySQL database using PHP + Ajax.
In this tutorial, i will create a simple file upload form with jQuery ajax code and upload csv file to server. Then extract data from csv file and insert into MySQL database using PHP + Ajax.
Import CSV File Into MySQL Database using PHP Ajax
Use the below given steps to import csv file into MySQL database using PHP Ajax:
- Step 1 – Create PHP Project Directory
- Step 2 – Create Table into Database
- Step 3 – Create a Database Connection File
- Step 4 – Create HTML Form with Ajax To Upload CSV File
- Step 5 – Create Upload.PHP File To Insert Csv File Data Into Database
Step 1 – Create PHP Project Directory
Create new project directory; so visit web server 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 with Ajax To Upload CSV File
Create a simple HTML upload csv file form with ajax 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"> <script src="https://code.jquery.com/jquery-3.6.0.min.js" integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script> <title>Import CSV File into MySQL using PHP</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> <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> </html>
Step 5 – Create Upload.PHP File To Insert 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
PHP import data into MySQL database; Through this tutorial, you have learned how to upload csv file and import/insert data into MySQL database using PHP.