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

19 April, 2016

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

In this post, we will discuss on how to insert and update bulk CSV file data into the database. This tutorial help with bulk product upload,bulk data upload website users. Because they need to insert and update large data daily. Just inserting data one by one is very difficult and time consuming also more process. So, I would suggest like website follow this script you can easily upload more data to the database.

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


Live Demo          Download

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

Table structure

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










Related Topics

No comments:

Post a Comment