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:
[php]
<?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('[email protected]); $mail->Subject = "Problem Updating Database"; $mail->Body = $msg; $nail->Send(); } ?>[/php]
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?
Thanks,
Clint
