CSV file to update MySQL DB

Codes here !

Moderators: macek, egami, gesf

New php-forum User
New php-forum User
Posts: 3
Joined: Wed Sep 19, 2012 8:20 am

CSV file to update MySQL DB

Postby clint6998 » Mon Sep 24, 2012 7:14 am

I have a file that my company had created to pull data from three CSV files, delte the CSV files, delete the contents of the DB, and add the new data into the DB.

Here is the script:

Code: Select all


   //ini_set("display_errors", '1');

   //error_reporting(E_ALL & ~E_NOTICE);

   // Determine if the three files exist, if not exit

   if(file_exists("loads.csv") && file_exists("loads-OB.csv") && file_exists("loads-IB.csv")) {


      // cycle through fuser until three files aren't being used by other program (like ftp)

      $rc1 = 0;

      $rc2 = 0;

      $rc3 = 0;

      $fuser_count = 0;

      while (($rc1== 0)  || ($rc2 == 0) || ($rc3 == 0)) {

         // include a counter. If number of cycles > n, send email and exit


         if($fuser_count > 180) {  // pick a number

            // mail about the problem and quit

            $file_error = "can't access csv file  - open for three minutes";





         sleep(1);  // wait one second

         $last_line = exec("fuser loads.csv", $output, $rc1);

         $last_line = exec("fuser loads-OB.csv", $output, $rc2);

         $last_line = exec("fuser loads-IB.csv", $output, $rc3);



      // Read the contents of each file into an array

      $loads = file("loads.csv");

      $loads_ob = file("loads-OB.csv");

      $loads_ib = file("loads-IB.csv");


      // Delete the files





      /*// Create empty output string

      $bigfile = ''; */


      // connect to database


      $dbobj = new mysqli(DBHOST, DBUSER, PASSW, USEDB);

      if ($dbobj->connect_errno) {

         $db_error = "Can't connect to mysql or database: " . $dbobj->connect_error;

         echo $db_error;





      // delete contents of loads table

      $query = "TRUNCATE TABLE loads";

      if(!$dbobj->query($query)) {

         $db_error = "couldn't empty loads table: " . $dbobj->error;

         echo $db_error;





      // set big array to contain the three arrays

      $big_array = array($loads, $loads_ob, $loads_ib);


      // for each array in big array

      foreach ($big_array as $csv_file) {

         // for each array as line

         foreach($csv_file as $line) {

            // each field in the line is enclosed in quotes!

            // convert string to an array using ", (quote + comma) as separator

            $linearray = explode('",', rtrim($line));

            // get rid of the rest of the quotes

            foreach($linearray as &$field) {

               $field = str_replace('"', '', $field);


            //  get first seven fields

            $firstpart = array_slice($linearray, 0 , 7);

            // secondpart = array_slice(line, -5) get the last five

            $secondpart = array_slice($linearray, -5);


            // Create the human readable timestamp


            $now = date('m/d/y - H:i');


            // insert the combined array into the database

            $nextline = array_merge($firstpart, $secondpart);


            $query = "INSERT INTO loads (load_num, stops, avail_date, pickup_city, pickup_state, deliv_city, deliv_state, description, equipment, broker, broker_phone, broker_email, updated) VALUES ('$nextline[0]', '$nextline[1]', '$nextline[2]', '$nextline[3]', '$nextline[4]', '$nextline[5]', '$nextline[6]', '$nextline[7]', '$nextline[8]', '$nextline[9]', '$nextline[10]', '$nextline[11]', '$now')";

            if(!$dbobj->query($query)) {

               $db_error = "couldn't insert data into table: " . $dbobj->error;

               echo $db_error;





            /*// combine and turn back into a string with newline

            $nextline = implode(',', array_merge($firstpart, $secondpart)) . "\n";

            // add to output string

            $bigfile .= $nextline; */




      /*// output string now has contents of all three files

      // output the big string to the temp file

      file_put_contents("temp_data.csv", $bigfile);

      chmod("temp_data.csv", 0644); */      


      /*// use LOAD DATA INFILE to put contents of file into loads table

      $query = "LOAD DATA INFILE './temp_data.csv' INTO TABLE loads FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (load_num, stops, avail_date, pickup_city, pickup_state, deliv_city, deliv_state, description,equipment,broker,broker_phone,broker_email)";

      if(!$dbobj->query($query)) {

         $db_error = "couldn't load new data into table: " . $dbobj->error;

         echo $db_error;



      } */


      echo "database updated";



   function mail_clint($msg) {


      $mail = new PHPMailer;



      $mail->Subject = "Problem Updating Database";

      $mail->Body = $msg;




What I would like to do is modify this script for another project so that it imports the data from a single CSV file, deletes the CSV file from the server, and then updates the "products" table with new inventory qty where "products_model" in the table equals column A from the CSV file and columnB="products_quantity". I do not want the info deleted from the server, but instead updated. I also need it to check and and ignore any data from column A from the CSV data that are not in the DB as there are some products in the CSV file that I do not have listed on the website.

Would anyone be willing to assist me in editing this script? :help: :help: :help:



New php-forum User
New php-forum User
Posts: 3
Joined: Wed Sep 19, 2012 8:20 am

Re: CSV file to update MySQL DB

Postby clint6998 » Wed Sep 26, 2012 7:05 am


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 6 guests