Import CSV File Data into MySQL Database using PHP


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

How to Import CSV File Data into MySQL Database using PHP

Follow the below steps to import 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>

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, we have learned how to upload csv file and import/insert data into MySQL database using PHP.

Recommended PHP Tutorials

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

jassweb logo

Jassweb always keeps its services up-to-date with the latest trends in the market, providing its customers all over the world with high-end and easily extensible internet, intranet, and extranet products.

Contact
Jassweb, Rai Chak, Punjab, India. 143518
Open chat
Scan the code
Hello 👋
Can we help you?