Saturday, 29 July 2017

Update Mysql Database through Upload CSV File using PHP



In this post We have discuss topic like how to upload CSV file for update mysql table data by using PHP script. We have received to many request on make tutorial on how to update mysql data by uploading CSV file by simple PHP script. In one of our previous tutorial we have already learn how to insert data into mysql table by uploading CSV file using PHP code. But now here topic is something different, here we do not want to insert data but we want to update existing inserted data by uploading CSV file using simple PHP code.

This type of feature mainly used if you have manage online store or online restaurant booking system in which you want to update data on daily basis. For example, suppose you have run online store and on your website home page want to display daily different product then at that time if you have build this type of feature for your web application. By using this feature you can easily update existing data and that data you can display on your web application. So this way you can use this feature for display different data on your web application.

In simple terms by uploading file you can edit large amount of data in single click event. For developed this type of simple application we just want knowledge of PHP script filesystem function. Here we have used fopen() and fclose() function for open file for data from particular file. After this we have use fgetcsv() function, by using this function we can get comma separated values data in array format. After getting CSV data in array format, then after we have make simple mysql update data query for edit existing data in database. This way we can update existing data of Mysql table by using CSV file using simple PHP script.



Source Code



<?php
//index.php
$connect = mysqli_connect("localhost", "root", "", "testing");
$message = '';

if(isset($_POST["upload"]))
{
 if($_FILES['product_file']['name'])
 {
  $filename = explode(".", $_FILES['product_file']['name']);
  if(end($filename) == "csv")
  {
   $handle = fopen($_FILES['product_file']['tmp_name'], "r");
   while($data = fgetcsv($handle))
   {
    $product_id = mysqli_real_escape_string($connect, $data[0]);
    $product_category = mysqli_real_escape_string($connect, $data[1]);  
                $product_name = mysqli_real_escape_string($connect, $data[2]);
    $product_price = mysqli_real_escape_string($connect, $data[3]);
    $query = "
     UPDATE daily_product 
     SET product_category = '$product_category', 
     product_name = '$product_name', 
     product_price = '$product_price' 
     WHERE product_id = '$product_id'
    ";
    mysqli_query($connect, $query);
   }
   fclose($handle);
   header("location: index.php?updation=1");
  }
  else
  {
   $message = '<label class="text-danger">Please Select CSV File only</label>';
  }
 }
 else
 {
  $message = '<label class="text-danger">Please Select File</label>';
 }
}

if(isset($_GET["updation"]))
{
 $message = '<label class="text-success">Product Updation Done</label>';
}

$query = "SELECT * FROM daily_product";
$result = mysqli_query($connect, $query);
?>
<!DOCTYPE html>
<html>
 <head>
  <title>Update Mysql Database through Upload CSV File using PHP</title>
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.0/jquery.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.6/css/bootstrap.min.css" />
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
 </head>
 <body>
  <br />
  <div class="container">
   <h2 align="center">Update Mysql Database through Upload CSV File using PHP</a></h2>
   <br />
   <form method="post" enctype='multipart/form-data'>
    <p><label>Please Select File(Only CSV Formate)</label>
    <input type="file" name="product_file" /></p>
    <br />
    <input type="submit" name="upload" class="btn btn-info" value="Upload" />
   </form>
   <br />
   <?php echo $message; ?>
   <h3 align="center">Deals of the Day</h3>
   <br />
   <div class="table-responsive">
    <table class="table table-bordered table-striped">
     <tr>
      <th>Category</th>
      <th>Product Name</th>
      <th>Product Price</th>
     </tr>
     <?php
     while($row = mysqli_fetch_array($result))
     {
      echo '
      <tr>
       <td>'.$row["product_category"].'</td>
       <td>'.$row["product_name"].'</td>
       <td>'.$row["product_price"].'</td>
      </tr>
      ';
     }
     ?>
    </table>
   </div>
  </div>
 </body>
</html>



Database



CREATE TABLE IF NOT EXISTS `daily_product` (
  `daily_product_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `product_category` varchar(250) NOT NULL,
  `product_name` varchar(250) NOT NULL,
  `product_price` double(10,2) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `daily_product`
--

INSERT INTO `daily_product` (`daily_product_id`, `product_id`, `product_category`, `product_name`, `product_price`) VALUES
(1, 101, 'Home and Garden', 'Fashionoma Hobby Toolkit', 199.00),
(2, 102, 'Electronics', 'Samsung EVO 32 GB', 749.00),
(3, 103, 'Fashion', 'Nova NHT Trimmer', 349.00),
(4, 104, 'Mobiles', 'Cases and Safeguard', 299.00),
(5, 105, 'Fashions', 'Womens Bag', 180.00),
(6, 106, 'Home and Garden', 'Insects Repellents', 99.00),
(7, 107, 'Home and Garden', 'Pressure Cookers and pans', 499.00),
(8, 108, 'Electronis', 'Mitashi TV', 6999.00),
(9, 109, 'Home and Garden', 'Water Gas Graysers', 2499.00),
(10, 110, 'Office', 'HP Deskjet Ink Advantage', 3999.00);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `daily_product`
--
ALTER TABLE `daily_product`
  ADD PRIMARY KEY (`daily_product_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `daily_product`
--
ALTER TABLE `daily_product`
  MODIFY `daily_product_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;

19 comments:

  1. super sir ! thanks for your share

    ReplyDelete
  2. please can you upload the template of the CSV file ?

    ReplyDelete
  3. Hi.
    Do you have a CSV file to test this update ? Thanks.

    ReplyDelete
  4. Followed the steps above. It displays "Product Updating Done" but the table data is not updated. Please help.

    ReplyDelete
  5. message is displaying updated but data not getting updated

    ReplyDelete
  6. how do you skip the 1st row from csv file???

    ReplyDelete
  7. what error


    Notice: Undefined offset: 1 in C:\xampp\htdocs\ssdt\form_upload.php on line 17

    Notice: Undefined offset: 2 in C:\xampp\htdocs\ssdt\form_upload.php on line 18

    Notice: Undefined offset: 3 in C:\xampp\htdocs\ssdt\form_upload.php on line 19

    ReplyDelete
  8. it will skip header row of csv

    ReplyDelete
  9. can u help me please , this updation ocuure after import the csv file?

    ReplyDelete
  10. Do you happen to have an example CSV file for this?

    ReplyDelete
  11. Thanks for the great support and knowledge you are sharing.
    Much appreciated. Would like you to also make a tutorial about sending emails to multiple users of the system at once.
    Thanks

    ReplyDelete
  12. Instead of upload csv shall we take csv from local storage path?

    ReplyDelete
  13. Hello, I tried using the script to update same record after downloading and changing some of the values

    ReplyDelete