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;

21 comments:

VS30 July 2017 at 00:06
super sir ! thanks for your share
Berrimi Mohamed31 July 2017 at 07:50
please can you upload the template of the CSV file ?
imba2 August 2017 at 03:49
thanks really help
Herbert2 August 2017 at 05:02
Thks so helpful
hugobenz17 January 2018 at 04:25
Hi.
Do you have a CSV file to test this update ? Thanks.
Unknown17 March 2018 at 04:15
Followed the steps above. It displays "Product Updating Done" but the table data is not updated. Please help.
surendra khadka8 May 2018 at 03:29
how to update in .xlsx
Abhay3 June 2018 at 09:21
message is displaying updated but data not getting updated
Bill's Firt Google Blog9 July 2018 at 20:30
how do you skip the 1st row from csv file???
Unknown30 July 2018 at 06:15
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
Anonymous18 September 2018 at 01:45
it will skip header row of csv
Unknown8 April 2019 at 02:52
can u help me please , this updation ocuure after import the csv file?
Tim31 May 2019 at 08:02
Do you happen to have an example CSV file for this?
Rukundo Agira6 January 2020 at 01:06
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
Unknown9 March 2020 at 16:56
doesnt work for me.
Zus10 March 2020 at 23:14
Instead of upload csv shall we take csv from local storage path?
red25 March 2020 at 23:21
it's not working =(
AMAREAL13 May 2020 at 05:54
Hello, I tried using the script to update same record after downloading and changing some of the values
Unknown11 August 2020 at 23:44
works only 1st row....
Unknown12 January 2021 at 01:53
Hi give me please a index.php file

Tanks !
Lewis Broadbent12 November 2021 at 08:04
Great bit of code. Thank you :)

Webslesson

PHP, Node.js, React.js, MySql, Jquery, AngularJS, Ajax, Codeigniter, Laravel Tutorial

Saturday, 29 July 2017

Update Mysql Database through Upload CSV File using PHP

Source Code

Database

21 comments:

Popular Posts