loop through records in excel and update specific rows in mysql database with values from the excel file in php

Question

Hello am trying to update rows in my savings table but getting values from an excel file using a primary key as account_id which both the file and the table.My code does not execute like expected

   if(isset($_POST['import'])){
       $filename = explode(".", $_FILES["file"]["name"]);
       if ($filename[1]=="csv") {
           $file = $_FILES['file']['tmp_name'];
           $openFile = fopen($file,'r');

           while ($dataFile = fgetcsv($openFile,3000,",")) {
               $acc = $dataFile[0];
               $amount_deposited = $dataFile[2];
               $recovered_amount = $dataFile[3];

               /*update balances */
               $insert = "UPDATE savings SET balance = balance+'$amount_deposited' WHERE account_id ='$acc'";
               $run_insert = mysql_query($insert);

               $insert = "INSERT INTO transactions values('','$acc','Saving','$recovered_amount','',now(),'')";
               $run_insert = mysql_query($insert);
          }
          echo "<div id='alert'>Successfully ADDED</div>";
       } else {
          echo "You must choose a CSV file to import";
       }
    }

i expected the savings table to be updated with a new sum of balance after importing the csv file with the new amount deposited+the balance that was originally there — Jobs Athand, Aug 25 '16 at 08:52
Every time you use [the `mysql_` database extension](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php), **a Kitten is strangled somewhere in the world** it is deprecated (gone for ever in PHP7) Specially if you are just learning PHP, spend your energies learning the `PDO` database extensions. [Start here](http://php.net/manual/en/book.pdo.php) its really pretty easy — RiggsFolly, Aug 25 '16 at 08:57
I suggest you : Add [error reporting](http://stackoverflow.com/questions/845021/how-to-get-useful-error-messages-in-php/845025#845025) to the top of your file(s) _while testing_ right after your opening PHP tag for example ` — RiggsFolly, Aug 25 '16 at 09:10
I suggest you also add a test of the `$run_insert` status like so `if ( ! $run_insert) { echo mysql_error(); exit;}` after each INSERT so you get to see any errors that may be happening — RiggsFolly, Aug 25 '16 at 09:11
I also suggest you show us the code that makes the connection to your database — RiggsFolly, Aug 25 '16 at 09:13
This is my code to the database mysql_connect('localhost','root','root'); mysql_select_db('test'); — Jobs Athand, Aug 25 '16 at 09:19

score 0 · Answer 1 · answered Aug 25 '16 at 09:24

This worked,Thanks guys

              while ($dataFile = fgetcsv($openFile,3000,",")) {
               $acc = $dataFile[0];
                $amount_deposited = $dataFile[1];

               /*update balances */

               $insert = "UPDATE savings SET balance = balance+".intval($amount_deposited)." WHERE account_id ='$acc'";
               $run_insert = mysql_query($insert);

              $insert2 = "INSERT INTO trans values('','$amount_deposited',now(),'$acc')";
               $run_insert = mysql_query($insert2);
               }
              echo "<div id='alert'>Successfully ADDED</div>";
            }
            else{
              echo "You must choose a CSV file to import";
            }
          }

loop through records in excel and update specific rows in mysql database with values from the excel file in php

1 Answers1