Bulk Product Data Insert,Update CSV File into Database Using PHP and Mysqli

In this article, we will discuss how to efficiently insert and update data from CSV files into a database using PHP and MySQL. This process can save a significant amount of time, especially for eCommerce websites that require bulk product information updates in the database.

Live Demo Download

For using this you must create one example CSV sheet based on your database column. Here I have created a sample shopping cart website product table.

Table Structure
CSV File Preparation
Database Configuration (config.php)
HTML Form for Bulk Data Upload
Bulk Data Insert Code (insert_data.php)
Update Bulk Data (update_data.php)

Table structure

Before proceeding, you need to create a 'products' table in the database with the following structure.

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sku_id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `qty` int(11) NOT NULL,
  `descriptions` varchar(200) NOT NULL
)

CSV File Preparation

Create a sample CSV file based on the column names of the 'products' table. The 'id' column is auto-incremented, so it does not need data in the CSV file. The 'sku_id' should contain unique identifiers for the products.

Id - Autoincrement so no need gives that column data.

Sku_id - Unique id (like product unique identify id).

Database Configuration (config.php)

Create a 'config.php' file and add the following code to establish a database connection:

<?php 
    $user = 'root';
    $password = '';
    $database = 'Your_database';
    $db = new mysqli('localhost',$user,$password,$database); 
?>

HTML Form for Bulk Data Upload

<div align="center">
<h3>Bulk Data upload</h3>
    <form action="insert_data.php" enctype="multipart/form-data" method="post"/>
        <label>Import csv file :</label>
        <input type="file" name="csv" id="csv" class="large"/>
        <input type="submit" name="form_submit" value="import file" />
    </form>
</div>

The 'action' attribute specifies the target PHP script for data insertion.

Bulk Data Insert Code (insert_data.php)

<?php
include("config.php");
if(isset( $_FILES['csv'] )) :
  $csv_file = $_FILES['csv']['tmp_name'];
  if(is_file( $csv_file)) :
    if(($handle = fopen($csv_file,"r")) !== FALSE) :
       while (($csv_data = fgetcsv($handle, 1000, ",")) !== FALSE)  {
       $num = count($csv_data);
        for ($c=0; $c < $num; $c++):
          $colum[$c] = $csv_data[$c]; 
        endfor;         
        $inserted=$db->query("INSERT INTO products(sku_id,name,qty,descriptions)

         VALUES('$colum[0]','$colum[1]','$colum[2]','$colum[3]')");
       }
       $msg = "Data uploaded successfully.";
       fclose($handle);
    else :
      $msg = "unable to read the format try again";
    endif;
  else :
    $msg = "CSV format File not found";
  endif;
else :
    $msg = "Please try again.";
endif;
echo $msg;
?>

fgetcsv() - Gets line from file pointer and parse for CSV fields, This function will check the CSV files array fields success or Failure.
fgetcsv($handle,1000) - Count the total keys in the row.

The output of the products table.

Update bulk data (update_data.php)

Create a new file name called update_data.php write and follow the below code.

<div align="center">
<h3>Bulk Data upload</h3>
    <form enctype="multipart/form-data" method="post"/>
        <label>Import csv file :</label>
        <input type="file" name="csv" id="csv" class="large"/>
        <input type="submit" name="submit" value="import file" />
    </form>
</div>

<?php
include("config.php");
if(isset( $_FILES['csv'] )) :
  $csv_file = $_FILES['csv']['tmp_name'];
  if(is_file( $csv_file)) :
    if(($handle = fopen($csv_file,"r")) !== FALSE) :
       while (($csv_data = fgetcsv($handle, 1000, ",")) !== FALSE)  {
       $num = count($csv_data);
        for ($c=0; $c < $num; $c++):
          $colum[$c] = $csv_data[$c]; 
        endfor;
        if($colum[0]!="") : 
          $sql="SELECT sku_id FROM products WHERE sku_id ='$colum[0]'";
          $db_check=$db->query($sql);
          $count=mysqli_num_rows($db_check); // checking exist data
           if($count==1) :
             $update=$db->query("UPDATE products SET qty = '$colum[2]',

              descriptions = '$colum[3]' WHERE sku_id='$colum[0]'");
           else :
            echo '<pre> wrong sku id data<br>'; print_r($colum); exit;
           endif;
        else :
          $msg = "Some data is missing"; exit;
        endif;
       }
       $msg = "Data uploaded successfully.";
       fclose($handle);
    else :
      $msg = "unable to read the format try again";
    endif;
  else :
    $msg = "CSV format File not found";
  endif;
else :
    $msg = "Please submit the form";
endif;
echo $msg;
?>

CSV file Insert and update both code almost same. we need to check inserted data based on sku_id(unique id) . Sku_id already exist the table then only allow to update the data.

mysqli_num_rows() - Returns the number of rows in the result set. if the $count==1 the data already exist.

Menu Link

03 October, 2020