Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

CSV file to update MySQL DB

Codes here !

Moderators: macek, egami, gesf

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
<?php

   //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

         $fuser_count++;

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

            // mail about the problem and quit

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

            mail_clint($file_error);

            exit;

         }

         

         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

      unlink('loads.csv');

      unlink('loads-OB.csv');

      unlink('loads-IB.csv');

      

      /*// Create empty output string

      $bigfile = ''; */

      

      // connect to database

      include('../other/define.php');

      $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;

         mail_clint($db_error);

         exit();

      }

      

      // 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;

         mail_clint($db_error);

         exit();

      }

      

      // 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

            date_default_timezone_set('America/Chicago');

            $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;

               mail_clint($db_error);

               exit();

            }

            

            /*// 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;

         //mail_clint($db_error);

         exit();

      } */

   

      echo "database updated";

   }

   

   function mail_clint($msg) {

      require_once('../other/class.phpmailer.php');

      $mail = new PHPMailer;

      

      $mail->AddAddress('charris@xxxxx.com);

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

      $mail->Body = $msg;

      $nail->Send();

   }

?>



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:

Thanks,

Clint
clint6998
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

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


Return to mySQL & php coding

Who is online

Users browsing this forum: No registered users and 2 guests

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.

cron