Hi,
A while back I had written a script for a client who had wanted to upload an excel file for his clients that contained their inventory information. I had it set so everytime he would upload the file it would check the information in the file against that already stored in the db. If a row in the db contained a certain field like an account number. It would make any changes that were made to that field or if none were made it would leave it be. If there were any new rows in the file it would upload those. Worked great. Here's the code.
<?
break;
case "force_import":
$hmm = $_REQUEST['upfile'];
$uploaddir = '/home/ritegrap/www/cms/upload/';
$uploadfile = $uploaddir . basename($_FILES['upfile']['name']);
if (move_uploaded_file($_FILES['upfile']['tmp_name'], $uploadfile)) {
$upstatus = "File was successfully uploaded.";
} else {
$upstatus = "File was not uploaded. Please try again.";
}
// Include config file
include('common.php');
$link = dbConnect();
// Fetch the current time
$posted = time();
$lines = file ($uploadfile);
foreach ($lines as $line_num => $line)
{
$pieces = explode("|", $line);
$query = "SELECT * FROM inv WHERE anum = '$pieces[1]' AND pon = '$pieces[4]' AND rnu = '$pieces[5]'";
$existing_record = mysql_query($query) or die(mysql_error());
// if record exists already, then do an UPDATE
if(mysql_num_rows($existing_record)){
$existing_record = mysql_fetch_array($existing_record);
// update
$query = "UPDATE inv SET client = '$pieces[0]', qnty = '$pieces[2]', uom = '$pieces[3]', des = '$pieces[6]', ware = '$pieces[7]', posted = '$posted' WHERE invID='" . $existing_record["invID"] . "'";
// Execute Query
$result = @mysql_query($query) or die ("Error: $query" . mysql_error());
}
// otherwise, record does not exist - do an INSERT
else{
$query = "INSERT INTO inv (client, anum, qnty, uom, pon, rnu, des, ware, posted)
VALUES('$pieces[0]', '$pieces[1]', '$pieces[2]', '$pieces[3]', '$pieces[4]', '$pieces[5]', '$pieces[6]', '$pieces[7]', $posted)";
// Execute Query
$result = @mysql_query($query) or die ("Error: $query" . mysql_error());
}
}
?>
Now what I want to do is run through a few more things. Like if theres a row in the DB that is no longer in his excel file I want it to remove it from the DB so he doesn't have to go in and remove every row one by one. This way he gets the full fucntionality of only having to upload that file to maintain the whole thing. Can someone please help me with what I'd have to do there to accomplish that?
Another thing is I want him to be able to contain empty values and if that row is already in the DB and has something in it I want it to set it to NULL. Know what I mean? Another words, If he already had a client listed in there and they decided to empty out one of the rows in the excel file (which are broken down into the $pieces name in the code). When he uploads it I would like the client to still have their row in the DB but set it to NULL.
Any help anyone could give me on either of these things would be great. I just want it set so he doesn't have to go into the DB or anything. Just upload his file and maintain it all through there.
Thanks in advance!
