How to Import CSV File Data to MySQL Database using PHP

0 0 3 minutes read

Import/upload CSV file data into mysql database using php; In this tutorial; you will learn how to how to import/upload CSV file data to MySQL database using PHP script.

Import/Upload CSV File Data to MySQL Database using PHP

Follow the below steps to import/upload CSV file data into MySQL using PHP script or code:

Step 1 – Create PHP Project
Step 2 – Create Table in Database
Step 3 – Create a Database Connection File
Step 4 – Create HTML Form To Upload CSV File
Step 5 – Create PHP File To Import Csv File Data Into Database

Step 1 – Create PHP Project

First of all; 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 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 To Upload CSV File

Create a simple HTML upload csv file form and add the following code into 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>

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
// 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.