PHP MySQL import CSV file data; In this tutorial; i am going to show you how to import CSV file data into MySQL database using PHP.
In this tutorial, i will create a simple file upload form and upload csv file to server. Then extract data from csv file and insert into MySQL database using PHP.
Import CSV File Data Into MySQL Database using PHP
Use the below given steps to import csv file data into MySQL database using PHP code:
- Step 1 – Create PHP Project Directory
- Step 2 – Create Table into Mysql DB
- Step 3 – Create MySQL DB Connection PHP File
- Step 4 – Create HTML Form 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 into Mysql DB
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 MySQL DB Connection PHP 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 To Upload CSV File
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</title> <style> .custom-file-input.selected:lang(en)::after { content: "" !important; } .custom-file { overflow: hidden; } .custom-file-input { white-space: nowrap; } </style> </head> <body> <div class="container"> <form action="upload.php" method="post" enctype="multipart/form-data"> <div class="input-group"> <div class="custom-file"> <input type="file" class="custom-file-input" id="customFileInput" aria-describedby="customFileInput" name="file"> <label class="custom-file-label" for="customFileInput">Select file</label> </div> <div class="input-group-append"> <input type="submit" name="submit" value="Upload" class="btn btn-primary"> </div> </div> </form> </div> </body> </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 // include mysql database configuration file include_once 'db.php'; if (isset($_POST['submit'])) { // 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 // 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); header("Location: index.php"); } else { echo "Please select valid file"; } }
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.