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

In this article, we will discuss How to insert and update CSV file data into the database by using a PHP script. By using this logic we can avoid a large amount of data manual entries and time consuming, Especially eCommerce website bulk product information upload to the database. for that kind of website builder, I strongly suggest following this script you can easily achieve bulk data insert to 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

We need to create 'products' table into 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

Create sample csv file based on the products table column name and insert product information data to that csv file.

Id - Autoincrement so no need gives that column data.

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

Database (config.php)

Create config.php file write database connection code to this file.

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

HTML

<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 above form action file insert_data.php and enctype="multipart/form-data" it will encrypt the form data to post on another page.

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