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?
Thanks,
Clint
